Importing number fields that contain commas

[ebrooks54]

Nobody ever said my job would be simple. I have a tab delimited text file that needs to be imported and processed. Unfortunately the file, as received has formatted numbers with commas separating the thousands: 5,916 for example. A straightforward import fails as it won't recognize as a valid number. It is not possible to get the source file changed.

I have a work around that seems to work:

[b][COLOR=red]OnHand: IIf(IsNull([Indigo POS Data]![DC OH]),0,Val(Replace([Indigo POS Data]![DC OH],",","")))[/color][/b]

This involves importing it as text, then using a query to take the Value of the field after stripping the comma out. It is cumbersome, but it works.

Question is, am I missing any solution that is simpler?

 

[BobStubbs]

If you also have Excel available, do this:

-- Open the .txt file in Excel
-- The Excel import wizard gets this file format correct, and will import all numbers including those with commas.
-- Save the file in Excel .xls format
-- Import the .xls file into Access - this worked OK when I tested it (Access / Excel 2000 and 2003)

This will only work if your .txt file contains less than 65,536 records, as this is Excel's row limit.

By the way, your numbers will be imported without nthe commas.


Bob Stubbs