1 2 Previous Next 20 Replies Latest reply on Jun 1, 2016 2:18 AM by tonimedia

    Exporting tab-text with strange/wrong Carriage Return

    tonimedia

      Hi,

      I am having trouble with exporting collected data to a .csv file. The issue already happened in FM 14 on PC and still exists on FM 14 Mac.

      I try to explain the problem in an easy example. I have two tables. The first contains different articles:

      Bildschirmfoto 2016-04-14 um 08.39.33.png

       

      In the second one I merge the article-informations with Liste(table1::article):

      Bildschirmfoto 2016-04-14 um 08.41.24.png

       

      When I export the field "List" as tab-text, ANSI, named as file.csv, I get this result:

      TextWrangler: Bildschirmfoto 2016-04-14 um 08.41.37.png

      TexteMate: Bildschirmfoto 2016-04-14 um 09.30.40.png

       

      But it should look like this:

      Bildschirmfoto 2016-04-14 um 08.41.59.png

       

       

      The exported file doesn't work when I try to import it on a webserver, because the 4 articles are not separated correctly.

      Excel (Mac) forces to crash when trying to open the file.

      OpenOffice (Mac) does not recognize the data-seperator.

      TextWrangler (Mac) shows a red questionmark (see screenshot above)

      TextEdit (Mac) separates the files correctly. But when I save this file in TextEdit, the error is not corrected - after that I have the same issues when opening in TextWrangler or others.

      The only solution that works is: opening the .csv with WordPad (Windows XP). The separator is recognized and gets replaced with a correct CarriageReturn. After saving the file from WordPad the file works perfect in any other program. This work-around was used the last five years. But I have to switch from FM 11 (WinXP) to FM 14 (Mac) and every week I have to export 25 different export-files to a Typo3 website. Installing XP on a virtual-machine is a solution - but I hope, it is not the only one !!!

       

      To solve the problem, I have tried different export-methods with different charsets. But Tab-separated Text with ANSI is the one that shows me the best result. The used solution is "a little" more complex as shown above, so using a different way (without saying "List()") will not work.

      I tried to exchange the "wrong return" with a different one. Inside of FM the symbol is char(13). I can replace it with any other char - but char(13) is the correct CarriageReturn, that is needed... So exporting char(13) results in a flipped, red questionmark...

       

      Thanks for helping me out

       

      Andreas

        • 1. Re: Exporting tab-text with strange/wrong Carriage Return
          tonimedia

          I just got the tip, to research the "wrong" CarriageReturn. I found out, that inside FM it is handled as a char(13) but becomes a char(11) after exporting...

          • 2. Re: Exporting tab-text with strange/wrong Carriage Return
            Ben

            Hi

            (Ben again)- It looks like progress - maybe some of the clever people here can help with your question. Have you sent this problem to support? They may be able to help, or comment.


            As for me - I would try to export the record set you want as records & fields rather than as a "list". Do you have an example copy of your database that I could see?


            [ PS - I have a cold so I might be a bit slow answering :-( ]



            • 3. Re: Exporting tab-text with strange/wrong Carriage Return
              wimdecorte

              That's totally normal: the return character is the record delimiter when you export so FM replaces any returns in your text with the Vertical Tab character.  Otherwise the exported file would show wrong information (you want it to indicate the equivalent of "4 records were exported" where really there are only 2 records exported.

               

              If you want a true return delimited list returned then take your data and dump it into a scratch table with one record per item in the list and export from there.

              • 4. Re: Exporting tab-text with strange/wrong Carriage Return
                beverly

                This help topic explains what happens in tab-delimited exports:

                https://www.filemaker.com/help/14/fmp/en/html/import_export.18.28.html#1029588

                 

                Ben may have the answer to export not as list(s). But this is a known 'feature' and not an issue/bug.

                 

                Andreas, if you need something different, let us know and we may have other suggestions for export.

                beverly

                • 5. Re: Exporting tab-text with strange/wrong Carriage Return
                  Markus Schneider

                  as already mentioned in the german forum: Create a helper table (if needed), create Your 'line' there as individual records - and export them.

                   

                  (-:

                  • 6. Re: Exporting tab-text with strange/wrong Carriage Return
                    tonimedia

                    ok, I see, I have to show a little more the complexitivity ;-)

                     

                    I am working for a travel-business. We print different catalogs. When the catalog is printed, the actual data go to a webserver. In the time, when we start to work on a new catalog, we still have to serve the data from last year. To do so, we change the UID-prefix from 2016 to 2017, that is attended on export to every single UID. That allows me to edit the new data, without starting to duplicate every single data record in all tables.

                     

                    Because the webserver still needs 2016 while we are working on 2017 (and in a grace-period both years are shown simultaneos online), we have to add the "old" 2016 to the "new" 2017. This is done in a seperate table, with only one record, by OldData & List (catalog::newData). The textfield "OldData" contains a complete .CSV of the last years export. The catalog-table is connected with a "x", so that I can list every record...

                     

                    I use a function, to combine my complete single record. Every field is seperated with a semikolon ;

                    Fields, containing text are enclosed with an " (char(34)) to prevent errors. And in the End I exchange every Return to a html break.

                     

                    this is how I generate one row in my export:

                    ---

                    Austauschen (

                     

                    Globale_Werte::Prefix & Kurortinfo_UID & ";" &

                    ortuid & ";" &

                    aw_Kurprogramm_edit::Kur_Prg_Nr & ";" &

                    Char(34) & Beschreibung_Ort & Char(34) & ";" &

                    Leistungen_Anz_Uebernacht & " Übernachtungen" & Wenn (Leistungen_Kurtaxe = ""; "" ; " inkl. " & Leistungen_Kurtaxe) & " /" & Leistungen_Verpflegung & ";" &

                    Char(34) & Leistungen_Zusatz & Char(34) & ";" &

                    Char(34) & Zielgruppe & Char(34) & ";" &

                    Char(34) & Trainingsziel & Char(34) & ";" &

                    Char(34) & Leistungen_Eltern & Char(34) & ";" &

                    Char(34) & Leistungen_Kinder_4 & Char(34) & ";" &

                    Char(34) & Leistungen_Kinder_5 & Char(34) & ";" &

                    Char(34) & Leistungen_Kinder_6 & Char(34) & ";" &

                    Char(34) & Leistungen_Kinder_12 & Char(34) & ";" &

                    Char(34) & Aktionen_So & Char(34) & ";" &

                    Char(34) & Aktionen_Mo & Char(34) & ";" &

                    Char(34) & Aktionen_Di & Char(34) & ";" &

                    Char(34) & Aktionen_Mi & Char(34) & ";" &

                    Char(34) & Aktionen_Do & Char(34) & ";" &

                    Char(34) & Aktionen_Fr & Char(34) & ";" &

                    Char(34) & Aktionen_Sa & Char(34) & ";" &

                    Char(34) & Zus_Info_Ort & Char(34) & ";" &

                    Char(34) & Zus_Info_Unterbringung & Char(34) & ";" &

                    gsm_id & ";" &

                     

                    Kurort__Saison_Infos_edit::Saison1_Bez & ";" &

                    S1_DZ & ";" &

                    S1_EZ & ";" &

                    Kurort__Saison_Infos_edit::Saison1_a_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_a_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison1_b_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_b_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison1_c_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_c_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison1_d_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_d_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison1_e_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_e_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison1_f_von & ";" &

                    Kurort__Saison_Infos_edit::Saison1_f_bis & ";" &

                     

                     

                    Kurort__Saison_Infos_edit::Saison2_Bez & ";" &

                    S2_DZ & ";" &

                    S2_EZ & ";" &

                    Kurort__Saison_Infos_edit::Saison2_a_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_a_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison2_b_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_b_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison2_c_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_c_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison2_d_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_d_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison2_e_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_e_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison2_f_von & ";" &

                    Kurort__Saison_Infos_edit::Saison2_f_bis & ";" &

                     

                    Kurort__Saison_Infos_edit::Saison3_Bez & ";" &

                    S3_DZ & ";" &

                    S3_EZ & ";" &

                    Kurort__Saison_Infos_edit::Saison3_a_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_a_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison3_b_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_b_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison3_c_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_c_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison3_d_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_d_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison3_e_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_e_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison3_f_von & ";" &

                    Kurort__Saison_Infos_edit::Saison3_f_bis & ";" &

                     

                    Kurort__Saison_Infos_edit::Saison4_Bez & ";" &

                    S4_DZ & ";" &

                    S4_EZ & ";" &

                    Kurort__Saison_Infos_edit::Saison4_a_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_a_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison4_b_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_b_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison4_c_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_c_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison4_d_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_d_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison4_e_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_e_bis & ";" &

                    Kurort__Saison_Infos_edit::Saison4_f_von & ";" &

                    Kurort__Saison_Infos_edit::Saison4_f_bis & ";" &

                     

                    guenstigster_Preis & ";" &

                    guenstigster_Preis_Text & ";" &

                    Char(34) & Kurort__Saison_Infos_edit::Zusatzinfo_Web & Char(34) & ";" &

                    Globale_Werte::Jahr & ";" &

                    bilderid & ";" &

                     

                    Leistungen_Gesundheit & ";" &

                    Leistungen_Handlungsfeld_Erw & ";" &

                    Leistungen_Themenschwerpunkt_Erw & ";" &

                    Leistungen_Handlungsfeld_Kinder & ";" &

                    Leistungen_Themenschwerpunkt_Kinder & ";" &

                    _Abfrage_Familienprogramm

                     

                    ; "¶" ; "<br />")

                    • 7. Re: Exporting tab-text with strange/wrong Carriage Return
                      wimdecorte

                      And you are exporting the result of this calc?  I don't really see how this calc changes anything in the solutions offered...

                      Why wouldn't exporting from a scratch table not work for you?

                      • 8. Re: Exporting tab-text with strange/wrong Carriage Return
                        tonimedia

                        wimdecorte schrieb:

                         

                        That's totally normal: the return character is the record delimiter when you export so FM replaces any returns in your text with the Vertical Tab character.  Otherwise the exported file would show wrong information (you want it to indicate the equivalent of "4 records were exported" where really there are only 2 records exported.

                         

                         

                        you are right. Thats what I also thought it could be the cause. But normaly, when I export text with a return, I set an " at the beginning and the end of the field...

                        wimdecorte schrieb:

                         

                        If you want a true return delimited list returned then take your data and dump it into a scratch table with one record per item in the list and export from there.

                         

                        ok. I don't quite understand how to do that. Let me try: I count the number of rows, add a new table with the same amount of records and put every row into a single record and then export it?

                        • 9. Re: Exporting tab-text with strange/wrong Carriage Return
                          tonimedia

                          wimdecorte schrieb:

                           

                          And you are exporting the result of this calc?  I don't really see how this calc changes anything in the solutions offered...

                          Why wouldn't exporting from a scratch table not work for you?

                          because this calculation only contains one year, but I have to add another year: OldData & List (catalog::newData)

                          • 10. Re: Exporting tab-text with strange/wrong Carriage Return
                            beverly

                            rather than Char(34) & someField & Char(34), you might use:

                            * Quote

                            Quote( someField )

                            as for your export...

                            are you using Export Field Contents or another method to export?

                            beverly

                            • 11. Re: Exporting tab-text with strange/wrong Carriage Return
                              tonimedia

                              beverly schrieb:

                               

                              as for your export...

                              are you using Export Field Contents or another method to export?

                              beverly

                               

                              not shure, what you mean - I use the german version. I go to "Ablage->Datensätze exportieren" (File->export data?).

                               

                              Andreas

                              • 12. Re: Exporting tab-text with strange/wrong Carriage Return
                                beverly

                                what method? Tab-delimited, Comma-separated, ??

                                it would appear by your original post, that you are using Tab-delimited and by my link to the help topic, you know that this changes 'return-in-field' to VT (vertical tab).

                                 

                                do you need to preserve the return-in-field?

                                beverly

                                • 13. Re: Exporting tab-text with strange/wrong Carriage Return
                                  tonimedia

                                  Hi Beverly,
                                  I've tried different methods. Comma-separated also, with the same results. :-(

                                  I need 4 rows in my final export.

                                  Andreas

                                  • 14. Re: Exporting tab-text with strange/wrong Carriage Return
                                    beverly

                                    is that the only field you are exporting or are there others?

                                    1 2 Previous Next