CSV Import Decimal in Integer Access

Question

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:

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

Sergey S. · Accepted Answer · 2017-09-05 11:27:44Z

1

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.

answered Sep 5, 2017 at 11:27

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gustav Over a year ago

This method will work as there is no option to set the thousand separator during import.

TheNiers Over a year ago

The file holds roughly 30000 record for any given day, would this method still be feasible?

Sergey S. Over a year ago

That's no too much. Yes, will work. But you will need to compact the db sometimes.

Thomas G · Accepted Answer · 2017-09-05 11:28:01Z

0

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.

answered Sep 5, 2017 at 11:28

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Comments

TheNiers · Accepted Answer · 2017-09-06 08:49:08Z

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.

Collectives™ on Stack Overflow

CSV Import Decimal in Integer Access

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related