I am trying to find a solution to this for few days without any success. Would really appreciate any help
I am trying to import a csv file into an Access database but some data from a numeric column gets truncated.
I do not have MS Access installed in my machine(only the windows preinstalled jet database engine (msjet40.dll and msjetoledb40.dll)). But everything else works fine except this issue. I am using Provider=Microsoft.ACE.OLEDB.12.0 in the connection string.
The csv file contains numeric as well as non numeric columns in the file. As long as the numeric column in question has value less than around 2140649050 it selects fine.
SELECT * into ds1 FROM [Text;FMT=Delimited;HDR=YES;DATABASE=" & LFPath & "].[" & LFName & "]
Initially i assumed the truncation of data happens while loading data to the table. But the data gets truncated at the time of select itself. The below select query returns part of the data truncated for the UPC column
SELECT HeadName,[upc code],len([upc code]) as maxlen FROM [Text;FMT=Delimited;HDR=YES;DATABASE=" & LFPath & "].[" & LFName & "]
Here is the output for the same
HeadName upc code maxlen
TOTAL U.S 2140649030 10
TOTAL U.S 2140649050 10
TOTAL U.S 2140649050 10
TOTAL U.S
TOTAL U.S
TOTAL U.S
TOTAL U.S
TOTAL U.S
Seems it sets the maxlength of the column based on the initial few records. How can i prevent it from doing that.
Access is opted because of no extra setup is required for the tool to run in windows.
The data is not truncated when MS Access is installed(Tested with Access 2010). Does that mean ACE.OLEDB.12 works better with latest ACE egine ACECORE.dll (wiki)
Please suggest any possibilities to fix the problem without the extra load of installing other versions of database
As far as i could see and research i found 2 options we can go with. Both involves an additional step.
First option would be to sort the csv file in descending order of the column. In my case only one column was getting truncated. So i could sort the list based on the particular column in descending order and the data gets inserted without truncation. (could not find an automated solution to sort a file programatically. so we need to manually sort the file and then load to database)
The other option is to convert the csv file into an excel file and use the same to load the data. Here is the SO Post for the same