반응형
MS Excel 데이터를 SQL Server에 붙여넣기
Excel에는 MS SQL의 새 테이블에 붙여넣는 행이 많이 있습니다.간단한 방법이 있나요?
SQL Server Management Studio를 사용하는 경우 마우스를 사용하여 Excel에서 복사하여 Management Studio의 테이블에 붙여넣기만 하면 됩니다.그저.
- 붙여넣을 테이블로 이동합니다.
- "상위 200개 행 편집"을 선택합니다.
- 아무 곳이나 마우스 오른쪽 버튼으로 클릭하고 붙여넣기를 선택합니다.
이 작업을 수행하기 전에 Excel과 Management Studio 사이의 열을 일치시켜야 합니다.또한 Management Studio의 Table Designer를 사용하여 편집할 수 없는 열을 맨 마지막(맨 오른쪽)에 배치해야 합니다.
전체 절차에는 몇 초가 소요되며(설치와 시작에는 필요하지 않음) SQL 문이 필요하지 않습니다.
빈 데이터베이스 테이블 및 SSMS v18.1+에 대해서
이 기술은 과거에 성공적으로 사용되었습니다.
(...) 열을 건너뛰고(또는 메모에 사용) 다음 수식을 입력합니다.
="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"이것으로 프라이머리 키(PK), 정수 및 유니코드 문자열이 있는 테이블에 대한 삽입문이 작성되었습니다. (...)
엑셀
- Excel에서 SQL에 붙여넣을 데이터를 강조 표시하고 복사합니다.
SQL
- *를확인하세요
Identity Specification[Yes] (아이덴티티)럼이 자동으로 증가합니다. - 찾아서 을 클릭한 표를 선택합니다.
Edit Top 200 Rows를 누릅니다. - * 기호가 있는 빈 행을 마우스 오른쪽 버튼으로 클릭하고 대화 상자에서 붙여넣기를 선택합니다.
향후 참고 자료:
이를 통해 데이터를 excel-sheet에서 SQL-table로 복사 붙여넣을 수 있습니다.
- Excel에서 데이터를 선택하고 Ctrl + C를 누릅니다.
- SQL Server Management Studio에서 테이블을 오른쪽 클릭하여 [Edit Top 200 Rows]를 선택합니다.
- 맨 아래로 스크롤하여 행 머리글을 클릭하여 빈 행 전체를 선택합니다.
- Ctrl + V를 눌러 데이터를 붙여넣습니다.
주의: 대부분의 테이블에는 ID가 자동으로 생성/증가되는 ID 열이 있습니다.데이터를 붙여넣으면 Excel에서 가장 왼쪽 열을 SSMS에서 가장 왼쪽 열에 삽입하기 시작하고 ID 열에 데이터를 삽입합니다.SSMS에서 이 컬럼을 건너뛰기 위해 선택 항목 맨 왼쪽에 빈 컬럼을 유지하는 것을 방지하기 위해 SSMS는 기본 데이터인 자동 생성된 ID를 삽입합니다.또한 Excel 시트 선택 항목에서 건너뛸 열과 동일한 서수 위치에 빈 열이 있으면 다른 열을 건너뛸 수 있습니다.그러면 SSMS가 기본값(또는 기본값을 지정하지 않은 경우에는 NULL)을 삽입합니다.
Excel에서 선택한 항목을 잘라내 SQL Server에 붙여넣기 할 수 있는 Excel VBA 매크로를 개발하여 새로운 테이블을 만듭니다.이 매크로는 수천 개의 행과 여러 개의 열을 빠르고 지저분한 테이블 작성에 적합합니다(이론적으로 최대 200개의 열을 관리할 수 있습니다).이 매크로는 헤더 이름을 자동으로 검출하여 각 열에 가장 적합한 데이터 유형을 할당하려고 합니다(최대 1000자의 varchar 열을 처리합니다.
권장 설정 절차:
- Excel이 매크로를 실행할 수 있도록 설정되어 있는지 확인합니다.(파일->옵션->트러스트 센터->트러스트 센터 설정->매크로 설정->모든 매크로를 유효하게 합니다.)
- 아래의 VBA 코드를 개인 워크북과 관련된 모듈에 복사하십시오(모든 워크시트에서 매크로를 사용할 수 있도록).
- 매크로에 적절한 키 스트로크를 할당합니다(Ctrl Shift X를 할당했습니다).
- 개인 워크북 저장
매크로 사용
- SQL로 전송할 Excel 셀(열 머리글 포함)을 선택합니다.
- 할당된 키워드 조합을 눌러 매크로를 실행합니다.
- 지시에 따릅니다.(기본 테이블명은 ##Table)
- 클립보드 내용을 SSMS 창에 붙여넣고 생성된 SQL 코드를 실행합니다.브리프라이데이 238
VBA 코드:
'------------------------------------------------------------------------------
Public Sub TransferToSQL()
' TransferToSQL Macro
'
' This macro prepares data for pasting into SQL Server and posts it to the clipboard for inserting into SSMS.
' It attempts to automatically detect header rows and does a basic analysis of the first 15 rows to determine
' the most appropriate datatype to use handling text entries up to 1000 chars.
'
'
' Use of Macro
'
' 1. Select the cells in Excel (including column headers if they exist) to be transferred to SQL
' 2. Press the assigned keyword combination that you have assigned to run the macro
' 3. Follow the prompts. (Default table name is ##Table)
' 4. Paste the clipboard contents into a SSMS window and run the generated SQL code.
'
' Max Number of Columns: 200
'
' Created by BriFri238 - https://stackoverflow.com/a/26219806/1898524
'
' Keyboard Shortcut: Ctrl+Shift+X
'
' ver Date Reason
' === ==== ======
' 1.7 07/2018 Added prompt for "Append to existing table?" so SELECT INTO is not used.
' Added strInsertHeader to hold the list of columns which are used with the INSERT (field1, field2, ...)
' to support IDENTITY_INSERT.
' 1.6 06/2012 Fixed bug that prevented auto exit if no selection made / auto exit if blank Tablename entered or 'cancel' button pressed
' 1.5 02/2012 made use of function fn_ColLetter to retrieve the Column Letter for a specified column
' 1.4 02/2012 Replaces any Tabs in text data to spaces to prevent Double quotes being output in final results
' 1.3 02/2012 Place the 'drop table if already exists' code into a separate batch to prevent errors when inserting new table with same name but different shape and > 100 rows
' 1.2 01/2012 If null dates encountered code to cast it as Null rather than '00-Jan-1900'
' 1.1 10/2011 Code to drop the table if already exists
' 1.0 03/2011 Created
Dim intLastRow As Long
Dim intlastColumn As Integer
Dim intRow As Long
Dim intDataStartRow As Long
Dim intColumn As Integer
Dim strKeyWord As String
Dim intPos As Integer
Dim strDataTypeLevel(4) As String
Dim strColumnHeader(200) As String
Dim strDataType(200) As String
Dim intRowCheck As Integer
Dim strFormula(20) As String
Dim intHasHeaderRow As Integer
Dim strCellRef As String
Dim intFormulaCount As Integer
Dim strSQLTableName As String
Dim strSQLTableName_Encap As String
Dim intdataTypelevel As Integer
Const strConstHeaderKeyword As String = "ID,URN,name,Title,Job,Company,Contact,Address,Post,Town,Email,Tele,phone,Area,Region,Business,Total,Month,Week,Year,"
Const intConstMaxBatchSize As Integer = 100
Const intConstNumberRowsToAnalyse As Integer = 100
Dim strInsertHeader As String
Dim i As Integer
Dim bolAppendToSQLTable As Boolean ' True if the table exists and you want to append to it.
On Error GoTo ErrorHandler
intHasHeaderRow = vbNo
strDataTypeLevel(1) = "VARCHAR(1000)"
strDataTypeLevel(2) = "FLOAT"
strDataTypeLevel(3) = "INTEGER"
strDataTypeLevel(4) = "DATETIME"
' Use current selection and paste to new temp worksheet
Selection.Copy
Workbooks.Add ' add temp 'Working' Workbook
' Paste "Values Only" back into new temp workbook
Range("A3").Select ' Goto 3rd Row
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Copy Format of Selection
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Copy Values of Selection
ActiveCell.SpecialCells(xlLastCell).Select ' Goto last cell
intLastRow = ActiveCell.row
intlastColumn = ActiveCell.Column
' Check to make sure that there are cells which are selected
If intLastRow = 3 And intlastColumn = 1 Then
Application.DisplayAlerts = False ' Temporarily switch off Display Alerts
ActiveWindow.Close ' Delete newly created worksheet
Application.DisplayAlerts = True ' Switch display alerts back on
MsgBox "*** Please Make selection before running macro - Terminating ***", vbOKOnly, "Transfer Data to SQL Server"
Exit Sub
End If
' Prompt user for Name of SQL Server table
strSQLTableName = InputBox("SQL Server Table Name?", "Transfer Excel Data To SQL", "##Table")
' if blank table name entered or 'Cancel' selected then exit
If strSQLTableName = "" Then
Application.DisplayAlerts = False ' Temporarily switch off Display Alerts
ActiveWindow.Close ' Delete newly created worksheet
Application.DisplayAlerts = True ' Switch display alerts back on
Exit Sub
End If
Application.ScreenUpdating = False
' encapsulate tablename with square brackets if user has not already done so
strSQLTableName_Encap = Replace(Replace(Replace("[" & Replace(strSQLTableName, ".", "].[") & "]", "[]", ""), "[[", "["), "]]", "]")
' Try to determine if the First Row is a header row or contains data and if a header load names of Columns
Range("A3").Select
For intColumn = 1 To intlastColumn
' first check to see if the first row contains any pure numbers or pure dates
If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
intHasHeaderRow = vbNo
intDataStartRow = 3
Exit For
Else
strColumnHeader(intColumn) = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select ' go to the row below
If IsNumeric(ActiveCell.Value) Or IsDate(ActiveCell.Value) Then
intHasHeaderRow = vbYes
intDataStartRow = 4
End If
ActiveCell.Offset(-1, 0).Range("A1").Select ' go back up to the first row
If intHasHeaderRow = vbNo Then ' if still not determined if header exists: Look for header using keywords
intPos = 1
While intPos < Len(strConstHeaderKeyword) And intHasHeaderRow = vbNo
strKeyWord = Mid$(strConstHeaderKeyword, intPos, InStr(intPos, strConstHeaderKeyword, ",") - intPos)
If InStr(1, ActiveCell.Value, strKeyWord) > 0 Then
intHasHeaderRow = vbYes
intDataStartRow = 4
End If
intPos = InStr(intPos, strConstHeaderKeyword, ",") + 1
Wend
End If
End If
ActiveCell.Offset(0, 1).Range("A1").Select ' Goto next column
Next intColumn
' If auto header row detection has failed ask the user to manually select
If intHasHeaderRow = vbNo Then
intHasHeaderRow = MsgBox("Does current selection have a header row?", vbYesNo + vbQuestion, "Auto header row detection failure")
If intHasHeaderRow = vbYes Then
intDataStartRow = 4
Else
intDataStartRow = 3
End If
End If
' *** Determine the Data Type of each Column ***
' Go thru each Column to find Data types
If intLastRow < intConstNumberRowsToAnalyse Then ' Check the first intConstNumberRowsToAnalyse rows or to end of selection whichever is less
intRowCheck = intLastRow
Else
intRowCheck = intConstNumberRowsToAnalyse
End If
For intColumn = 1 To intlastColumn
intdataTypelevel = 5
For intRow = intDataStartRow To intRowCheck
Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intColumn)
If ActiveCell.Value = "" Then ' ignore blank (null) values
ElseIf IsDate(ActiveCell.Value) = True And Len(ActiveCell.Value) >= 8 Then
If intdataTypelevel > 4 Then intdataTypelevel = 4
ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") = 0 And (Left(CStr(ActiveCell.Value), 1) <> "0" Or ActiveCell.Value = "0") And Len(ActiveCell.Value) < 10 Then
If intdataTypelevel > 3 Then intdataTypelevel = 3
ElseIf IsNumeric(ActiveCell.Value) = True And InStr(1, CStr(ActiveCell.Value), ".") >= 1 Then
If intdataTypelevel > 2 Then intdataTypelevel = 2
Else
intdataTypelevel = 1
Exit For
End If
Next intRow
If intdataTypelevel = 5 Then intdataTypelevel = 1
strDataType(intColumn) = strDataTypeLevel(intdataTypelevel)
If intHasHeaderRow = vbYes Then
' Build a string of the column headings to be used by the INSERT (field1, field2, ...)
strInsertHeader = strInsertHeader & ", [" & strColumnHeader(intColumn) & "]"
End If
Next intColumn
If intHasHeaderRow = vbYes Then
strInsertHeader = Mid(strInsertHeader, 3) ' Remove prefix
' Ask user if they want to Append to an existing table or DROP and CREATE a new table. BS 7/19/2018
bolAppendToSQLTable = MsgBox("Do you want to APPEND to this table?", vbYesNo + vbQuestion, "Append to " & strSQLTableName) = vbYes
End If
' *** Build up the SQL
intFormulaCount = 1
If intHasHeaderRow = vbYes Then ' *** Header Row ***
Application.GoTo Reference:="R4" & "C" & CStr(intlastColumn + 1) ' Goto next column in first data row of selection
strFormula(intFormulaCount) = "= ""SELECT "
For intColumn = 1 To intlastColumn
If strDataType(intColumn) = "DATETIME" Then ' Code to take Excel Dates back to text
strCellRef = "Text(" & fn_ColLetter(intColumn) & "4,""dd-mmm-yyyy hh:mm:ss"")"
ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "4,""'"",""''"")" ' Convert any single ' to double ''
Else
strCellRef = fn_ColLetter(intColumn) & "4"
End If
strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [" & strColumnHeader(intColumn) & "]"
If intColumn < intlastColumn Then
strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
Else
strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
End If
' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
intFormulaCount = intFormulaCount + 1
strFormula(intFormulaCount) = "= """
End If
Next intColumn
' Assign the formula to the cell(s) just right of the selection
For intColumn = 1 To intFormulaCount
ActiveCell.Value = strFormula(intColumn)
If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select ' Goto next column
Next intColumn
' Auto Fill the formula for the full length of the selection
ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "4:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault
' Go to start row of data selection to add 'Select into' code
If bolAppendToSQLTable = True Then
' When appending to an existing table, use this syntax
ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " ( " & strInsertHeader & " )" & " SELECT * FROM (" & ActiveCell.Value
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
ActiveCell.Value = "SET IDENTITY_INSERT " & strSQLTableName_Encap & " ON;"
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
ActiveCell.Value = "--BEGIN TRANSACTION; COMMIT; SET IDENTITY_INSERT " & strSQLTableName_Encap & " OFF;"
Else
' If creating a new table, use this syntax
ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value
' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
ActiveCell.Value = "GO"
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
If Left(strSQLTableName, 1) = "#" Then ' temp table
ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
Else
ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
End If
End If
' For Big selections (i.e. several 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
intRow = intConstMaxBatchSize + 4 ' add 4 to make sure 1st batch = Max Batch Size
While intRow < intLastRow
Application.GoTo Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount) ' Goto Row before intRow and the last column in formula selection
ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a") ' Remove last 'UNION ALL'
Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1) ' Goto intRow and the first column in formula selection
' BS 7/19/2018: Updated to include the list of fields so that IDENTITY_INSERT can be used.
ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " ( " & strInsertHeader & " )" & " SELECT * FROM (" & ActiveCell.Value
' ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value
intRow = intRow + intConstMaxBatchSize ' increment intRow by intConstMaxBatchSize
Wend
' Delete the last 'UNION AlL' replacing it with brackets to mark the end of the last insert
Application.GoTo Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")
' Select all the formula cells
ActiveCell.Offset(-intLastRow + 2, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow - 1)).Select
Else ' *** No Header Row ***
Application.GoTo Reference:="R3" & "C" & CStr(intlastColumn + 1) ' Goto next column in first data row of selection
strFormula(intFormulaCount) = "= ""SELECT "
For intColumn = 1 To intlastColumn
If strDataType(intColumn) = "DATETIME" Then
strCellRef = "Text(" & fn_ColLetter(intColumn) & "3,""dd-mmm-yyyy hh:mm:ss"")" ' Format Excel dates into a text Date format that SQL will pick up
ElseIf strDataType(intColumn) = "VARCHAR(1000)" Then
strCellRef = "SUBSTITUTE(" & fn_ColLetter(intColumn) & "3,""'"",""''"")" ' Change all single ' to double ''
Else
strCellRef = fn_ColLetter(intColumn) & "3"
End If
' Since no column headers: Name each column "Column001",Column002"..
strFormula(intFormulaCount) = strFormula(intFormulaCount) & "CAST('""& " & strCellRef & " & ""' AS " & strDataType(intColumn) & ") AS [Column" & CStr(intColumn) & "]"
If intColumn < intlastColumn Then
strFormula(intFormulaCount) = strFormula(intFormulaCount) + ", "
Else
strFormula(intFormulaCount) = strFormula(intFormulaCount) + " UNION ALL """
End If
' since each cell can only hold a maximum no. of chars if Formula string gets too big continue formula in adjacent cell
If Len(strFormula(intFormulaCount)) > 700 And intColumn < intlastColumn Then
strFormula(intFormulaCount) = strFormula(intFormulaCount) + """"
intFormulaCount = intFormulaCount + 1
strFormula(intFormulaCount) = "= """
End If
Next intColumn
' Assign the formula to the cell(s) just right of the selection
For intColumn = 1 To intFormulaCount
ActiveCell.Value = strFormula(intColumn)
If intColumn < intFormulaCount Then ActiveCell.Offset(0, 1).Range("A1").Select ' Goto next column
Next intColumn
' Auto Fill the formula for the full length of the selection
ActiveCell.Offset(0, -intFormulaCount + 1).Range("A1:" & fn_ColLetter(intFormulaCount) & "1").Select
If intLastRow > 4 Then Selection.AutoFill Destination:=Range(fn_ColLetter(intlastColumn + 1) & "3:" & fn_ColLetter(intlastColumn + intFormulaCount) & CStr(intLastRow)), Type:=xlFillDefault
' Go to start row of data selection to add 'Select into' code
ActiveCell.Value = "SELECT * INTO " & strSQLTableName_Encap & " FROM (" & ActiveCell.Value
' Go to cells above data to insert code for deleting old table with the same name in separate SQL batch
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
ActiveCell.Value = "GO"
ActiveCell.Offset(-1, 0).Range("A1").Select ' go to the row above
If Left(strSQLTableName, 1) = "#" Then ' temp table
ActiveCell.Value = "IF OBJECT_ID('tempdb.." & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
Else
ActiveCell.Value = "IF OBJECT_ID('" & strSQLTableName & "') IS NOT NULL DROP TABLE " & strSQLTableName_Encap
End If
' For Big selections (i.e. serveral 100 or 1000 rows) SQL Server takes a very long time to do a multiple union - Split up the table creation into many inserts
intRow = intConstMaxBatchSize + 3 ' add 3 to make sure 1st batch = Max Batch Size
While intRow < intLastRow
Application.GoTo Reference:="R" & CStr(intRow - 1) & "C" & CStr(intlastColumn + intFormulaCount) ' Goto Row before intRow and the last column in formula selection
ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a") ' Remove last 'UNION ALL'
Application.GoTo Reference:="R" & CStr(intRow) & "C" & CStr(intlastColumn + 1) ' Goto intRow and the first column in formula selection
ActiveCell.Value = "INSERT " & strSQLTableName_Encap & " SELECT * FROM (" & ActiveCell.Value
intRow = intRow + intConstMaxBatchSize ' increment intRow by intConstMaxBatchSize
Wend
' Delete the last 'UNION AlL'
Application.GoTo Reference:="R" & CStr(intLastRow) & "C" & CStr(intlastColumn + intFormulaCount)
ActiveCell.Value = Replace(ActiveCell.Value, " UNION ALL ", " ) a")
' Select all the formula cells
ActiveCell.Offset(-intLastRow + 1, 1 - intFormulaCount).Range("A1:" & fn_ColLetter(intFormulaCount + 1) & CStr(intLastRow)).Select
End If
' Final Selection to clipboard and Cleaning of data
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Repaste "Values Only" back into cells
Selection.Replace What:="CAST('' AS", Replacement:="CAST(NULL AS", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ' convert all blank cells to NULL
Selection.Replace What:="'00-Jan-1900 00:00:00'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ' convert all blank Date cells to NULL
Selection.Replace What:="'NULL'", Replacement:="NULL", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ' convert all 'NULL' cells to NULL
Selection.Replace What:=vbTab, Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ' Replace all Tabs in cells to Space to prevent Double Quotes occuring in the final paste text
Selection.Copy
Application.GoTo Reference:="R1" & "C" & CStr(intlastColumn + 1), Scroll:=True ' Goto next column in first data row of selection
Application.ScreenUpdating = True
If MsgBox("SQL Code has been added to clipboard - Please Paste into SSMS window." _
& vbCrLf & vbCrLf & "Do you want to close this temporary sheet?", vbYesNo + vbQuestion, "Transfer to SQL") = vbYes Then
Application.DisplayAlerts = False ' Temporarily switch off Display Alerts
ActiveWindow.Close ' Delete newly created worksheet
Application.DisplayAlerts = True ' Switch display alerts back on
End If
Exit_Sub:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure TransferToSQL of basMisc"
GoTo Exit_Sub
Resume Next
Resume
End Sub
Function fn_ColLetter(Col As Integer) As String
Dim strColLetter As String
If Col > 26 Then
' double letter columns
strColLetter = Chr(Int((Col - 1) / 26) + 64) & _
Chr(((Col - 1) Mod 26) + 65)
Else
' single letter columns
strColLetter = Chr(Col + 64)
End If
fn_ColLetter = strColLetter
End Function
가장 간단한 방법은 삽입문의 구문을 생성하는 계산 컬럼을 XLS에 작성하는 것입니다.그런 다음 이러한 삽입을 텍스트 파일에 복사한 다음 SQL에서 실행합니다.다른 대안은 Excel용 데이터베이스 연결 추가 기능을 구입하여 VBA 코드를 작성하는 것입니다.
일부 데이터베이스는 CSV(쉼표로 구분된 값) 파일에서 데이터를 가져올 수 있으며 exel에서 내보낼 수 있습니다.또는 적어도 CSV 파서를 사용하여 데이터베이스로 Import하는 것은 매우 간단합니다(사용하는 언어에 맞는 파서를 직접 작성하지 마십시오. 보기보다 어렵습니다).
MS SQL에 익숙하지 않지만 직접 지원한다고 해도 놀라지 않습니다.
어쨌든 Exel 시트와 데이터베이스 테이블의 구조가 비슷해야 한다고 생각합니다.
인터페이스가 지난번 사용했을 때와 같이 동작하고 있는 경우는, Excel 로 영역을 선택해 카피하고, 액세스와 같이 SQL Server 를 열어, 데이터를 테이블에 붙여넣을 수 있습니다.
또는 Excel과 SQL Server 간의 ODBC 링크를 설정할 수 있습니다.
SSMS에서 내보내기/가져오기 마법사만 사용하면 되지 않을까요?
VBA 코드를 사용하여 엑셀에서 복사하여 SSMS 작업에 붙여넣을 수 없습니까?
언급URL : https://stackoverflow.com/questions/316978/paste-ms-excel-data-to-sql-server
반응형
'source' 카테고리의 다른 글
| NSAray를 통해 어떻게 반복해야 하나요? (0) | 2023.04.19 |
|---|---|
| Microsoft 의 사용법.Office.Interop.MS Office가 설치되지 않은 머신에서 뛰어난 성능을 발휘하시겠습니까? (0) | 2023.04.19 |
| 푸시되지 않은 Git 커밋 보기 (0) | 2023.04.19 |
| Excel 피벗 테이블을 다른 피벗 테이블의 데이터 소스로 사용 (0) | 2023.04.19 |
| Xcode 5: 코드 서명 자격 오류 (0) | 2023.04.19 |