CODEWITHSUNDEEP
excelms-accesscsv
user1986202
user1986202

Reputation: 31

Avoid numbers from getting truncated when copying from CSV to access

I am using this code to copy data from csv file to access tables:

str = "insert into tablex (field1, field2, field3) 
select field1, field2, field3
from
 [Text; 
FMT=Delimited; 
HDR=YES; 
CharacterSet=437; 
DATABASE=" & ThisWorkbook.Path & "\data-for-tool-refresh].csvtable.csv;"

But after the data gets pasted certain columns get truncated i.e. decimal values get removed. I have checked the datatype off the access tables it is set as "double" but still values get truncated ex: 15.12345 becomes 15

Can someone please suggest me a way to get these values into access without getting truncated

Upvotes: 3

Views: 694

Answers (1)

grahamj42
grahamj42

Reputation: 2762

The best way to import external data accurately into Access is to describe the external file.

This can be done by an Import Specification (the Advanced tab in the Import Wizard), which can be referenced by a macro or VBA.

If you need to stick with the ODBC text driver, as in your question, you need a schema.ini. http://office.microsoft.com/en-us/access-help/initializing-the-text-data-source-driver-HP001032166.aspx?CTT=1 explains this. A big downside is that the specification is linked to the filename.

Upvotes: 1

Related Questions