Import txt with Thousands Separator

[Glen Beer]

I've been trying to import comma delimited text files with mixed character and numerical fields.

The problem is that the numerical fields have a comma as the thousands separator, and I can't get around this.
Is is true that A5 can't handle this?
MS access and excel don't seem to have this problem at all and can import the text files without any problem.

Also, is there any way to change the text file extension default to *.txt instead of *.asc. Alpha 5 is the only program which seems to want to see the text files with an *.asc extension.

[Stan Mathews]

RE: Import txt with Thousands Separator

I think you could get around the separator by enclosing the data in quotes. Alpha would then interpret "1,000" as one field.

If this is coming from a spreadsheet, have you tried turning of the display of the separator before the import?

[Glen Beer]

RE: Import txt with Thousands Separator

Thanks, the data is in quotes.
The data shows as: "text1","123,456","text2"
I download the data from a service daily in 3 files with
100- 500 records in each. I can't be involved with manual manipulation.
Any thoughts?

[csda1]

RE: Import txt with Thousands Separator

Glen,

I see no way that any program could differentiate the separating commas from the thousand's separator without knowing the exact format of the data to import.

Possibly, you could specify a different separator (tilde ~ and vertical bar | are popular) or export the data in a different format, e.g. Table ASCII, Excel, etc.

Another thing you could do is write an import routine using Xbasic that reads the import file line by line and breaks it up by some algorithm.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[csda1]

RE: Import txt with Thousands Separator

Glen,

If the numbers are surrounded by quotes, then you should import the numeric data as a character field and then convert it to numeric through an update operation to another field.

You could also import it to a temporary table that reads numbers as character fields, and then append the data which could convert the field to a numeric value as part of the append.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Stan Mathews]

RE: Import txt with Thousands Separator

Glen,

Ira has carried my point to where you want to go. There is the additional possibility of not having to convert the values to numeric until you want to use them.

If you are just using them for reporting, there is no need to convert to numeric at all. If you need to use them in calculations, you can do so at the time of calculation by using

val(remspecial(fieldname))

for the value in place of just the fieldname.

Stan

[Glen Beer]

RE: Import txt with Thousands Separator

I've tried your suggestion of importing the numerical data as characters and then append the fields with an update expression converting them to values.

I thought this worked, but it seems that the thousands separator (comma) is still screwing me up.

here is how the expression evaluates
val(123,500)= 123

It seems that the expression can't handle the comma.

I guess I could figure out an expression to parse the comma, but this could get tricky reading from right to left and the possibility of no, one or two commas depending on the value.

Any thoughts?

[csda1]

RE: Import txt with Thousands Separator

Glen,

Just use

val(remspecial("123,500"))= 123500

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Glen Beer]

RE: Import txt with Thousands Separator

Thanks, remspecial did the trick.
I greatly appreciate your help.