0

I am reading a .xls document into a dataset and I am having a problem that it is passing the entire column as a wrong data type. It thinks that the column is number based, when it should be string based. I have a lot rows that are just numbers then only few are letter, 1 or 2. I need it to treat the entire column as letters instead of numbers.

What is happening right now is when it encounters those 2 rows it reverts them to null and passes in everything else. I remember reading somewhere that excel determines the row type by first 8 columns, now if this is the problem does anyone know how I can disable this functionality and tell it that this column is a string.

I tried: Highlighting entire column > Right Click > Format > Selected Text and it still does not works.

Also tried: "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + FileUpload1.PostedFile.FileName.ToString() + ";Extended Poperties=Excel 8.0;HDR=Yes;IMEX=1"

And: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileUpload1.PostedFile.FileName.ToString() + ";Extended Poperties=Excel 8.0;HDR=Yes;IMEX=1"

And: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileUpload1.PostedFile.FileName.ToString() + ";Extended Properties=\"Excel 8.0;HDR=YES\";"; Error I get here is: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Bagzli
  • 6,254
  • 17
  • 80
  • 163
  • Are you reading using AdoDB? Does this help? http://stackoverflow.com/questions/16718739/excel-to-ado-recordset-has-empty-values-for-numeric-cells/16721946#16721946 – mr.Reband Jun 19 '13 at 18:42
  • My connection string is: "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + FileUpload1.PostedFile.FileName.ToString() - If you could tell me how to append this to include what those guys are speaking of it would be of great help, I think that will solve the problem. I just don't know if that can work with my connection string. If you do answer this, please post it as an answer so I can mark it correct. – Bagzli Jun 19 '13 at 18:47
  • That looks like an ODBC Connection String -- if that's the case, you can go to the ODBC Data Source Administrator on the machine that runs this, configure the Excel Driver, expand Options, and manually set the `Rows to Scan` there. – mr.Reband Jun 19 '13 at 19:05
  • This will be sitting somewhere on the web, and many pcs might be using it. Is there another connection string that would fix my problem? – Bagzli Jun 19 '13 at 19:08
  • Regarding your second connection string, newer machines don't use the `Microsoft.Jet.OLEDB.4.0` driver - instead they use `Microsoft.Ace.OLEDB.12.0`. More info here: http://stackoverflow.com/questions/14401729/difference-between-microsoft-jet-oledb-and-microsoft-ace-oledb – mr.Reband Jun 19 '13 at 19:11
  • I tried ACE now as well, but I get same error, I edited my question show it. – Bagzli Jun 19 '13 at 19:25
  • Did you install the driver? The SO topic I posted has a link to it. – mr.Reband Jun 19 '13 at 19:32
  • no its not installed, I have the ODBC driver, just checked. I don't think I fully understood how this worked before. I'm using windows xp still believe it or not with office 2007, and I highly doubt my administrator will approve the install. Is there anything I can do to achieve what my original question stated using my original connection string or any way at all really? – Bagzli Jun 19 '13 at 19:56
  • Sorry, I'm all out of ideas. I found this article that suggests to convert to csv before reading, but even that solution would require a JET or ACE driver: http://stackoverflow.com/questions/10556947/importing-excel-errors-odbc-and-schema-ini – mr.Reband Jun 19 '13 at 21:43

2 Answers2

0

Edit the first cell of the row so that it starts with the apostrophe character. I had a similar problem and as I recall it infers the data type from the first value in the cell and this did the trick for me.

dazedandconfused
  • 3,131
  • 1
  • 18
  • 29
  • I can't do that, the values are generated randomly, sometimes there won't be apostrophe character in the cell. – Bagzli Jun 19 '13 at 18:49
  • In that case, look at the registry change mentioned here... http://stackoverflow.com/questions/2567673/how-to-force-ado-net-to-use-only-the-system-string-datatype-in-the-readers-table – dazedandconfused Jun 19 '13 at 18:54
  • 1
    I can't go and change the registry of every person who comes to use this website. – Bagzli Jun 20 '13 at 12:14
  • First, you didn't mention in your OP whether this was a site, a client app, or a utility used only by one person. Better questions get better answers. Secondly, the registry change would only have to be made on the server performing the import not on the client. – dazedandconfused Jun 20 '13 at 15:45
0

I've solved the problem using DTG.excel, its a solution that requires a purchase however it is the only way I was able to do this using without having to use the ACE drivers.

Bagzli
  • 6,254
  • 17
  • 80
  • 163