andybster
Posts: 3
|
| Posted: 03/22/2006, 4:07 AM |
|
I am importing an XLS spreadsheet to update an online Access database. However, one of the columns in the spreadsheet has some cells in the format "100 pcs" - a pack description meaning that product has 100 pieces per unit. I have tried like hell to get this field imported but without luck.
Is there a way that I can 'force' the ASP to retrieve the cell as text irrespective of its content?
_________________
Andy B |
 |
 |
andybster
Posts: 3
|
| Posted: 03/22/2006, 4:22 AM |
|
I have found (on Microsoft's site) that the driver cannot distinguish mixed format columns but at what point does the driver determine the type for the column? I have tried making the first row (after the description row) a text entry 'two' and it STILL treats the entire column as numeric.
Is there somewhere in Excel that I can pre-process the column which will make the driver treat the column as text?
_________________
Andy B |
|
|
|
andybster
Posts: 3
|
| Posted: 03/22/2006, 7:01 AM |
|
Obviously, this forum is mainly used by Americans so my post at 4am has not received any replies. However, in the meantime I have found a solution:
The Excel Jet driver looks at your columns and 'decides' what type of data it is looking at!! (Good one Microsoft) - the majority wins so if numeric is chosen, any text entries are treated as null. You can use IMEX=1 in the Extended Properties of the connection string but the driver STILL looks ahead - there is also a registry setting you can change: "TypeGuessRows" - mine was set to 25 - I decreased mine to 5 (3 of the first 5 were text) so it then worked.
Phew!! You can find where I discovered this at:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q...ml?qid=21036929 although I think you need to register to see the solution.
_________________
Andy B |
|
|
|
|
