Import Excel to SQL Server 2008

问题:

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.

回答1:

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:

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.

回答3:

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/

回答4:

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