Import Excel spreadsheet columns into SQL Server database

Question

I have an Excel spreadsheet that I want to import select columns into my SQL Server 2008 database table  The wizard didn t offer that option   Do any easy code options exist

User · Answer

If you would like a visual tool with Desktop interface including validation .. you may well like this Excel tool. You can also use the tool to create multi user data-edit tasks, or even paste data to SQL server from any source..

How to Validate and Import Excel spreadsheet to SQL Server database:

http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

User · Answer

Microsoft suggest several methods    SQL Server Data Transformation Services  DTS  Microsoft SQL Server 2005 Integration Services  SSIS  SQL Server linked servers SQL Server distributed queries ActiveX Data Objects  ADO  and the Microsoft OLE DB Provider for SQL Server ADO and the Microsoft OLE DB Provider for Jet 4 0   If the wizard  DTS  isn t working  and I think it should  you could try something like this http   www devasp net net articles display 771 html which basically suggests doing something like  INSERT INTO  tblTemp    Column1    Column2    Column3    Column4    SELECT A  Column1   A  Column2   A  Column3   A  Column4  FROM OPENROWSET    Microsoft Jet OLEDB 4 0    Excel 8 0 Database D  Excel xls HDR YES    select   from  Sheet1     AS A

User · Answer

go sp configure  show advanced options  1   reconfigure with override   go   sp configure  Ad Hoc Distributed Queries  1   reconfigure with override   go SELECT   into temptable FROM OPENROWSET  Microsoft Jet OLEDB 4 0                    Excel 8 0 Database C  Documents and Settings abhisharma Desktop exl ImportExcel2SQLServer ImportExcel2SQLServer example xls IMEX 1                    SELECT   FROM  Sheet1      select   from temptable

User · Answer

I have used DTS  now known as SQL server Import and Export Wizard    I used the this tutorial which worked great for me even in Sql 2008 and excel 2010  14 0   I hope this helps  -D

User · Answer

Microsoft Access is another option   You could have a Access database locally on your machine that you import the excel spreadsheets into  wizards available  and link to the the SQL Server database tables via ODBC   You could then design a query in access that appends data from the Excel spreadsheet to the SQL Server Tables

User · Answer

Excel   SQLCMD   Perl   exceltomssqlinsert  and you can use your Excel as frond-end to MSSQL db     Note the truncate table at the beginning of each generated sql insert file

User · Answer

I think it will help you  Visit http   social msdn microsoft com Forums en-US sqlgetstarted thread 78f8e980-0bb6-4a48-b1b1-5f65bca1d511

User · Answer

You could use OPENROWSET  something like    SELECT   FROM OPENROWSET  Microsoft Jet OLEDB 4 0       Excel 8 0 IMEX 1 HDR NO DATABASE C  FILE xls    Select   from  Sheet1       Just make sure the path is a path on the server  not your local machine

User · Answer

I have used DTS  now known as SQL server Import and Export Wizard    I used the this tutorial which worked great for me even in Sql 2008 and excel 2010  14 0   I hope this helps  -D

User · Answer

The best tool i ve ever used is http   tools perceptus ca text-wiz php ops 7 Did you try it

User · Answer

The import wizard does offer that option   You can either use the option to write your own query for the data to import  or you can use the copy data option and use the  Edit Mappings  button to ignore columns you do not want to import

User · Answer

Microsoft suggest several methods    SQL Server Data Transformation Services  DTS  Microsoft SQL Server 2005 Integration Services  SSIS  SQL Server linked servers SQL Server distributed queries ActiveX Data Objects  ADO  and the Microsoft OLE DB Provider for SQL Server ADO and the Microsoft OLE DB Provider for Jet 4 0   If the wizard  DTS  isn t working  and I think it should  you could try something like this http   www devasp net net articles display 771 html which basically suggests doing something like  INSERT INTO  tblTemp    Column1    Column2    Column3    Column4    SELECT A  Column1   A  Column2   A  Column3   A  Column4  FROM OPENROWSET    Microsoft Jet OLEDB 4 0    Excel 8 0 Database D  Excel xls HDR YES    select   from  Sheet1     AS A

User · Answer

You could use OPENROWSET  something like    SELECT   FROM OPENROWSET  Microsoft Jet OLEDB 4 0       Excel 8 0 IMEX 1 HDR NO DATABASE C  FILE xls    Select   from  Sheet1       Just make sure the path is a path on the server  not your local machine

User · Answer

If you would like a visual tool with Desktop interface including validation .. you may well like this Excel tool. You can also use the tool to create multi user data-edit tasks, or even paste data to SQL server from any source..

How to Validate and Import Excel spreadsheet to SQL Server database:

http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

User · Answer

Microsoft Access is another option   You could have a Access database locally on your machine that you import the excel spreadsheets into  wizards available  and link to the the SQL Server database tables via ODBC   You could then design a query in access that appends data from the Excel spreadsheet to the SQL Server Tables

User · Answer

I think it will help you  Visit http   social msdn microsoft com Forums en-US sqlgetstarted thread 78f8e980-0bb6-4a48-b1b1-5f65bca1d511

User · Answer

By  the wiz  I m assuming you re talking about the  SQL Server Import and Export Wizard    I m also pretty new so I don t understand most questions  much less most answers  but I think I get this one   If so couldn t you take the spreadsheet  or a copy of it  delete the columns you don t want imported and then use the wizard   I ve always found the ability to do what I need with it and I m only on SQL Server 2000  not sure how other versions differ    Edit  In fact I m looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table  On the  Select Source Tables and Views  screen I check the datasheet I m using  select the  Destination  then click the  Edit     button  From there you can choose the Excel column and the table column to map it to

User · Answer

This may sound like the long way around  but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table   Works well if you cant use the wizards because the excel file isn t on the server

User · Answer

Once connected to Sql Server 2005 Database  From Object Explorer Window  right click on the database which you want to import table into  Select Tasks -  Import Data  This is a simple tool and allows you to  map  the incoming data into appropriate table  You can save the scripts to run again when needed

User · Answer

By  the wiz  I m assuming you re talking about the  SQL Server Import and Export Wizard    I m also pretty new so I don t understand most questions  much less most answers  but I think I get this one   If so couldn t you take the spreadsheet  or a copy of it  delete the columns you don t want imported and then use the wizard   I ve always found the ability to do what I need with it and I m only on SQL Server 2000  not sure how other versions differ    Edit  In fact I m looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table  On the  Select Source Tables and Views  screen I check the datasheet I m using  select the  Destination  then click the  Edit     button  From there you can choose the Excel column and the table column to map it to

User · Answer

Once connected to Sql Server 2005 Database  From Object Explorer Window  right click on the database which you want to import table into  Select Tasks -  Import Data  This is a simple tool and allows you to  map  the incoming data into appropriate table  You can save the scripts to run again when needed

User · Answer

First of all  try the 32 Bit Version of the Import Wizard  This shows a lot more supported import formats   Background  All depends on your Office  Runtimes Engines  installation   If you dont t have Office 2007 or greater installed  the Import Wizard  32 Bit  only allows you to import Excel 97-2003   xls  files   If you have the Office 2010 and geater  comes also in 64 Bit  not recommended  installed  the Import Wizard also supports Excel 2007    xlsx  files   To get an overview on the runtimes see   39 Microsoft ACE OLEDB 12 0  39  provider is not registered on the local machine

User · Answer

The import wizard does offer that option   You can either use the option to write your own query for the data to import  or you can use the copy data option and use the  Edit Mappings  button to ignore columns you do not want to import

User · Answer

The import wizard does offer that option   You can either use the option to write your own query for the data to import  or you can use the copy data option and use the  Edit Mappings  button to ignore columns you do not want to import

User · Answer

By  the wiz  I m assuming you re talking about the  SQL Server Import and Export Wizard    I m also pretty new so I don t understand most questions  much less most answers  but I think I get this one   If so couldn t you take the spreadsheet  or a copy of it  delete the columns you don t want imported and then use the wizard   I ve always found the ability to do what I need with it and I m only on SQL Server 2000  not sure how other versions differ    Edit  In fact I m looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table  On the  Select Source Tables and Views  screen I check the datasheet I m using  select the  Destination  then click the  Edit     button  From there you can choose the Excel column and the table column to map it to

User · Answer

The best tool i ve ever used is http   tools perceptus ca text-wiz php ops 7 Did you try it

User · Answer

This may sound like the long way around  but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table   Works well if you cant use the wizards because the excel file isn t on the server

User · Answer

You could use OPENROWSET  something like    SELECT   FROM OPENROWSET  Microsoft Jet OLEDB 4 0       Excel 8 0 IMEX 1 HDR NO DATABASE C  FILE xls    Select   from  Sheet1       Just make sure the path is a path on the server  not your local machine

User · Answer

Excel   SQLCMD   Perl   exceltomssqlinsert  and you can use your Excel as frond-end to MSSQL db     Note the truncate table at the beginning of each generated sql insert file

User · Answer

go sp configure  show advanced options  1   reconfigure with override   go   sp configure  Ad Hoc Distributed Queries  1   reconfigure with override   go SELECT   into temptable FROM OPENROWSET  Microsoft Jet OLEDB 4 0                    Excel 8 0 Database C  Documents and Settings abhisharma Desktop exl ImportExcel2SQLServer ImportExcel2SQLServer example xls IMEX 1                    SELECT   FROM  Sheet1      select   from temptable

User · Answer

Microsoft suggest several methods    SQL Server Data Transformation Services  DTS  Microsoft SQL Server 2005 Integration Services  SSIS  SQL Server linked servers SQL Server distributed queries ActiveX Data Objects  ADO  and the Microsoft OLE DB Provider for SQL Server ADO and the Microsoft OLE DB Provider for Jet 4 0   If the wizard  DTS  isn t working  and I think it should  you could try something like this http   www devasp net net articles display 771 html which basically suggests doing something like  INSERT INTO  tblTemp    Column1    Column2    Column3    Column4    SELECT A  Column1   A  Column2   A  Column3   A  Column4  FROM OPENROWSET    Microsoft Jet OLEDB 4 0    Excel 8 0 Database D  Excel xls HDR YES    select   from  Sheet1     AS A

User · Answer

The import wizard does offer that option   You can either use the option to write your own query for the data to import  or you can use the copy data option and use the  Edit Mappings  button to ignore columns you do not want to import

User · Answer

Another option is to use VBA in Excel  and write a macro to parse the spreadsheet data and write it into SQL   One example is here  http   www ozgrid com forum showthread php t 26621 amp page 1  Sub InsertARecord    Dim cnt As ADODB Connection  Dim rst As ADODB Recordset  Dim stCon As String  stSQL As String  Set cnt   New ADODB Connection  Set rst   New ADODB Recordset   stCon    Provider MSDASQL 1 Persist Security Info False Data Source JOEY   cnt ConnectionString   stCon  stSQL    INSERT INTO MyTable  Price    stSQL   stSQL  amp   VALUES  500     cnt Open  rst Open stSQL  cnt  adOpenStatic  adLockReadOnly  adCmdText   If CBool rst State And adStateOpen    True Then rst Close  Set rst   Nothing  If CBool cnt State And adStateOpen    True Then cnt Close  Set cnt   Nothing   End Sub

User · Answer

Microsoft suggest several methods    SQL Server Data Transformation Services  DTS  Microsoft SQL Server 2005 Integration Services  SSIS  SQL Server linked servers SQL Server distributed queries ActiveX Data Objects  ADO  and the Microsoft OLE DB Provider for SQL Server ADO and the Microsoft OLE DB Provider for Jet 4 0   If the wizard  DTS  isn t working  and I think it should  you could try something like this http   www devasp net net articles display 771 html which basically suggests doing something like  INSERT INTO  tblTemp    Column1    Column2    Column3    Column4    SELECT A  Column1   A  Column2   A  Column3   A  Column4  FROM OPENROWSET    Microsoft Jet OLEDB 4 0    Excel 8 0 Database D  Excel xls HDR YES    select   from  Sheet1     AS A

User · Answer

First of all  try the 32 Bit Version of the Import Wizard  This shows a lot more supported import formats   Background  All depends on your Office  Runtimes Engines  installation   If you dont t have Office 2007 or greater installed  the Import Wizard  32 Bit  only allows you to import Excel 97-2003   xls  files   If you have the Office 2010 and geater  comes also in 64 Bit  not recommended  installed  the Import Wizard also supports Excel 2007    xlsx  files   To get an overview on the runtimes see   39 Microsoft ACE OLEDB 12 0  39  provider is not registered on the local machine

User · Answer

By  the wiz  I m assuming you re talking about the  SQL Server Import and Export Wizard    I m also pretty new so I don t understand most questions  much less most answers  but I think I get this one   If so couldn t you take the spreadsheet  or a copy of it  delete the columns you don t want imported and then use the wizard   I ve always found the ability to do what I need with it and I m only on SQL Server 2000  not sure how other versions differ    Edit  In fact I m looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table  On the  Select Source Tables and Views  screen I check the datasheet I m using  select the  Destination  then click the  Edit     button  From there you can choose the Excel column and the table column to map it to

User · Answer

You could use OPENROWSET  something like    SELECT   FROM OPENROWSET  Microsoft Jet OLEDB 4 0       Excel 8 0 IMEX 1 HDR NO DATABASE C  FILE xls    Select   from  Sheet1       Just make sure the path is a path on the server  not your local machine

User · Answer

Another option is to use VBA in Excel  and write a macro to parse the spreadsheet data and write it into SQL   One example is here  http   www ozgrid com forum showthread php t 26621 amp page 1  Sub InsertARecord    Dim cnt As ADODB Connection  Dim rst As ADODB Recordset  Dim stCon As String  stSQL As String  Set cnt   New ADODB Connection  Set rst   New ADODB Recordset   stCon    Provider MSDASQL 1 Persist Security Info False Data Source JOEY   cnt ConnectionString   stCon  stSQL    INSERT INTO MyTable  Price    stSQL   stSQL  amp   VALUES  500     cnt Open  rst Open stSQL  cnt  adOpenStatic  adLockReadOnly  adCmdText   If CBool rst State And adStateOpen    True Then rst Close  Set rst   Nothing  If CBool cnt State And adStateOpen    True Then cnt Close  Set cnt   Nothing   End Sub

User · Answer

This may sound like the long way around  but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table   Works well if you cant use the wizards because the excel file isn t on the server

User · Answer

This may sound like the long way around  but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table   Works well if you cant use the wizards because the excel file isn t on the server

[sql-server] Import Excel spreadsheet columns into SQL Server database

Examples related to sql-server

Examples related to excel

Examples related to sql-server-2008

Examples related to import

Examples related to etl