How to Import many excel Files to sql server 2005?

caiguosen

I need to import many excel files to sql server 2005, These excel files have the same fields and different values. for example: table1 a b c 1 2 3 4 5 6 table2 a b c 22 33 44 77 88 66 Because there are many similar files in one directory, I want to import all these files into sql server 2005, and build these tables automatically. I don`t know how to do it?

Lost User

caiguosen wrote:

I want to import all these files into sql server 2005, and build these tables automatically. I don`t know how to do it?

There's more than one way to do so. You might be interested in this[^] KB-article :)

I are Troll :suss:

caiguosen

I know this solution, but I have several thousands excel Files, and their version are EXCEL2.1, I still do't know how to import so many excel2.1 files to sql server? I have such a solution,but I first need to save excel files to the latest version, and it only can import one file to sql server. for example: SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\tt.SZ.xls', 'SELECT * FROM [tt$]')

Lost User

caiguosen wrote:

I know this solution, but I have several thousands excel Files, and their version are EXCEL2.1, I still do't know how to import so many excel2.1 files to sql server?

You're opening the Excel-file as a database over JET - that means that you're importing from a lot of databases. You can't concatenate the Excel-files, as the Excel-format is limited to 65536 rows. There's an article on importing multiple files here[^] that might help.

I are Troll :suss:

caiguosen

Thank Eddy Vluggen very much! I'll try it.

Lost User

My pleasure, and good luck :)

I are Troll :suss:

caiguosen

the version of the excel files which i want to import is Excel2.1, if I use the following solution to import, it can not work. for example: select * into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\a.xls;HDR=YES', 'SELECT * FROM [a$]') The fact is that I have to open it and save it as the later version.Unfortunately,I have thousands of excel files so that I couldn`t open them one by one. Are there some solutions that I change their version quickly? or some solutions that can help me to import excel2.1 to sql server directly?

Lost User

caiguosen wrote:

the version of the excel files which i want to import is Excel2.1, if I use the following solution to import, it can not work.

Can you try it with the Excel 5.0 format, like this?

select *
into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=D:\a.xls;HDR=YES',
'SELECT * FROM [a$]')

caiguosen wrote:

Are there some solutions that I change their version quickly?

I don't know of any, you'd have to Google for those. I'd try to read the file using C#, and write it directly to a database :)

I are Troll :suss:

caiguosen

I tried following solution,but it still can not work. it can not import excel2.1 to sql server. select * into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=D:\a.xls;HDR=YES', 'SELECT * FROM [a$]') do you have some c# code that can import excel2.1 to sql server2005 quickly? I google some code which was written by c++,but most of them can not read excel2.1, and some of them can read, but the speed is really slowly. the problemis that I have thousands of excel files,not only one. or do you have some solution that can save many excel2.1 files as later version? if i open it and save as it, it can be imported.

Lost User

caiguosen wrote:

do you have some c# code that can import excel2.1 to sql server2005 quickly?

I'm sorry, but I don't have that code. If I did, I would've posted it.

caiguosen wrote:

I google some code which was written by c++,but most of them can not read excel2.1, and some of them can read, but the speed is really slowly. the problemis that I have thousands of excel files,not only one.

There's no "quick" way to get those Excel-files in the database. Version 2.1 is an old file-format, perhaps you could find a tool that converts those files in batch, but I doubt it. If you can read the files, even if it's slow, than you can convert them. If you can write a console-application that reads a single file, then you can re-use that to import the other files. If "slowly" means that it takes a minute, than 1000 files take 1000 minutes. That's roughly 16 hours, so the conversion would be done within a day.

I are Troll :suss:

caiguosen

ok，thank you for your help. I think I need to search other solutions, otherwise it will cost me too much time.