Automate Importing Multiple Excel Worksheets into SQL

Question

In VB6:

I need to allow a user to select an Excel file, and then upload All Worksheets to a SQL table. I'm not sure how to do this. The format of each sheet is exactly the same. And the worksheet format matches the SQL table format.

I'm really banging my head against the wall over here. If anybody could offer a suggestion, or a code example, etc., I would REALLY appreciate it.

Thanks guys.

Answer 1

You can use the ODBC Provider for Excel files. Here is some code written in javascript to import a Excel file to a SQL Server Table. Each Worksheet is treated as a single table. There are some troubles with data types because the ODBC Driver deduces the data type of each column by reading it's first values, so if a column has numbers in the first rows of data the whole column will be read as numeric and every "non numeric" value will be read as NULL.

var objCat = new ActiveXObject("ADODB.Connection"); 
var objRSExcel = Server.CreateObject("ADODB.Recordset");
objRSExcel.CursorLocation = 3;
var file =  "imported_file.xls";
var TableName = "[Sheet1$]"

// Database Creation
objCat.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";                    
objCat.Open();
objRSExcel.Open("SELECT * FROM " + TableName, objCat);

var rsInsert = Server.CreateObject("ADODB.Recordset");
rsInsert.Open("SELECT * FROM [TARGET_TABLE]", cn, 1, 3);

while (!objRSExcel.EOF) {
    rsInsert.AddNew();
    for (var j=0;j<objRSExcel.Fields.Count; j++) {
        rsInsert.Fields(j).Value = objRSExcel.Fields(j).Value;
    }
    rsInsert.Update();
    objRSExcel.MoveNext();
}
objRSExcel.Close();
rsInsert.Close();