Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snake9284
Starting Member
10 Posts |
 Posted - 2007-11-09 : 09:12:24
|
| I need to load information from multiple excel workbooks into SQL server. I'm trying to figure out the best approch to do this. Each spreadsheet has the same column headings but the sheet names are different for each workbook (only loading first sheet from each book). I've collected the urls for each file into a database and was using the url to create a linked server to the workbook. I'm using sp_tables_ex to return the table_name in the linked server but cannot figure out how to capture this information to query the top table_name. I'm hoping to be able to pass this value as a parameter so I can select the fields from this table and the drop the linked server and loop through the other files in the same manner. Any ideas would be greatly appreciated. |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-11-09 : 09:30:15
|
| What you can do is to insert into a temporary table the result of the sp_tables_ex stored procedure. Then once you have it in a temporary table, you can loop through it using a cursor. For each worksheet in the Excel file, you can use OPENROWSET to read the contents of the Excel worksheet. For more information on reading an Excel file without creating a linked server, you can refer to the following links:http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspxhttp://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspxhttp://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspxSQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
snake9284
Starting Member
10 Posts |
Posted - 2007-11-09 : 10:09:55
|
| Thanks for the reply. Could you help me with the syntax for this? I tried to use select * into from sp_tables_ex 'ImportData' but this doesn't work...how do I write this to a temp table so I can query the tablename that I want to pass on to the variable? |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-11-09 : 10:23:13
|
| To insert it into a temporary table, you have to first create the structure of the temp table. Then once you have the table created, you do the following to insert into it:INSERT INTO #YourTempTableEXECUTE [dbo].[sp_tables_ex] 'ImportData'SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
snake9284
Starting Member
10 Posts |
Posted - 2007-11-09 : 10:32:39
|
| Thanks for you help, that worked perfectly. Do you think that I am taking the best approach to loading this imformation? |
|
|
|
|
|
|
Loading Excel Information