Jump to content

Strange Import Problem


presley
 Share

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

Recommended Posts

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!

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

This topic is 5644 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.