problems importing phone numbers. How to add initial zero?

[parasko]

Hi all,

I'm importing phone numbers from Excel but the Excel data does not show the initial zero on numbers.

The data is to be imported in two fields. The first field is for cell/mobile phones and a zero should appear in all records.

The second field is for landline numbers. Some of these numbers have a zero as part of an area code prefix, while other records have the cell/mobile number entered in this field. However, most records do not need an initial zero.

I don't want to make the changes manually as it is over 4000 records being imported.

Should this be resolved in Filemaker or Excel? How do I resolve this in either program, to add a zero to the relevant records?

[LaRetta]

Should this be resolved in Filemaker or Excel?

It indicates that Excel has the columns set as number instead of text. Although we call them phone numbers, they never should be number data-types in any program. In Excel, if you click into the field, does it show the leading zero there?

Can you provide a sample of your data (make up numbers) from both fields as an Excel file (with same format as Excel)? It is possible that you can fix it from the FileMaker side upon import but we would need to have a consistent, dependable pattern from which to work. Is this an ongoing need or a one-time thing? How does the data get into Excel?

I know, I know ... lots of questions. :king:

Edited March 14, 2009 by Guest

[parasko]

Hi LaRetta,

I basically need a formula which states:

Any number commencing with 2 or 4, add initial zero. This should cover all instances.

To answer your questions:

1. In Excel, the zero does not appear when you click in the cell. The data has been imported as a number. However, when I select the two columns in Excel and change to text, no change occurs.

2. This is an import I will need to do approx. 6 times per year. The data is imported into Excel from a central university database, which I'm eventually importing into Filemaker (a departmental database). The University database only permits docs to be saved in Excel.

[LaRetta]

Hi,

There are a few considerations before choosing the best approach.

1. Once these numbers are imported, can Users change them?

2. Will you want to use 'Update and Add To' in case some records already exist in the system?

I will give you the best method (in my opinion) but we'll have to modify it depending upon your answers to 1 and 2 above:

In field definitions, LandLine should be standard field set to text result. Then go to Options button on LandLine and select Auto-Enter tab. Click the Specify button next to 'Calculated Value' and enter this calculation:

Case (

Left ( LandLine ; 1 ) = 2

or

Left ( LandLine ; 1 ) = 4 ;

"0" & LandLine ; LandLine

)

... where LandLine is your field name (which it is best to double-click and insert from the field list above the calc box). Click OK to leave the calc box and, back at the Auto-Enter tab, UNCHECK the box below 'Calculated Value' which says, 'Do not replace existing value ...". This will convert the records in the LandLine field as they are imported. Repeat with Cel field.

Consideration 1) If there is possibility that item 1 might apply (users can change the data) and there is EVER an exception or rule change whereas 2 or 4 might NOT warrant a leading zero (and a User tries to remove the leading zero), system will keep adding it back. Odds are you are fine but I want to be sure you are aware of this ... when you uncheck 'Do not replace existing value...' it means it will reapply this calculation whenever that field value changes. Also, it is usually wise to import into a different field so you can keep the original field as the actual text coming in (for history/audit purpose). But since you can keep the Excel spreadsheets, I see no reason to create second fields for the numbers).

So import script would use 'Add New Records' and be sure to CHECK the box when it asks if you want to 'Perform Auto-Enter' because if you don't, your calculation will NOT fire and correct the data.

Consideration 2) If there is possibility of duplicates or numbers coming in which have been changed for a person, you will want to precede your import script-step with Show All Records and you will want to use 'UpDate and Add to' option instead; specifying which imported field means the record will be unique, ie, place an = on the field which designates the person is unique. And again, check 'Perform Auto-Enter'.

Since you are sure about the rules, you can take the Excel as it comes (without changing the Excel spreadsheet first). Also remember that, when scripting the import, to specify whether the first record from Excel is data or contains the field name. Please ask if you have any questions at all - there are many wonderful people here on FM Forums who are willing to assist!!

LaRetta :wink2:

[parasko]

Thanks LaRetta.

Works like a charm!

You've been a great help. Very much appreciated! :thankyou: