Automating the import of multiple Excel tables into SQL
In VB6:
I need to allow the user to select an Excel file and then load all worksheets into an SQL table. I'm not sure how to do this. The format of each sheet is exactly the same. The worksheet format follows the SQL table format.
I'm really banging my head against the wall here. If anyone could offer a suggestion, sample code, etc., I would REALLY appreciate it.
Thanks guys.
You can use ODBC provider for Excel files. Here is the code written in javascript to import an Excel file into a SQL Server table. Each worksheet is treated as a separate table. There are some problems with data types because the ODBC driver infers the data type of each column by reading its first values, so if the column has numbers in the first rows of data, the entire 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();
source to share