I need to create a process to import a multi tabbed excel spreadsheet into SQL Server 2008R2. Each tab will be a different table in the database. This will need to be done weekly and imports should be automated. Ideally I want to pop the spreadsheet into a folder [or have some intern do it] and have sql run a procedure that looks in this folder, and adds the data to the tables in this db. I would also like to have another table that tracks the imports and date stamps them. I really have no idea where to even start here as I'm a pretty huge noob when it comes to tsql.
4 Answers
There is a nice article by microsoft - http://support.microsoft.com/kb/321686 - that outlines the processes involved.
The process is simply
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 you want to import into and the datasource is the excel sheet you want to import from.
1 Comment
illumi
You need to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure in order to use 'OpenDatasource'
If you're limited solely to TSQL, the above two answers will show you some ideas. If you have access to either Data Tools or Business Intelligence, with SSIS, you can automate it with the assumption that each sheet in the Excel workbook matches each time. With SSIS, you'll use a Data Flow task and each sheet will be imported into the table that you want. When you're ready for the file the next week, you'll drop it into the folder and run the SSIS package.
However, if the sheet names change, (for instance, one week sheets are called Cats, Dogs, Rain and the next week it's Sulfur, Fire, Hell) then this would cause the package to break. Otherwise, if only the data within the worksheet change, then this can be completely automated with SSIS.
Example article: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/
2 Comments
d90
I read through the article and it seems pretty straightforward. Would I be creating a different data flow branch for each tab\table?
Question3CPO
@NicholasJDininno With multiple sheets, I would have one Data Flow task, and within that data flow task, have multiple Excel Sources with multiple ADO.NET or OLE DB Connection Destinations. If you choose, you could do several Data Flows; it's up to you.
Below is the code to insert data from a csv file into a given table. I don't what the full requirements are for the project, but if I were you I would just separate each table into a different file and then just run a proc that 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())
Also, for the table that tracks imports and timestamps them, you could just insert some data into that table after each bulk insert as seen above.
Also, here's a link to tutorial on bulk inserting from a csv file that 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/
Comments
Its very simple. Right click the Database in Sql Server(2008), select Tasks and select Import Data
Now change the DataSource to Microsoft Excel. Chose the path of Excel file by clicking Browse button and click Next.
Chose the Sql Server instance and chose the database to which the excel to be imported.
Select Copy data from one or more tables or views and click Next.
Now select the sheets to be imported to Sql Server.
Click Next
Now click Finish
Now the wizard imports the data from Excel to Sql Server and click Close.
Here is the table
answered Apr 30, 2015 at 16:17
Sarath Subramanian
21.6k12 gold badges89 silver badges95 bronze badges