Importing An Excel CSV File
GCC Consulting
gcc at optonline.net
Tue Nov 16 08:02:09 PST 2004
________________________________
From: filepro-list-bounces at lists.celestial.com
[mailto:filepro-list-bounces at lists.celestial.com] On Behalf Of Art Moore
Sent: Monday, November 15, 2004 10:15 PM
To: filePro List
Subject: Importing An Excel CSV File
I am using Windows XP Professional with filePro 5.0.5 and EXCEL 2003.
I have received an EXCEL file that I need to import to a filePro file.
All of the fields in the filePro file have been defined as '*'.
When I save the EXCEL file as a .csv file so I can import it problems
developed with what the EXCEL file lists as phone and FAX numbers. In the .csv
file many of what should be phone numbers turn to exponential form. This only
happens with phone numbers that are entered as just a string of numbers like
1234567890. When the entry is listed as 123-456-7890 there is no problem.
When the entry of 1234567890 is hi-lited an exclamation mark appears and
says 'the number in this cell is formatted as text or has an apostrophe at the
beginning'. The entry does have an apostrophe at the beginning.
Even if I change the format to a number left justified (or text) and
save it, when I re-open the file it is back to the exponential form.
What am I doing or not doing to make these entries correct and stay that
way when saved an opened as .csv file. Formatting as special/phone number does
not do anything either.
When I open the file (saved a .csv) it does say EXCEL recognizes the
file as a sylk file but cannot open it that way and so will open in another
format if I want it to. I say okay.
Thanks in advance.
Art Moore
Art,
My suggestion would be to save the file as a dbf file and use dbcopy to create a
filePro file. You can the create a posting routine to post to the file you want
to use. You can manipulate the data at that point.
You can take the string value and move it into a numeric field or do what ever
you have to do.
Be careful of dates. I had this problem recently as the are converted to a
Julian Date (see previous thread). Ken advised if you added the numeric value to
12/21/1899 the date would be the current date. It appears that MS is using 1900
as the starting date for their "Julian Value".
If you have zip codes, you may loose the leading "0" and have to put it back in
with processing.
Have found that it may take one more step using this method, but I can view the
data prior to posting and insure that it comes in correctly. As long as the
file structure is similar each time, you should only have to write the
processing once.
Richard Kreiss
GCC Consulting
More information about the Filepro-list
mailing list