locked
VB.net + Datareader + importing from Excel RRS feed

  • Question

  • Greetings all,

     

    I have a bit of a problem that bugging me and hoping someone can help.

     

    I'm importing from Excel using a datareader that's passing parameters to a stored procedure.

     

    Here's the problem. The Excel template has columns that are numbers stored as text. If no changes are made to the spreadsheet, everything imports fine. If something is changed, i.e. a number is chanaged, the records' field is null when imported. It's not reading it from the spreadsheet.

     

    Any ideas on how I can import a variant of sort regardless of the type in Excel?

     

    Thanks,

     

    Adamus

     

     

    Friday, July 20, 2007 4:58 PM

Answers

  • Thank you,

     

    I have found a shortcut that seems to work for the moment. The Excel template contains macros that format the information prior to import. As part of the macro, I've incorporated a type change for specific columns by adding an apostrophe to the beginning of each entry so regardless of type, it becomes universal in Excel and is changed later in the db. This seems to be the optimal approach resulting in minimal overhead.

     

    (Without knowing, I'm guessing the apostrophe acts as a string identifier.)

     

    Thank you,

     

    Adamus

    Monday, July 23, 2007 12:11 PM

All replies

  • It is because Excel spreadsheet contains mixed data. Excel files are not really databases and do not have specific column types. What happens in this case is when provider reads data from specific column, it tries to guess type of the values in it based on scan of N first rows. Assuming that it decided that it will be numeric column. For all other values, which are not numeric, it will return NULL value. What you could do is to set IMEX value of the extended properties of your connection string to 1. It will force Jet provider to return all the values as strings. In this case you would need to deal with types yourself.

    Another way is to use Excel automation, but it could be slow for large chunks of data. When I faced that situation couple of years ago, I decided to write my own component for that. You could try it to see if it fits your need.

     

    Monday, July 23, 2007 10:50 AM
  • Here is link how to use IMEX property

     

    http://support.microsoft.com/kb/257819/en-us

    Monday, July 23, 2007 10:53 AM
  • Thank you,

     

    I have found a shortcut that seems to work for the moment. The Excel template contains macros that format the information prior to import. As part of the macro, I've incorporated a type change for specific columns by adding an apostrophe to the beginning of each entry so regardless of type, it becomes universal in Excel and is changed later in the db. This seems to be the optimal approach resulting in minimal overhead.

     

    (Without knowing, I'm guessing the apostrophe acts as a string identifier.)

     

    Thank you,

     

    Adamus

    Monday, July 23, 2007 12:11 PM