none
Visual Studio 2005 (VB) ADO.NET 2.0 OLE ODBC SQL import of Excel data RRS feed

  • Question

  • I have been working on a Visual Studio 2005 (VB) project to unify real estate data from many different data sources into a central database.  The remote data includes .xls Excel worksheets, .csv data interchange files, HTML text and tables, Word and WordPerfect text, and various independent database formats.

     

    In experimenting with Visual Studio 2005 (Standard) and SQL Server 2005 (Express), I have had limited success importing Excel workbook data (.xls) using OLE and ODBC drivers, but no success importing with SQL 2005 Express. Specifically, I am able to read data from the remote files, but cannot manipulate data table mappings because the remote schema is not being imported.  This prompts several questions:

     

         1) The Excel worksheet is in HTML table format with column names in row 0, so why doesn't this schema show up when the data is imported?

     

         2) Will SQL Data Translation Services (DTS) import tabular data from Excel and .csv files? Is the full version of SQL Server 2005/2008 required or is DTS not present in the express versions?

     

         3) Will LINQ help with this type of data import?

     

    I was hoping to find a useful tool to build and maintain schema mappings between the remote and central database schemas, rather than writing VB data translation routines for each data source.  Any ideas? 

     

    Thanks,

     

    -Brian

     

    Wednesday, February 27, 2008 10:09 PM

Answers

  • Thanks for everyone's help. I got ADO.NET to recognize both schema and data from the Excel file using the following code sequence:

     

    strCS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFile.xls;Extended Properties="HTML Import;HDR=Yes;IMEX=1;"""

     

    Dim oleCON As New OleDbConnection

    oleCON.ConnectionString = strCS

    oleCON.Open()

     

    Dim oleCMD As New OleDbCommand("SELECT * FROM [Table]", oleCON)

    Dim oleDA As New OleDbDataAdapter(oleCMD)

     

    Dim oleDT As New DataTable()

    oleDA.Fill(oleDT)

     

    DataGridView1.DataSource = oleDT

     

    Now that the remote data and schema is recognized, the next step is data translation.  Assuming DTS is not included in SQL 2005 Express, any suggestion as to where I can see the capabilities of SQL DTS and possibly obtain a developer version at reasonable price?

     

    Thanks,

     

    -Brian

    Friday, February 29, 2008 3:55 PM

All replies

  • 1) First of all Excel worksheet is not in HTML format and it was no predefined fixed schema and it does not treat first row as a column header. Another challenge is that Excel also does not contain fixed data types for each column and data type varies for each specific cell. There is no universal way to work with Excel data and you would need to identify group of formats and try to build logic based on those groups.

     

    2) If I remember correctly DTS shipped with SQL Server Enterprise and Developer editions and definitely not with Express edition. You cannot get all the tools for free.

     

    3) LINQ might help you with querying data after it is loaded, but you still need to know what to load and how to treat each specific column.

     

    Thursday, February 28, 2008 10:52 AM
    Moderator
  • Val, thankyou for the informative reply and here is some clarifying info:

     

    1) Yes, the .xls file IS in html rather than Excel binary format.  I don't know whether this is due to the file being written as data interchange or by Office 2007. Opened in Notepad, file data starts with an <html> tag, followed by <td> column IDs and <tr> row delimiters:  <html><body style="font-size:10pt;font-family:Arial; border: gray 0.1pt outset;"><TABLE cellpadding="3" cellspacing="5"><TR style="font-weight:bold;"><TD>Age</TD><TD>Asking Price</TD>...etc. In Excel the file opens just fine, but 'save as' defaults to file type of 'web page'.  Saving as an .xls binary also works, but in VS a different connection string is necessary to open the file.

     

    2) In VS I can open either excel file format with an appropriate connection string, and can read individual columns and rows with a data reader, but how do I infer a data structure from the row 0 column names and records in rows > 1?  After the connection.open(), I have tried creating new oledbDataAdapter(), new dataset(), new datatable(), and binding the open connection to a datagrid, but haven't got the object creation syntax sequence quite right to allow me to explore the data and structure of the remote file.  Why wouldn't the column and row data simply display on a datagrid? 

     

    Any further guidance you can provide is thankfully appreciated.

     

    -Brian

    Thursday, February 28, 2008 11:35 AM
  • Brian,

    You might take a look at this small program - it converts an excel file to a DataTable which fits into the ADO.NET world:    I'm only a part-time programmer, but you can bind a datatable to a datagrid (once you import properly).

    Tom

    Thursday, February 28, 2008 11:49 AM
  • The issue might be related to the fact that since Excel does not have column data types and does not have predefined structure of the columns. What your connection string look like and what do you get when you create DataTable and load it? Is it empty DataTable?

     

    Friday, February 29, 2008 10:44 AM
    Moderator
  • Thanks for everyone's help. I got ADO.NET to recognize both schema and data from the Excel file using the following code sequence:

     

    strCS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFile.xls;Extended Properties="HTML Import;HDR=Yes;IMEX=1;"""

     

    Dim oleCON As New OleDbConnection

    oleCON.ConnectionString = strCS

    oleCON.Open()

     

    Dim oleCMD As New OleDbCommand("SELECT * FROM [Table]", oleCON)

    Dim oleDA As New OleDbDataAdapter(oleCMD)

     

    Dim oleDT As New DataTable()

    oleDA.Fill(oleDT)

     

    DataGridView1.DataSource = oleDT

     

    Now that the remote data and schema is recognized, the next step is data translation.  Assuming DTS is not included in SQL 2005 Express, any suggestion as to where I can see the capabilities of SQL DTS and possibly obtain a developer version at reasonable price?

     

    Thanks,

     

    -Brian

    Friday, February 29, 2008 3:55 PM