How can i import a excel spread sheets to a database?

How can i import a excel spread sheet information to a table?

Regards

Karen

Save it as a CSV file(comma seperated values). Each line will refer to a row in excel. Then know the order of the cells in excel and loop through all the lines in the csv file and insert into database. If there is a value missing for one of the fields then two commas will appear next to each other. Just make sure you don't have commas in your data. You can also change the delimeter to use instead of a comma if you do happen to have commas in your data. Hope this helps.

Eric

|||

Eric,

Thanks for ur answer. Actually i am building a website where the user will upload 5-6 excel spread sheets.. and i want to save that excel sheet, i am using a file upload control, and then i want to just take the spread sheet they provide and load it in to a sql server database... so is saving it to .csv file the only option. Any ideas will be appreciated.

regards

Karen

|||

Its the only method that I'm aware of. The problem stems from the cell titles in Excel not being the same column name in a sql database. Also the dataformats might not be the same. Sorry but its the only way I know and the safest. You may want to do some more research though.

Eric

|||

Thanks for ur input, But i will be creating tables in my sql Server database... that will match the excel spread sheet. How bout running a DTS a package that transfer a data thru a stored procedure?

Regards

Karen

|||

Its worth a shot. Im sorry but I'm not too familiar with exporting excel to sql server. I've done it a few times through stored procedure where I loop through lines in CSV and import data, but thats about it.

Eric

|||

Hi Karen, did you have a look at this article...http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

And this:http://support.microsoft.com/kb/321686

|||

Karen,

Bullpit's method is a much better approach. I would go with that one.

Eric