Paste MS Excel data to SQL Server

Question

I have a bunch of rows in Excel that I want to paste into a new table in MS SQL  Is there a simple way

User · Answer

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement  Then copy these insert into a text file and then execute on the SQL  The other alternatives are to buy database connectivity add-on s for Excel and write VBA code to accomplish the same

User · Answer

I have used this technique successfully in the past  Using Excel to generate Inserts for SQL Server        Skip a column  or use it for notes  and then type something like the following formula in it    quot insert into tblyourtablename  yourkeyID pk  intmine  strval  values   quot  amp A4 amp  quot    quot  amp B4 amp  quot   N  quot  amp C4 amp  quot    quot  Now you   ve got your insert statement for a table with your primary key  PK   an integer and a unicode string

User · Answer

I have used this technique successfully in the past  Using Excel to generate Inserts for SQL Server        Skip a column  or use it for notes  and then type something like the following formula in it    quot insert into tblyourtablename  yourkeyID pk  intmine  strval  values   quot  amp A4 amp  quot    quot  amp B4 amp  quot   N  quot  amp C4 amp  quot    quot  Now you   ve got your insert statement for a table with your primary key  PK   an integer and a unicode string

User · Answer

Excel   In Excel  highlight and copy the data you want to paste into SQL    SQL   Create the table with the desired column names and give you table a name   Make sure Identity Specification is Yes  so it will auto increment your  Identity column  Find your table  and right click on it and choose Edit Top 200 Rows from the dialog-box  Right click on the empty row with the   sign and select paste from the dialog-box

User · Answer

If the interface works the way it did last I used it  you can select the region in Excel  copy it  open SQL Server and paste the data into the table as you would with Access   Or you could setup an ODBC link between Excel and SQL Server

User · Answer

For future references  You can copy-paste data from en excel-sheet to an SQL-table by doing so   Select the data in Excel and press Ctrl   C In SQL Server Management Studio right click the table and choose Edit Top 200 Rows Scroll to the bottom and select the entire empty row by clicking on the row header Paste the data by pressing Ctrl   V  Note  Often tables have a first column which is an ID-column with an auto generated incremented ID  When you paste your data it will start inserting the leftmost selected column in Excel into the leftmost column in SSMS thus inserting data into the ID-column  To avoid that keep an empty column at the leftmost part of your selection in order to skip that column in SSMS  That will result in SSMS inserting the default data which is the auto generated ID  Furthermore you can skip other columns by having empty columns at the same ordinal positions in the Excel sheet selection as those columns to be skipped  That will make SSMS insert the default value  or NULL where no default value is specified

User · Answer

I d think some datbases can import data from CSV  comma separated values  files  wich you can export from exel  Or at least it s quite easy to use a csv parser  find one for your language  don t try to create one yourself - it s harder than it looks  to import it to the database    I m not familiar with MS SQL but it wouldn t suprise me if it does support it directly   In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar

User · Answer

why not just use export import wizard in SSMS

User · Answer

Excel   In Excel  highlight and copy the data you want to paste into SQL    SQL   Create the table with the desired column names and give you table a name   Make sure Identity Specification is Yes  so it will auto increment your  Identity column  Find your table  and right click on it and choose Edit Top 200 Rows from the dialog-box  Right click on the empty row with the   sign and select paste from the dialog-box

User · Answer

I have developed an Excel VBA Macro for cutting and pasting any selection from Excel into SQL Server   creating a new table   The macro is great for quick and dirty table creations up to a few thousand rows and multiple columns  It can theoretically manage up to 200 columns   The macro attempts to automatically detect header names and assign the most appropriate datatype to each column  it handles varchar columns upto 1000 chars    Recommended Setup procedure    Make sure Excel is enabled to run macros   File- Options- Trust Center- Trust Center Settings- Macro Settings- Enable all macros    Copy the VBA code below to the module associated with your personal workbook  So that the Macro will be available for all worksheets  Assign an appropriate keystroke to the macro   I have assigned Ctrl Shift X  Save your personal workbook    Use of Macro   Select the cells in Excel  including column headers if they exist  to be transferred to SQL  Press the assigned keyword combination that you have assigned to run the macro Follow the prompts   Default table name is   Table  Paste the clipboard contents into a SSMS window and run the generated SQL code  BriFri 238   VBA Code   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 upto 1000 chars      Max Number of Columns  200     Keyboard Shortcut  Ctrl Shift X     ver   Date    Reason                           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  gt  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 intHasHeaderRow   0  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      encapsulate tablename with square brackets if user has not already done so strSQLTableName Encap   Replace Replace Replace      amp  Replace strSQLTableName               amp                                            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   0 Then       if still not determined if header exists  Look for header using keywords         intPos   1         While intPos  lt  Len strConstHeaderKeyword  And intHasHeaderRow   0         strKeyWord   Mid  strConstHeaderKeyword  intPos  InStr intPos  strConstHeaderKeyword       - intPos          If InStr 1  ActiveCell Value  strKeyWord   gt  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   0 Then     intHasHeaderRow   MsgBox  Does current selection have a header row    vbYesNo   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  lt  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   amp  CStr intRow   amp   C   amp  CStr intColumn      If ActiveCell Value      Then     ignore blank  null  values     ElseIf IsDate ActiveCell Value    True And Len ActiveCell Value   gt   8 Then         If intdataTypelevel  gt  4 Then intdataTypelevel   4     ElseIf IsNumeric ActiveCell Value    True And InStr 1  CStr ActiveCell Value          0 And  Left CStr ActiveCell Value   1   lt  gt   0  Or ActiveCell Value    0   And Len ActiveCell Value   lt  10 Then         If intdataTypelevel  gt  3 Then intdataTypelevel   3     ElseIf IsNumeric ActiveCell Value    True And InStr 1  CStr ActiveCell Value         gt   1 Then         If intdataTypelevel  gt  2 Then intdataTypelevel   2     Else         intdataTypelevel   1         Exit For     End If     Next intRow     If intdataTypelevel   5 Then intdataTypelevel   1     strDataType intColumn    strDataTypeLevel intdataTypelevel  Next intColumn         Build up the SQL intFormulaCount   1 If intHasHeaderRow   vbYes Then           Header Row         Application Goto Reference   R4   amp   C   amp  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    amp  fn ColLetter intColumn   amp   4   dd-mmm-yyyy hh mm ss         ElseIf strDataType intColumn     VARCHAR 1000   Then         strCellRef    SUBSTITUTE    amp  fn ColLetter intColumn   amp   4                  Convert any single   to double        Else         strCellRef   fn ColLetter intColumn   amp   4      End If       strFormula intFormulaCount    strFormula intFormulaCount   amp   CAST     amp     amp  strCellRef  amp     amp      AS    amp  strDataType intColumn   amp     AS     amp  strColumnHeader intColumn   amp          If intColumn  lt  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    gt  700 And intColumn  lt  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  lt  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    amp  fn ColLetter intFormulaCount   amp   1   Select     If intLastRow  gt  4 Then Selection AutoFill Destination  Range fn ColLetter intlastColumn   1   amp   4    amp  fn ColLetter intlastColumn   intFormulaCount   amp  CStr intLastRow    Type  xlFillDefault     Go to start row of data selection to add  Select into  code    ActiveCell Value    SELECT   INTO    amp  strSQLTableName Encap  amp    FROM     amp  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     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    Else        ActiveCell Value    IF OBJECT ID     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    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  lt  intLastRow     Application Goto Reference   R   amp  CStr intRow - 1   amp   C   amp  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   amp  CStr intRow   amp   C   amp  CStr intlastColumn   1       Goto intRow and the first column in formula selection     ActiveCell Value    INSERT    amp  strSQLTableName Encap  amp    SELECT   FROM     amp  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   amp  CStr intLastRow   amp   C   amp  CStr intlastColumn   intFormulaCount      ActiveCell Value   Replace ActiveCell Value    UNION ALL        a          Select all the formula cells     ActiveCell Offset -intLastRow   2  1 - intFormulaCount  Range  A1    amp  fn ColLetter intFormulaCount   1   amp  CStr intLastRow - 1   Select Else          No Header Row         Application Goto Reference   R3   amp   C   amp  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    amp  fn ColLetter intColumn   amp   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    amp  fn ColLetter intColumn   amp   3                          Change all single   to double        Else         strCellRef   fn ColLetter intColumn   amp   3      End If        Since no column headers  Name each column  Column001  Column002        strFormula intFormulaCount    strFormula intFormulaCount   amp   CAST     amp     amp  strCellRef  amp     amp      AS    amp  strDataType intColumn   amp     AS  Column   amp  CStr intColumn   amp          If intColumn  lt  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    gt  700 And intColumn  lt  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  lt  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    amp  fn ColLetter intFormulaCount   amp   1   Select     If intLastRow  gt  4 Then Selection AutoFill Destination  Range fn ColLetter intlastColumn   1   amp   3    amp  fn ColLetter intlastColumn   intFormulaCount   amp  CStr intLastRow    Type  xlFillDefault     Go to start row of data selection to add  Select into  code    ActiveCell Value    SELECT   INTO    amp  strSQLTableName Encap  amp    FROM     amp  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     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    Else        ActiveCell Value    IF OBJECT ID     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  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  lt  intLastRow     Application Goto Reference   R   amp  CStr intRow - 1   amp   C   amp  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   amp  CStr intRow   amp   C   amp  CStr intlastColumn   1       Goto intRow and the first column in formula selection     ActiveCell Value    INSERT    amp  strSQLTableName Encap  amp    SELECT   FROM     amp  ActiveCell Value     intRow   intRow   intConstMaxBatchSize     increment intRow by intConstMaxBatchSize     Wend        Delete the last  UNION AlL      Application Goto Reference   R   amp  CStr intLastRow   amp   C   amp  CStr intlastColumn   intFormulaCount      ActiveCell Value   Replace ActiveCell Value    UNION ALL        a          Select all the formula cells     ActiveCell Offset -intLastRow   1  1 - intFormulaCount  Range  A1    amp  fn ColLetter intFormulaCount   1   amp  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   MsgBox  SQL Code has been added to clipboard - Please Paste into SSMS window   vbOKOnly   Transfer to SQL   Application DisplayAlerts   False         Temporarily switch off Display Alerts ActiveWindow Close                        Delete newly created worksheet Application DisplayAlerts   True          Switch display alerts back on    End Sub     Function fn ColLetter Col As Integer  As String  Dim strColLetter As String  If Col  gt  26 Then       double letter columns     strColLetter   Chr Int  Col - 1    26    64   amp            Chr   Col - 1  Mod 26    65  Else       single letter columns     strColLetter   Chr Col   64  End If fn ColLetter   strColLetter End Function

User · Answer

I have developed an Excel VBA Macro for cutting and pasting any selection from Excel into SQL Server   creating a new table   The macro is great for quick and dirty table creations up to a few thousand rows and multiple columns  It can theoretically manage up to 200 columns   The macro attempts to automatically detect header names and assign the most appropriate datatype to each column  it handles varchar columns upto 1000 chars    Recommended Setup procedure    Make sure Excel is enabled to run macros   File- Options- Trust Center- Trust Center Settings- Macro Settings- Enable all macros    Copy the VBA code below to the module associated with your personal workbook  So that the Macro will be available for all worksheets  Assign an appropriate keystroke to the macro   I have assigned Ctrl Shift X  Save your personal workbook    Use of Macro   Select the cells in Excel  including column headers if they exist  to be transferred to SQL  Press the assigned keyword combination that you have assigned to run the macro Follow the prompts   Default table name is   Table  Paste the clipboard contents into a SSMS window and run the generated SQL code  BriFri 238   VBA Code   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 upto 1000 chars      Max Number of Columns  200     Keyboard Shortcut  Ctrl Shift X     ver   Date    Reason                           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  gt  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 intHasHeaderRow   0  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      encapsulate tablename with square brackets if user has not already done so strSQLTableName Encap   Replace Replace Replace      amp  Replace strSQLTableName               amp                                            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   0 Then       if still not determined if header exists  Look for header using keywords         intPos   1         While intPos  lt  Len strConstHeaderKeyword  And intHasHeaderRow   0         strKeyWord   Mid  strConstHeaderKeyword  intPos  InStr intPos  strConstHeaderKeyword       - intPos          If InStr 1  ActiveCell Value  strKeyWord   gt  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   0 Then     intHasHeaderRow   MsgBox  Does current selection have a header row    vbYesNo   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  lt  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   amp  CStr intRow   amp   C   amp  CStr intColumn      If ActiveCell Value      Then     ignore blank  null  values     ElseIf IsDate ActiveCell Value    True And Len ActiveCell Value   gt   8 Then         If intdataTypelevel  gt  4 Then intdataTypelevel   4     ElseIf IsNumeric ActiveCell Value    True And InStr 1  CStr ActiveCell Value          0 And  Left CStr ActiveCell Value   1   lt  gt   0  Or ActiveCell Value    0   And Len ActiveCell Value   lt  10 Then         If intdataTypelevel  gt  3 Then intdataTypelevel   3     ElseIf IsNumeric ActiveCell Value    True And InStr 1  CStr ActiveCell Value         gt   1 Then         If intdataTypelevel  gt  2 Then intdataTypelevel   2     Else         intdataTypelevel   1         Exit For     End If     Next intRow     If intdataTypelevel   5 Then intdataTypelevel   1     strDataType intColumn    strDataTypeLevel intdataTypelevel  Next intColumn         Build up the SQL intFormulaCount   1 If intHasHeaderRow   vbYes Then           Header Row         Application Goto Reference   R4   amp   C   amp  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    amp  fn ColLetter intColumn   amp   4   dd-mmm-yyyy hh mm ss         ElseIf strDataType intColumn     VARCHAR 1000   Then         strCellRef    SUBSTITUTE    amp  fn ColLetter intColumn   amp   4                  Convert any single   to double        Else         strCellRef   fn ColLetter intColumn   amp   4      End If       strFormula intFormulaCount    strFormula intFormulaCount   amp   CAST     amp     amp  strCellRef  amp     amp      AS    amp  strDataType intColumn   amp     AS     amp  strColumnHeader intColumn   amp          If intColumn  lt  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    gt  700 And intColumn  lt  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  lt  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    amp  fn ColLetter intFormulaCount   amp   1   Select     If intLastRow  gt  4 Then Selection AutoFill Destination  Range fn ColLetter intlastColumn   1   amp   4    amp  fn ColLetter intlastColumn   intFormulaCount   amp  CStr intLastRow    Type  xlFillDefault     Go to start row of data selection to add  Select into  code    ActiveCell Value    SELECT   INTO    amp  strSQLTableName Encap  amp    FROM     amp  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     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    Else        ActiveCell Value    IF OBJECT ID     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    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  lt  intLastRow     Application Goto Reference   R   amp  CStr intRow - 1   amp   C   amp  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   amp  CStr intRow   amp   C   amp  CStr intlastColumn   1       Goto intRow and the first column in formula selection     ActiveCell Value    INSERT    amp  strSQLTableName Encap  amp    SELECT   FROM     amp  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   amp  CStr intLastRow   amp   C   amp  CStr intlastColumn   intFormulaCount      ActiveCell Value   Replace ActiveCell Value    UNION ALL        a          Select all the formula cells     ActiveCell Offset -intLastRow   2  1 - intFormulaCount  Range  A1    amp  fn ColLetter intFormulaCount   1   amp  CStr intLastRow - 1   Select Else          No Header Row         Application Goto Reference   R3   amp   C   amp  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    amp  fn ColLetter intColumn   amp   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    amp  fn ColLetter intColumn   amp   3                          Change all single   to double        Else         strCellRef   fn ColLetter intColumn   amp   3      End If        Since no column headers  Name each column  Column001  Column002        strFormula intFormulaCount    strFormula intFormulaCount   amp   CAST     amp     amp  strCellRef  amp     amp      AS    amp  strDataType intColumn   amp     AS  Column   amp  CStr intColumn   amp          If intColumn  lt  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    gt  700 And intColumn  lt  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  lt  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    amp  fn ColLetter intFormulaCount   amp   1   Select     If intLastRow  gt  4 Then Selection AutoFill Destination  Range fn ColLetter intlastColumn   1   amp   3    amp  fn ColLetter intlastColumn   intFormulaCount   amp  CStr intLastRow    Type  xlFillDefault     Go to start row of data selection to add  Select into  code    ActiveCell Value    SELECT   INTO    amp  strSQLTableName Encap  amp    FROM     amp  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     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  strSQLTableName Encap    Else        ActiveCell Value    IF OBJECT ID     amp  strSQLTableName  amp      IS NOT NULL DROP TABLE    amp  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  lt  intLastRow     Application Goto Reference   R   amp  CStr intRow - 1   amp   C   amp  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   amp  CStr intRow   amp   C   amp  CStr intlastColumn   1       Goto intRow and the first column in formula selection     ActiveCell Value    INSERT    amp  strSQLTableName Encap  amp    SELECT   FROM     amp  ActiveCell Value     intRow   intRow   intConstMaxBatchSize     increment intRow by intConstMaxBatchSize     Wend        Delete the last  UNION AlL      Application Goto Reference   R   amp  CStr intLastRow   amp   C   amp  CStr intlastColumn   intFormulaCount      ActiveCell Value   Replace ActiveCell Value    UNION ALL        a          Select all the formula cells     ActiveCell Offset -intLastRow   1  1 - intFormulaCount  Range  A1    amp  fn ColLetter intFormulaCount   1   amp  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   MsgBox  SQL Code has been added to clipboard - Please Paste into SSMS window   vbOKOnly   Transfer to SQL   Application DisplayAlerts   False         Temporarily switch off Display Alerts ActiveWindow Close                        Delete newly created worksheet Application DisplayAlerts   True          Switch display alerts back on    End Sub     Function fn ColLetter Col As Integer  As String  Dim strColLetter As String  If Col  gt  26 Then       double letter columns     strColLetter   Chr Int  Col - 1    26    64   amp            Chr   Col - 1  Mod 26    65  Else       single letter columns     strColLetter   Chr Col   64  End If fn ColLetter   strColLetter End Function

User · Answer

I d think some datbases can import data from CSV  comma separated values  files  wich you can export from exel  Or at least it s quite easy to use a csv parser  find one for your language  don t try to create one yourself - it s harder than it looks  to import it to the database    I m not familiar with MS SQL but it wouldn t suprise me if it does support it directly   In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar

User · Answer

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement  Then copy these insert into a text file and then execute on the SQL  The other alternatives are to buy database connectivity add-on s for Excel and write VBA code to accomplish the same

User · Answer

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement  Then copy these insert into a text file and then execute on the SQL  The other alternatives are to buy database connectivity add-on s for Excel and write VBA code to accomplish the same

User · Answer

I have used this technique successfully in the past  Using Excel to generate Inserts for SQL Server        Skip a column  or use it for notes  and then type something like the following formula in it    quot insert into tblyourtablename  yourkeyID pk  intmine  strval  values   quot  amp A4 amp  quot    quot  amp B4 amp  quot   N  quot  amp C4 amp  quot    quot  Now you   ve got your insert statement for a table with your primary key  PK   an integer and a unicode string

User · Answer

If you have SQL Server Management Studio  you can just Copy from Excel and Paste into the table in Management Studio  using your mouse  Just    Go to the table you want to paste into  Select  Edit Top 200 Rows   Right-click anywhere and select Paste    Before you do this  you must match the columns between Excel and Management Studio  Also  you must place any non-editable columns last  right-most  using the Table Designer in Management Studio   The whole procedure takes seconds  to set-up and start - not necessarily to execute  and doesn t require any SQL statements    Regarding empty database tables and SSMS v18 1

User · Answer

I d think some datbases can import data from CSV  comma separated values  files  wich you can export from exel  Or at least it s quite easy to use a csv parser  find one for your language  don t try to create one yourself - it s harder than it looks  to import it to the database    I m not familiar with MS SQL but it wouldn t suprise me if it does support it directly   In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar

User · Answer

why not just use export import wizard in SSMS

User · Answer

Can t you use VBA code to do the copy from excel and paste into SSMS operations

User · Answer

why not just use export import wizard in SSMS

User · Answer

If the interface works the way it did last I used it  you can select the region in Excel  copy it  open SQL Server and paste the data into the table as you would with Access   Or you could setup an ODBC link between Excel and SQL Server

User · Answer

For future references  You can copy-paste data from en excel-sheet to an SQL-table by doing so   Select the data in Excel and press Ctrl   C In SQL Server Management Studio right click the table and choose Edit Top 200 Rows Scroll to the bottom and select the entire empty row by clicking on the row header Paste the data by pressing Ctrl   V  Note  Often tables have a first column which is an ID-column with an auto generated incremented ID  When you paste your data it will start inserting the leftmost selected column in Excel into the leftmost column in SSMS thus inserting data into the ID-column  To avoid that keep an empty column at the leftmost part of your selection in order to skip that column in SSMS  That will result in SSMS inserting the default data which is the auto generated ID  Furthermore you can skip other columns by having empty columns at the same ordinal positions in the Excel sheet selection as those columns to be skipped  That will make SSMS insert the default value  or NULL where no default value is specified

User · Answer

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement  Then copy these insert into a text file and then execute on the SQL  The other alternatives are to buy database connectivity add-on s for Excel and write VBA code to accomplish the same

User · Answer

Can t you use VBA code to do the copy from excel and paste into SSMS operations

User · Answer

why not just use export import wizard in SSMS

User · Answer

I d think some datbases can import data from CSV  comma separated values  files  wich you can export from exel  Or at least it s quite easy to use a csv parser  find one for your language  don t try to create one yourself - it s harder than it looks  to import it to the database    I m not familiar with MS SQL but it wouldn t suprise me if it does support it directly   In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar

User · Answer

If the interface works the way it did last I used it  you can select the region in Excel  copy it  open SQL Server and paste the data into the table as you would with Access   Or you could setup an ODBC link between Excel and SQL Server

User · Answer

If the interface works the way it did last I used it  you can select the region in Excel  copy it  open SQL Server and paste the data into the table as you would with Access   Or you could setup an ODBC link between Excel and SQL Server

User · Answer

I have used this technique successfully in the past  Using Excel to generate Inserts for SQL Server        Skip a column  or use it for notes  and then type something like the following formula in it    quot insert into tblyourtablename  yourkeyID pk  intmine  strval  values   quot  amp A4 amp  quot    quot  amp B4 amp  quot   N  quot  amp C4 amp  quot    quot  Now you   ve got your insert statement for a table with your primary key  PK   an integer and a unicode string

User · Answer

If you have SQL Server Management Studio  you can just Copy from Excel and Paste into the table in Management Studio  using your mouse  Just    Go to the table you want to paste into  Select  Edit Top 200 Rows   Right-click anywhere and select Paste    Before you do this  you must match the columns between Excel and Management Studio  Also  you must place any non-editable columns last  right-most  using the Table Designer in Management Studio   The whole procedure takes seconds  to set-up and start - not necessarily to execute  and doesn t require any SQL statements    Regarding empty database tables and SSMS v18 1

[sql-server] Paste MS Excel data to SQL Server

Examples related to sql-server

Examples related to excel

Examples related to copy-paste