The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Import Wizard dropping the leading zero
Posted: 02 Oct 07 2:32 AM
|
I understand this is a known issue but can not resolve it myself ... need help!
I have Accounts/Contacts that I wish to import using the ImportWiz. The phone numbers have area codes that start with a '0' and this is being dropped after import.
Any help would be appreciated.
 |
|
|
|
Re: Import Wizard dropping the leading zero
Posted: 02 Oct 07 7:59 PM
|
I ran ImportWiz just yesterday and it did not drop leading zeroes, so I am wondering if maybe it is a problem with your source data - ie, in your source data, does the field holding phone number have a numeric datatype instead of a text datatype? That would probably do it. What is your source data held in (Access, CSV etc)?
Cheers
PP |
|
|
|
Re: Import Wizard dropping the leading zero
Posted: 03 Oct 07 11:38 PM
|
Hi Phil
My source data came to me as an Excel spreadsheet which I then save as a .csv (MS-DOS) file for import.
I have tested it by making the format of the phone number field in the Excel sheet each of numeric, general and text before saving it to csv.
Makes no difference - the leading zero gets dropped each time!
When I open any version of the .csv file in Notepad the leading zero is visible but they are not when i open the csv file.
So, it must be something to do with the csv format?
|
|
|
|
Re: Import Wizard dropping the leading zero
Posted: 04 Oct 07 12:32 AM
|
If you open the CSV file using Notepad, are the phone numbers enclosed in quotes, eg "0123 456 789"? If they are, the import wizard should treat them as text and the leading zero should not be dropped.
Otherwise I would suspect the 'Save As' process from Excel as just saving the data in numeric format. Note that changing the format of a field in Excel does not change its datatype, merely the way it is displayed.
One way of changing datatype is through using a formula: eg, if A1 contains 001 (numeric, but with leading-zero display format), enter the following formula in B1: = TEXT(A1, "000").
This performs a datatype conversion and retains your leading zeros.
After this, you can always just copy the new column and do PASTE SPECIAL / FORMULAS AS VALUES to remove the formulae from your spreadsheet.
Another plan (better in my opinion) is to use Access as your source - you have native control over your columns' datatypes and can perform the import directly (without needing to Save As to CSV).
I was using the latest Import Wizard (as comes with SLX 7.2) - maybe you are using an earlier version? If you are, perhaps there is an issue, as you suggest.
Cheers
Phil |
|
|
|
Re: Import Wizard dropping the leading zero
Posted: 04 Oct 07 2:00 AM
|
I use a similar technique to ensure that seemingly numeric fields are not misinterpreted, I concatenate a leading Text character (e.g. "p" + number), then during the import process I strip it.
I haven't used the Import wizard in a while, but I recall it also does a sample of the file in order to determine the schema. Even with the leading zeros in the file, it may still interpret it as Numeric, so you may want to go into the Schema and take a look at how it was defined. Then again, if you concatenate a leading character as I stated above then the "Guessing" will identify the field as a char field... |
|
|
| |
|
Re: Import Wizard dropping the leading zero
Posted: 04 Oct 07 7:58 PM
|
Thanks for the input everyone ... I came up with a solution (with some help for a colleague) that works ....
1/ Recreate the .xls worksheet so that there are double quotation marks around each value (apart from the headings). I used the formula =CHAR(34)& 'Source Sheet'!A2 & CHAR(34) to do this.
2/ Save the new Worksheet as csv.
3/ Runthe Importer and it retained the leading zeros
The strange thing was thta the csv file initially had multiple quotes so I had to Edit/Replace all to remove them prior to running the import.
Thanks again .... Tom |
|
|
|