Change number formats on import

[Jondb]

Hi-

In number formats, my system uses a period for decimal and a comma as thousands separator. I have a number field set up the same way. 

Some of the records I need to import from .csv files use the European notation (comma for decimal, period for thousands). 
I'd like to change the European formatting to match my system format, preferably on import, but I can't find a way to do this.

An alternative would be to do this with a script after importing, but it would need to apply only to numbers that need the format change. I tried a simple script that divides the number by 100, but if I accidentally applied this across all records it would create a mess that would be impossible to fix. For this reason, a conversion on import would be the safest approach.

Any help would be greatly appreciated.

Thanks

[comment]

1. Does the source file actually contain periods as thousands separator?

2. When importing, do you know that the source file uses "European" formats?

[Jondb]

I'm not sure about the thousands separator, as all the amounts are small. It's an exported PayPal statement. Here's a small section from one of the records:

"Completed","GBP","-18,15","0,00",

As it's a Paypal account based in Spain, the European number format is used. The download is a comma delimited file, but also uses commas for decimals (presumably the quote marks prevent the commas causing confusion). The amount shown here is GBP -18.15, but the use of a comma instead of a period makes Filemaker import the amount as GBP -1,815.00 (even that doesn't make sense to me). 

Filemaker gives the option to use either number format, so it recognises the existence of both. I want it to switch the formats on import. I can make this part of a specific import script, so that I only need apply it to European format imports. This will save having to figure out a way for Filemaker to recognise the import format first.

Am I making sense?

[comment]

2 hours ago, Jondb said:

Filemaker gives the option to use either number format, so it recognises the existence of both.

No, I am afraid you are mistaken about that. Filemaker recognizes only one format for inputting data (this includes importing). This format is either the format of the current operating system, or the format of the operating system under which the file was first opened. You make this choice in File > File Options… . There is no way to switch the number/date formats used by the file only for the purpose of performing an import.

 

2 hours ago, Jondb said:

The amount shown here is GBP -18.15, but the use of a comma instead of a period makes Filemaker import the amount as GBP -1,815.00 (even that doesn't make sense to me). 

It actually imports it as is ("-18,15") but when converting this to a number (in a calculation, or if the field is formatted to display as decimal) it ignores any characters other than digits, the minus sign and the decimal dot (which is not present in this example).

 

2 hours ago, Jondb said:

I'm not sure about the thousands separator,

Well, if you're not sure, then you cannot divide by 100, because that would turn "1.234,56" into .0123456. Instead, replace the field contents with a calculated result =
 

Substitute ( YourTable::Amount ; [ "." ; "" ] ; [ "," ; "." ] )

If your import is scripted, you can make this a part of your script. Note that immediately after import, the found set consists of the imported records only - so you do not need to worry about modifying other records.

[Jondb]

that makes perfect sense, thanks. I'll try it out.