Excel and ODBC

Excel and ODBC

• Knightendo

  
  So are you saying that you have both currency and date data in the same column
  
  

• Missouri Mule

  
  The Excel ISAM driver will treat the column as text under the following circumstances:

  1) The cells in the column have been formated as text prior to entering the data.
  2) There is alphanumeric text in one of the first eight cells of the column.

  
  
  

• Pradeep Gupta

  I feel your pain.

  It may be easier if you can export the Excel spreadsheet to a tab delimitted format, define the file as all text fields with a schema.ini file, and read the records and parse the text fields.

  A text file can be used with the Jet provider and a slightly different connection string.

  Good luck.

  
  
  

• medel

  
  Use Jet OLEDB instead and specify the IMEX argument:

  Dim ConnectionString As String

  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Documents and Settings\...\My Documents\Book1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

  Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

  ExcelConnection.Open()

  
  
  

• Tdar

  That fixed part of it. Now many of the missing numeric columns are being filled correctly. Unfortunately the example I gave still isn't. There is a column of currency data (e.x. $197,284.23) that happens to have a date at a higher cell value. All the cells are being converted to dates.

  Still not fixed...

  
  
  

• DroopyPawn

  Are you sure that cells converted to dates When you set IMEX=1 then it means that all the values treated as strings and there is no datatypes conversion. Basically all the columns in this case will be of string type. What is your connection string looks like now

  
  
  

• The_Nod

  Also, I've tried pre-setting the column types in the DataTable before fill and the Adapter simply adds more columns and ignores the ones I added.

  I also tried setting the column data type after the fill, which threw an exception.

  
  
  

• SparkyCH

  You are not the only person who has these issues. I also had similar issues and to keep with the good level of the performance I was forced to create my own reader that reads cells preserving their types without using Excel or Jet. You could try it from my site and it could be solution for you.

  
  
  

• Richard Glanmark

  I understand that's what's going on. Isn't there a way to set all the column data types to be string ! This would completely eliminate the problem. Supposedly this is what IMEX does, but it doesn't work.

  Requiring excel to be installed is not an option. I don't need the application to be dependant on more modules.

  I also understand I could query specific ranges, but this is also not ideal. This requires more hardcoding, and honestly i'd just like to pull in the data as all strings. This way I can search for what I'm looking for and cast any values I need. As you can guess this spreadsheet is not static.

  Am I the only one that's run into this issue This seems more like a bug or huge oversight.

  If there isn't a built-in way to do this surprisingly, wouldn't there be someway to overload either the DataAdapter or DataSet/DataTable to set columns to strings only

  
  
  

• martinabc

  That's not an option. My client obviously needs to extract data from their excel sheets. At this point I'm quite dissapointed that it's becomming this hard. I'm about to learn the .xls file format and parse it myself.

  Is there seriously no work-around for this

  
  
  

• Anderss22

  
  The problem is that the Excel ISAM driver wants to choose a data type for the column based upon the contents. Unfortunately it cannot be both a Date and Currency (or Double).

  Not sure what the rest of the Worksheet looks like but if you need this type of flexibility you're looking at an automation solution instead (which of course requires that the Excel application be installed). If you know exactly where to look for the date and currency data then it would also be possible to independently query the data by an Excel Range.

  BTW, the HDR argument indicates whether to use the first row as column names for the resultset. If the value is No then the default column names are F1, F2, F3, etc.

  
  
  

• Philipp Lamp

  My connection string:

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""

  As I understand it HDR should be set to off. This is instructing that the first column values are the headers. I haven't seen any difference with this on or off.

  Yes I have currency and date data in the same column. More or less it looks like this actually:

  1/1/1111

  <blank cells>

  $157,857

  and on

  and on

  The cell to the left of the date is 'Invoice date'. This excel sheet is meant more for human readability than what an excel sheet would look like if it was extracted out of a relational database. I merely want to extract certain pieces of data. When I first parsed the file though I was missing column values. So I just went ahead and bound the data to a datagridview. The IMEX suggestion helped alot of the missing data appear, but one of the columns is coming up as all dates.

  It's certainly clear in the .xls file (viewing with excel) that the data in these cells are currency.

  It honestly shouldn't be this hard to extract a few values from an excel sheet. Even if the correct data types aren't pulled, casting from or parsing a string isn't a problem.

  
  
  
• Excel and ODBC