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 ?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
 

Forum statistics

Threads
1,141,918
Messages
5,709,339
Members
421,630
Latest member
MJ79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top