Excel + ODBC
I have data in around 50 spreadsheets that I want to combine into a database or single file. Apparently MSF provide an ODBC for Excel which I have successfully used to create a connection to a worksheet. But I cannot tell the table names in the 'database'.
Help ajamaa out.
posted by aJamaa @ 08:34
1 Comments:
I was born late after ODBC went out of fashion, meaning I don't how to do it in ODBC.
There are 2 ways.
1. DTS/SSIS
Install SQL Server 2000/2005 and import data using a wizard. Straight up. Eeezy Peezy. Export to Paradox, Oracle, Access, Excel, Informix, files or any other database that has a driver installed via the same wizard.
2. OLE DB
Open VB/C#. Net. Connection string ...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\hollahoppitooo.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
HDR says that the first row has column names.
DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
//blady erra!
}
//create some loop here
string wrksheet = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();
// Now we have the table name; proceed as before:
OleDbCommand cmd = new OleDbCommand ("SELECT * FROM [" + wrksheet + "]", dbConnection);
OleDbDataReader reader = cmd.ExecuteReader ();
To do the same in VB 6..
Dim rs as ADODB.RecordSet
Dim ADODB.Connection as conn
Set rs = conn.OpenSchema(adSchemaTables)
While Not rs.EOF
somevariable[i] = rs!TABLE_NAME
rs.MoveNext
Wend
Next....
Use the 'tables' and
select * from somevariable[i]
caveat: ideas presented not completely thought through and might not work
Post a Comment
<< Home