Import Excel in SQL Server 2008

Question

I need to create a process to import a table with multiple Excel tabs in SQL Server 2008R2. Each tab will be a different table in the database. This will need to be done weekly, and import should be automated. Ideally, I want to put the spreadsheet in a folder [or she has some kind of intern) and execute a sql procedure that looks at this folder and adds data to the tables in this db. I would also like to have another table that tracks imports and date stamps. I really don't know where to even start here, as I am pretty huge noob when it comes to tsql.

Answer 1

If you are limited only to TSQL , the two above answers will show you some ideas. If you have access to Data Tools or Business Intelligence using SSIS , you can automate it with the assumption that each sheet in an Excel workbook matches each time. With SSIS you will use the data flow task, and each sheet will be imported into the desired table. When you're ready for the file next week, put it in a folder and run the SSIS package.

However, if the sheet names change (for example, the weekly sheets are called Cats, Dogs, Rain, and next week - Sulfur, Fire, Hell), then this will cause the package to break. Otherwise, if only the data on the worksheet changes, it can be fully automated with SSIS.

Example article: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/

Answer 2

There is a good article from microsoft - http://support.microsoft.com/kb/321686 - which describes the processes involved.

The process is simple

 SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$] 

Where XLImport3 is the table into which you want to import, and the data source is the Excel sheet from which you want to import.

Answer 3

Below is the code for inserting data from a csv file into a given table. I'm not that all the requirements for a project, but if I were you, I would just split each table into a different file, and then just run proc, which inserts data into each of the tables.

 BULK INSERT TABLE_NAME FROM 'c:\filename.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) insert into import_history ('filename', 'import_date') values ('your_file_name', getdate()) 

In addition, for a table that tracks imports and timestamps, you can simply insert some data into this table after each bulk insert, as shown above.

In addition, here is a link to a volume insert tutorial from a csv file, which may also help: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql- server-using-bulk-insert-load-comma-delimited-file-into-sql-server /

Answer 4

It is very simple. Right-click Database on Sql Server (2008), select Tasks and select Import Data

Now change the value of the DataSource to Microsoft Excel . Select the path to the Excel file by clicking the Browse button and clicking Next .

Select the Sql Server instance and select the database into which excel will be imported.

Select Copy data from one or more tables or views and click Next .

Now select the sheets to import into Sql Server .

Click Next

Now click Finish

Now the wizard imports data from Excel into Sql Server and click Close .

Here is a table