Jump to content

retaining leading zeros in exported file


Sean O'Donoghue
 Share

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

Recommended Posts

quote:

Originally posted by Sean O'Donoghue:

When I export a number field to a comma-delimited text file, the exported file looses its leading zeros

Ie 00312 exports as 312.

I need to retain all five digits - how can I do this

Thanks in anticipation.

Sean O'Donoghue

Try defining an unstored calculated text field, e.g:

Number_Export=Right("00000" & NumToText(Number_Field), 5)

and export Number_Export instead

Link to comment
Share on other sites

We'll need a little more description of what you are doing. If you have 1) defined the new calculation field and 2) exported it INSTEAD of the existing number field, it should work. This new calculation field has no effect on which records are exported, that is a function of the found set at the time the export is performed.

I'm not quite sure what you mean by "it works fine on records created but leaves existing numbers unexported". Perhaps you can give some further details.

-bd

Link to comment
Share on other sites

Greetings

Sorry I was not too clear.

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export so I got

the following - which is but a sample.

""

""

""

""

""

""

""

""

""

""

"00001"

"00002"

"00003"

"00004"

"00005"

Regards

Sean O'D

Link to comment
Share on other sites

quote:

Originally posted by Sean O'Donoghue:

Greetings

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export...

I''m not sure exactly what you're trying to accomplish here, but can't you export those records (starting at 28000) again? Just export the calculated text field instead of the number.

Hope this helps!

Link to comment
Share on other sites

What exactly do you mean by "did not export". Were the 28000 series records in the found set when you tried to export? How did you perform the export? Manually? Under script control?

You'll need to answer some of the questions we've asked with some elaboration for us to be of further help. It's hard to watch your screen from here! wink.gif" border="0 .

-bd

Link to comment
Share on other sites

Greetings

Sorry I was not too clear.

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export so I got

the following - which is but a sample.

""

""

""

""

""

""

""

""

""

""

"00001"

"00002"

"00003"

"00004"

"00005"

Regards

Sean O'D

Link to comment
Share on other sites

Sorry - I had created the Number_EXport field as a Number Field - have now changed it to a text field.

"Number_Export" field is the calculated text field below, with "book_id" being the field whose properties I wish to export, retaining leading zero

book_id=Right("00000" & NumToText(book_id), 5)

I am using FMP 5.5 - you tell me to define an "unstored calculated text field" - the program Help & the book I am using (FMP Companion 5) both suggest that I should have a tick box by a "Do not store calculation results - calculate only when needed" command - I do not appear to have this, certainly on this tab - on the Storage tab for the Options for Field "Number_Export" box, I only have

a) Repeating field with a maximum...

??? Indexing On & Off tick boxes &

c) Automatically turn indexing on if needed

d) default language for indexing etc

And this is the result of my export of the Number_Export field - with lots of expty

""

""

""

""

""

""

""

""

""

"0"

Yours mystified

Sean O'D

Link to comment
Share on other sites

quote:

Originally posted by Sean O'Donoghue:

"Number_Export" field is the calculated text field below, with "book_id" being the field whose properties I wish to export, retaining leading zero

book_id=Right("00000" & NumToText(book_id), 5)

These statements seem to contradict one another. If I'm understanding you correctly, field book_id is the original field with values from 00001 to 99999 (or whatever your upper limit happens to be). However, when you try to export this you lose the leading zeros.

My suggestion was to create a new field, Number_Export. This should be a calculation field with a text result.

Number_Export=Right("00000" & NumToText(book_id), 5)

I believe you weren't able to select "Do not store calculation results" because Number_Export was not defined as a calculation field. I apologize for not being more clear before -- that is what I meant by unstored calculated text field. A calculated field with an unstored text result.

Number_Export is the field you want to export, not book_id.

I hope this is more clear.

Link to comment
Share on other sites

Greetings Again

Yes, Field_export is the field I am exporting - I have now discovered where the "Do not store calculation results" tick box is & have ticked it.

Now I am getting mostly "1" & "O" in my exported txt file, with a couple of total blanks -

Shouldn't the calculated field, Number_Export, contain the calculated result - as no doubt you will have deduced by now, mine only contains 1s & Os

Also, you say "unstored calculated TEXT field" - I am unable to create a text field when I opt for the calculation field -

>>Number_Export is the field you want to export, not book_id.

<<

Regards

Sean O'D

Link to comment
Share on other sites

quote:

Originally posted by Sean O'Donoghue:

Also, you say "unstored calculated TEXT field" - I am unable to create a text field when I opt for the calculation field -


There is a drop-down menu immediately below the area in which you define the calculation. The menu is labeled "Calculation results in". From that menu select Text.

I suspect that the calculation is defined to result in a number, which may explain the 1's, 0's and blank values. Otherise, it is also possible that book_id is defined as a Text field. Double-check this in Define Fields. If book_id is indeed a text field, then I suggest removing the NumToText() part of our calculation.

Hope this helps -- I'll be keeping my fingers crossed for you several thousand km away... smile.gif" border="0

[ November 26, 2001: Message edited by: The Bridge ]

Link to comment
Share on other sites

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