Jump to content

Change number formats on import


Jondb
 Share

This topic is 2274 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2274 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.