Importing numbers from txt with thousands separator problem

[poltroller]

Hi. I have this problem at the company I'm working for. When we try to import numbers with points as thousands separator, Access signals an import problem.
And that's fine, I've seen several web pages about this issue.
Then I've seen that if I set the data type as numeric and single (or double, but not integer), access imports the number, without the separator (which is not a problem because we just need the number).

I have two questions:

- why access recognize the number if I set a no integer data type?


- we have several access database with very long queries (which I will try to improve in the next days). If I switch data types from Long Integer to Single, should I expect
a considerable loss of performance, consideraing that sometimes we have 400.000 records but few limited count and sum operations

Thank's for your kind help.

[June7]

Don't know why the integer type confuses Access.

I don't think the Single type should cause performance issue. Have you tested?

[poltroller]

Hi. Thank's for your reply.

I haven't yet tested it because it's a complicated system of different acccess databases. My job, other than elaborating the data, is to improve it. I've seen that during different times of the day the performance of the queries changes.
So before making any change I just wanted to see if someone had this same experience. Right now they simply clean the .txt files with numbers with a point as a thousand separator. But I think is really annoying cleaning 26 txt files everyday.

[June7]

After more reflection on your original post, I wonder why Access recognizes the value as a number at all. I would think presence of multiple points or any commas would be seen as text string, not number, and the import would error. Are you using the import wizard? Must be something special built into the import engine. Makes me also wonder what would happen with comma thousand separator and point for decimal. What are you importing from - txt file?

[poltroller]

I'm importing numbers which count quantity of sales. So I can't use the currency data type.
The fact is that numbers more than 999 don't occur too often so I need to balance the time I lose making checks in the txt file (usually I have time because the extraction from the main data system is very slow) with the time I would lose eventually with a more slow elaboration process. I don't know if I could automate the .txt files cleaning that's because I have customers codes with points and numbers too.

I've tried different imports using the wizard.
This is the original file:

1000;1001;1.000;1.001;1.001,01;1,000;1,001;1,001.01

Import with "," as decimal separator and "." as thousands. All Campo settled to integer long
except for Campo8 and Campo5, settled as double:

ID	Campo1	Campo2	Campo3	Campo4	Campo5	Campo6	Campo7	Campo8
1	1000	1001			1001,01	1	1	1,00101

If I settle Campo8 and Campo5 as integer long this is what happens:

ID	Campo1	Campo2	Campo3	Campo4	Campo5	Campo6	Campo7	Campo8
1	1000	1001				1	1	1

This, if I settle all Campo to double:

ID	Campo1	Campo2	Campo3	Campo4	Campo5	Campo6	Campo7	Campo8
1	1000	1001	1000	1001	1001,01	1	1,001	1,00101

For the first try, this is what happen when choosing "." as thousands separator:

ID	Campo1	Campo2	Campo3	Campo4	Campo5	Campo6	Campo7	Campo8
1	1000	1001	1	1	1

[June7]

What a nightmare! Why does the file have mixed thousands and decimal separator styles or not using any thousand separator in some values? Can't the file be constructed with consistency?

[poltroller]

Wait, that is just a file I was using to look at access behavior with different kind of combinations. The empty fields are of course mistakes.

[June7]

Oh, okay. So what is the text file data structure and what structure do you want in the database?

Does your text file use comma for decimal? Have you set computer regional settings to recognize this?

With setting for dot as decimal, use of Double type causes comma separators to be ignored, all values will import as numbers, no error table. However, if values use comma for decimal and dot for thousands, the import is wrong.