Link to text file containing numbers with thousand delimiter like 123.456.789,44

[Bjorn]

In Access I am linking to a textfile produced by an external system. This textfile unfortunately contains numbers with dot as thousand separator and comma as decimal symbol. Example: 123.456.789,44



I need to have this as any kind of numeric field in Access. Unlike Excels import wizard, it is not possible in Access to set the thousand separator (only the decimal symbol).

I have tried importing this as a text field, and then created a query with a calculated field like this: AdjAmount:Replace([Amount];".";"")*1
This technically works, and the "multiply by 1" automatically converts this into a number. Using this technique, I can make calculations with the field "AdjAmount" in Access and everything works and looks fine, but....

... in our company we are using the technique to get "MS Access" result via "MS Query" in Excel. From Excel (via MS Query in Excel) we link to tables och queries in Access and that works fine. Linking the the table containing the field "Amount" (as text field) also works fine. Also linking to a query just showing all fields works fine. But when linking to the query with the conversion mentioned above (Replace...), it gets hooked in MS Query when linking. I get the error message (translated from Swedish): "Couldn't access the table... [my Access database filename]". This seems like a very strange error code, and my guess is that it is because the technique used to create the field "AdjAmount" is not compatible with MS Query in Excel. Therefore I have to find another technique!

I have tried to find documentation about different "normal" conversion built-in-functions, but were not succesful. I am not very familiar with them, and I haven't found anyone after searching a litte, but here there might be a solution (or at the import stage...).

Any suggestion?

I can not keep it as text-field in MS Access (and convert it to a number in Excel), since several calculations must be done with the field AdjAmount in Access.
I can not change regional settings
I can not influence the layout of the text field
I also want to avoid too comlicated solving techniques, since this (and similar cases) will be handled by "not-too-experienced users" (they usually do import/link of tables and create queries using the normal design tool, no VBA programming, no direct SQL-writing)

Thats quite a challenge, or what do you say!?!

[RuralGuy]

It sounds like you have your arms around the problem, just no solution yet. It also sounds like you say you could import the value correctly into Excel because the import wizard has more parameters. How about importing into Excel first and *then* into Access from Excel?

[Bjorn]

Hi there and thanks!
When I saw your suggestion I got a smile on my lips; so easy and still so clever!

It worked to 99%, with the very little extra "manual" work to remember (1% :-) that we have to open the first Excelfile (that links to the textfile) and "Agree to automatic updating" and then save it, otherwise a change in the textfile doesn't follow the whole link chain.

However, your solution is now implemented and a great THANK YOU !!

[RuralGuy]

You're very welcome and thanks for marking this thread as Solved.