CODEWITHSUNDEEP
csvms-accessinteger
TheNiers
TheNiers

Reputation: 237

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.

Upvotes: 0

Views: 1492

Answers (3)

TheNiers
TheNiers

Reputation: 237

After some fiddling around I managed to get it working with the following workaround.

First, I link the CSV file where I set the datatype to text for all fields (credit goes to Sergey S.).

Next, I created a local table which is, format wise, a copy of the CSV file. But in this table I have set the required fields to Integer, Date, etc.

Last, I used the following append query (notice the Replace function) to fill that table:

INSERT INTO Tbl_JDA ([*Item], [Supp Part Ref], description, Source, Descr, OrderCode, [*Dest], LT, UPO, OrderPlaceDate, ActionDate, ReqdQty, [Open Qty], [RCVG QTY], OrdLineStat, FlmtPlnr, BuyerName)
  SELECT
    [*Item],
    [Supp Part Ref],
    description,
    Source,
    Descr,
    OrderCode,
    [*Dest],
    LT,
    UPO,
    OrderPlaceDate,
    ActionDate,
    REPLACE([ReqdQty], ",", ""),
    REPLACE([Open Qty], ",", ""),
    REPLACE([RCVG QTY], ",", ""),
    OrdLineStat,
    FlmtPlnr,
    BuyerName
  FROM [JDA Orderbook];

I now have a local table with the right format which I can use in my other queries.

Upvotes: 0

Thomas G
Thomas G

Reputation: 10216

I guess you are on a machine with European regional settings and your data is from the US.

In the import wizard, somewhere you have "Regional options", then "Configuration" and there you can define what the decimal point is for numbers (typically a dot or comma), your choice should automatically adapt the thousands separator to the opposite.

Be aware that your CSV's zones delimiter should not be a comma.

Sergey's answer is also a good approach.

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

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.

Upvotes: 1

Related Questions