Importing .txt files

[4kitty]

I am trying to import a .txt file into an Access 2007 table. The file contains values in Euro format. How do I import them so they so as a value in US format.

This is how it is in the file: 1.200,00
I want it to import as: 1,200.00

Can it be done easily. I tried changing the specs to show the decimal as a comma, and it works for anything under a thousand. Anything over a thousand does not get imported.

Any help would be greatly appreciated. Thanks!

 

[HGMonaro]

if this is a once off... I'd edit the text file

otherwise (without knowing if there's a fancy way of doing this) I would
- import using the comma to split the number into two fields (eg Whole/Decimal)
- Use a query to multiple the Whole field by 1000 and add the Decimal (divided by 100) thus getting a number that can be used for numerical purposes. (ie. Number = (Whole*1000)+(Decimal/100)

my 'method' will fall in a miserable heap if multiple "."s are inserted into the data for larger numbers... what happens for a number greater than 99999?

Cheers, Nige

 

[Galaxiom]

Import as text.

Use Replace(fieldname, "." , "") to remove the dots.
Use Replace(fieldname, "," , ".") to replace the comma with a dot.

Append the records to a table with the correct field types.

 

[4kitty]

Thank you for the simple solution. It works great.