CODEWITHSUNDEEP
sql-servervbavb6exceldts
RNamo
RNamo

Reputation:

Automate Importing Multiple Excel Worksheets into SQL

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.

Upvotes: 3

Views: 1757

Answers (1)

Rodrigo
Rodrigo

Reputation: 4395

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();

Upvotes: 2

Related Questions