Search code examples
csvms-accessinteger

CSV Import Decimal in Integer Access

I have to import a .CSV into Access on a daily basis. One of the fields is causing problems. The value is stored as '5,000'. This is not '5 point 0', but '5000' (five thousand).

I've tried multiple combinations of decimal symbols and data types but I just can't seem to be able to cast the number to '5000'.

Example line in CSV:

*Item;Supp Part Ref;description;Source;Descr;OrderCode;*Dest;LT;UPO;OrderPlaceDate;ActionDate;ReqdQty;Open Qty;RCVG QTY;OrdLineStat;FlmtPlnr;BuyerName
1929637;1929637;Ppc screw;XFV;VOSS FLUID GMBH;G;ODC;21D;23925340001;27/06/2017;06/09/2017;5,000;5,000;0;30;DA;WILL VERMEULEN

Current Import Specs:

CSV File Import Specs

What options do I have, preferably without having to alter the data source.

Solution

  • Import from text/xls may have a lot of other "surprises", so most reliable way is importing into temporary table where all fields have Text data type, analyze of data and then copy to working tables. During coping you can do any conversions.