Excel to SQL Server database

[madhuri.t]

Hi,

I have to start working on a ASP which would actually automate the process of exporting the data from excel to SQL Sever database. Can you please tell me how to go about it.

Madhuri

[spinal007]

What experience have you got?
Have you worked with recordsets/connections before?

[madhuri.t]

yes I have worked on recordsets and connections before.

[spinal007]

Here's a great website for Connection Strings:
http://www.connectionstrings.com/

This is the idea:
1. Connect to the spreadsheet using this string:
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDi r=c:\mypath;"

2. Loop through the recordset doing the following...

Code: Select all

 
Do While Not excelRS.EOF
 
sqlRS.AddNew
For Each fld In excelRS.Fields
 sqlRS(fld.Name) = fld.Value ' which is the same as: excelRS(fld.Name)
Next
sqlRS.Update
 
excelRS.MoveNext
Loop

Done!

[madhuri.t]

Hi,

I am looking for a code which would help me importing the data in excel sheet to SQL Server table. I am good at connecting strings and stuff. Please let me know how to go about the importing part.

Regards,
Madhuri

[spinal007]

This is an ASP forum so I assumed you wanted to do it in ASP.
What do you not understand about my previous post?

[madhuri.t]

I understand the first part where you are trying to make a connection to spreadsheet but sorry I fail to understand the second part.
Do While Not excelRS.EOF

sqlRS.AddNew
For Each fld In excelRS.Fields
sqlRS(fld.Name) = fld.Value ' which is the same as: excelRS(fld.Name)
Next
sqlRS.Update

excelRS.MoveNext
Loop

I would have thought that we would create a table first and export the data from excel to SQL database (using ASP code). Sorry if I have confused you earlier.

[spinal007]

Well, have you used MS SQL before?
What software do you use to manage the database?

My assumption was that:

1. You know how to retrieve information from the excel spreadsheet

2. You have created a table in the SQL database with the same fields and in the same format as the table in the spreadsheet

The code I gave you is an algorithm to do the actual transfer of data from A to B.
It would be easier to help if you gave me some indication of what you have so far, and if you actually had a go at implementing something.

You can't expect me to give you the exact answer to your specific need...