12 Replies Latest reply on Jun 11, 2009 11:36 AM by VenturaDon

    Keeping converted mmddyyyy format in .csv export

    VenturaDon

      Title

      Keeping converted mmddyyyy format in .csv export

      Post

      I converted an imported yyyymmdd date field using the middle/right/left functions (kudos to Super Contributor "Comment" for that advice!), but when exporting records with that field as a .csv text file (with "Apply current layout's data formatting to exported data" selected) into an old DOS program, the data still shows up in yyyymmdd format. I've tried all combinatios I can think of, but no luck...

       

      Suggestions? Thanks!

        • 1. Re: Keeping converted mmddyyyy format in .csv export
          comment_1
             It looks like you are exporting the imported original field, not the converted version (which I guess resides in a calculation field?).
          • 2. Re: Keeping converted mmddyyyy format in .csv export
            VenturaDon
              

            That's what's driving me crazy...

             

            I've linked to the calc field in the table that has the correctly formatted date (field shows dates as mmddyyyy), plus the correct date format shows in the file I'm exporting from. I "assumed" that by selecting the "Apply current layout's data formatting to exported data" in the export choices would solve my issues, but...

             

            Ideas??

            • 3. Re: Keeping converted mmddyyyy format in .csv export
              comment_1
                 Are you sure you are exporting the calculation field?
              • 4. Re: Keeping converted mmddyyyy format in .csv export
                VenturaDon
                  

                I'm exporting from the file with the field that has the correct date format showing (which is linked to a calc field from another table that also shows the correct date format).

                 

                If the correct date format is showing in my working opened file, then by definition isn't that date format also supposed to be exported...??

                • 5. Re: Keeping converted mmddyyyy format in .csv export
                  comment_1
                    

                  I am sorry, but I don't quite follow what you're saying. You had (IIRC) a text field with imported YYYYMMDD data, and a calculation field returning a date result. I am not sure what you mean by "field ... which is linked to a calc field". Is it another calculation field? If so, what is the result type?

                   

                   

                  I don't think date format is the issue here, but just to make sure: what is the short date format of your computer OS? Was the file you are using created on your computer, and if so, what was the computer's short date format at that time?

                  • 6. Re: Keeping converted mmddyyyy format in .csv export
                    VenturaDon
                      

                    The field (f18) with the yyyymmdd data is currently text (I've experimented changing it to date, number, etc).

                     

                    But I created a new field "Date" which is a calc field with the following definition:

                    Date ( Middle ( f18 ; 5 ; 2 ) ; Right ( f18 ; 2 ) ; Left ( f18 ; 4 ) )   -  the calc result is set to Date.

                    This field correctly shows the mmddyyyy format.

                     

                    This Date field is the field I've linked to in my working file.


                    But this export issue also affects the formatting of other fields (not just this date field).  Field f17 is a field with 270,000 document numbers 9 digits long (123456789). I need to convert them to 12-3456789. So I created another calc field called DocNumber and used this function:  Replace ( f17 ; 3 ; 0 ; "-" )  - it works perfectly and this DocNumber field is what I've linked to in my working file. But when I export, the result is back to 123456789.

                     

                    So if I can figure out my date export issue, I suspect I can apply that solution to the other fields as well.

                     

                    Thanks for your patience and help!!

                    • 7. Re: Keeping converted mmddyyyy format in .csv export
                      comment_1
                        

                      VenturaDon wrote:

                      This Date field is the field I've linked to in my working file.


                      Again, I don't know what that means.


                      • 8. Re: Keeping converted mmddyyyy format in .csv export
                        VenturaDon
                          

                        Maybe I've approached the date conversion process incorrectly.

                         

                        I created a new field called "Date" - this field is a calculation field that reformats the data from f18 into the mmddyyyy format.

                         

                        In my working file, a date field is specified as the "Date" field from the other table.

                         

                        Did this answer your question?

                         

                         

                        • 9. Re: Keeping converted mmddyyyy format in .csv export
                          comment_1
                            

                          I'm afraid not. I am with you up to the point where you have a field f18 containing the imported data, which is (and should be) Text, and a calculation field named Date which returns a date result (as it should). I trust these two fields are in the same table.

                           

                          From this point on, I have no idea what you did. You speak of "other table", "working file" and "linking to fields" - I haven't got a clue what's all that about.

                          • 10. Re: Keeping converted mmddyyyy format in .csv export
                            philmodjunk
                              

                            A suggestion to clear up the confusion:

                             

                            Describe step by step how you exported your data into the .csv file. Specifically identify by name which field (f18 or date) that you selected for export.

                             

                            Maybe that'll help.

                            • 11. Re: Keeping converted mmddyyyy format in .csv export
                              VenturaDon
                                

                              Hi again -

                               

                              I'm working with two FP files:

                               

                              1) the f18 field (yyyymmdd format) is in Assessor Data.fp7, but I created a calculated field called Date (mmddyyyy).

                               

                              2) my "working file" is FLS Data Records.fp7

                               

                              I'm using fields from the Assessor Data file in the FLS Data Records file via relationships between the 2 files. I need to export those newly created records to an old DOS program, but after exporting, the date field in the DOS program shows up in the yyyymmdd format, even though the field in my FLS working file is the calculated Date field from the Assessor Data file (which correctly shows the mmddyyyy format).

                               

                              Does that clarify my situation? Thanks much and hang in there with me as I muddle through this!

                              • 12. Re: Keeping converted mmddyyyy format in .csv export
                                VenturaDon
                                  

                                I'm a newbie fool - beat me over head 50 times with a wet noodle...

                                 

                                Looking hard at my export process, I realized I hadn't selected the Assessor Data table & the Date field (in the small window over the primary field choices) where all the fields are selected; I merely accepted all 50+ fields in the main field box of choices. I deeply apologize for wasting all your time chasing this down; however, your request to take a hard look at the export process is what saved me.

                                 

                                Thank you again!