ODBC connection returning wrong data type

[mayre]

I am using an ODBC connection to retrieve data from a .csv text file. One of the columns being retrieved contains Fx Rates and hence requires decimal places but the query only returns integer values. Even using Microsft Query shows the Fx Rates as being integers but examining the raw text file shows they definitely have decimal places.

I though it might have been due to the parameter MaxScanRows as I believe this scans the first few lines of the text file to establish data types. If the first few rows contain an Fx rate of 1 then the query assumes it must be an integer column type. I therefore changed MaxScanRows to sample the whole data set (I used ActiveCell.QueryTable.Connection = ".... to edit the connection) but it still doesn't work.

Any ideas how to force ODBC connection to recognise correct data type ?

 

[mayre]

Re-posting as haven't received any responses yet...

 

[Pete_Bristol]

Hello mayre,

I've been eagerly watching this topic as well because I had a similar problem a while back that I spent literally days trawling the net/newsgroups for an answer too.

My column of data had mixed numeric/string types that I wanted to force to text. The only advice I could find was to include "IMEX=1" in the connection string. This apparently forces everything to be read as text (however, I have to say, it didn't work for me!) . Perhaps you could read them as text and cast to doubles?

Anyhow, I'm sure if you type "IMEX=1" into google, you'll find out more!

Please post back any findings.


Good luck,

Pete