Jump to content

Export Date to Maintain Leading Zeros?


tbfilemaker
 Share

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

Recommended Posts

I am exporting a date field and need to keep the # of characters in the date field.

Scenario: I am parsing a date to three separate fields using the Year(Date) Month(Date) Day(Date) functions(Calc result is text).

Problem: I need to keep the leading zeros in the month and date fields for the export.

Current Result:

Date Field = 2006/01/01

returns

2006 1 1

I need: 2006 01 01, and when exporting into Excel I need to maintain the same # of characters (i.e. 2006 01 01).

Any feedback would be greatly appreciated. I have been hitting this from every angle and can't find the solution.

Thanks in advance...

Link to comment
Share on other sites

"comment", I am trying to automate this export process to provide an output file for another system periodically, ideally I don't want to interact with Excel, currently I am only using Excel as a means of validating my data.

Thanks for the quick responses. "comment", I used your calculation and read your link which helped explain the issue in more detail. It is clearly an issue with how Excel interprets the text file.

Lessons Learned:

When opening my FileMaker exported file as a text file it maintained the leading zeros. However, when opening with Excel it trims the zeros. By changing the import delimiter options in Excel when importing I was able to maintain the zeros.

Thanks again for the help.

Edited by Guest
Link to comment
Share on other sites

Problem: I need to keep the leading zeros in the month and date fields for the export.

Why do you need to do this for Excel? Dates (or any number cell for that matter) in Excel can be formatted to show leading zeros or not, but it's not necessary to actually enter zeros. And if you export date records from FM and then import into Excel, you should get dates without doing anything extra. So I don't guess I understand why you are breaking the date into three pieces and what is happening to them when you get the over into Excel. Can you provide more information?

James

www.james-mc.com

Link to comment
Share on other sites

I am using Excel just to view the data.

I have to break the date into into a total of 8 characters (i.e. 1/1/2006 = 20060101) in order for my ASCII file to work with the COBAL system I am importing into.

I have to concatenate several fields into one text string equaling 2050 characters, so having all the spaces and zeros are critical.

Everthing seems to be working good in regards to this issue.

Thanks for all the feedback.

Link to comment
Share on other sites

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