ODBC connection returning wrong data type

mayre

New Member
Joined
Sep 28, 2005
Messages
16
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 ?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Pete_Bristol

Board Regular
Joined
Mar 8, 2006
Messages
122
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top