Strange Import Problem

[presley]

Hardware/Software

Mac G5, OSX 10.4.6, FM7

Description:

I have one table (APP) to hire/assign employees to a job via a pull-down value list. In another related table (JOBS) I have budget codes assigned to the specific jobs.

(19 digit numeric. Example: 0172620330361409200). When I assign an applicant to a job, the 19 digit budget code is auto-entered into the budget code field in the APP table.

When setting up this relationship I noticed that the APP auto entered the budget code into the field (as expected) but formatted like: 1.762e+17 …if I clicked into the field it would change to the expected 19 digit code. I solved this problem by changing the formatting of the field, so I no longer see 1.7262e+17 any longer….just the expected 19 digit code

The Problem:

I am required to submit this data (Export) in .xls format. When I export the data from FM

(.xls, merge, SLK, FM, .tab ….you name it I have done it) and then drop the exported file onto EXCEL, the field with the budget code either reads: 1.7262e+17 OR (strangely) it enters the last 18 characters of the budget code (ex. 172620330361409200) and will not display the beginning (19th) character which is always a zero…..

Stranger still …..I cannot seem to change the formatting of this import in EXCEL …it will not allow me to “force enter” the beginning zero in the budget code!

I have tried every conceivable trick I know to solve this problem

- copying/pasting budget codes into different fields and then importing

- trying numerous field formattings

- changing field types and definitions

- writing copy/paste scripts and then importing

The Desired Solution:

A nice simple/clean export from FM in any format ---drop onto EXCEL and see all 19 digits beautifully displayed…

Anybody run into this problem before!

[comment]

The budget code field needs to be a TEXT field.

[presley]

uhhhh....WRONG....

the fields are already text....it has something to do with the way EXCEL 1) formats fields on import 2) and auto removes the first character..if it is a zero....

[comment]

I was under the impression that there was something wrong with your export from Filemaker (after all, this is a Filemaker forum...). If your export file contains the leading zeros, then the problem is with Excel recognizing the data as text.

I am no expert on Excel (after all, this is a Filemaker forum...), but I think you need to tell Excel to treat the imported column as text, during the import dialog.

---

Perhaps if you exported a calculation field instead, placing a space in front of the numeric code, it would force Excel to recognize the column as text type?

Edited April 10, 2006 by Guest

[Vaughan]

Yes, Comment has the problem identified.

When opening a file, Excel offers no way to identify what data type a column is, it makes its own assumptions. Very often it permanently changes the file, too, without warning.

(I had a case where a client was complaining that a csv file wasn't correct because a column was screwed up -- in some rows the data was converted into dates. I would export the file, send it to them; they would open the file in Excel and see the column data was mangled. I finally convinced them to open the file in Notepad instead of Excel and they could see the data was OK.)