7 Replies Latest reply on Jun 10, 2013 9:05 AM by philmodjunk

    No LF / CR on Exported CSV files??

    ChrisDeith

      Title

      No LF / CR on Exported CSV files??

      Post

           Interesting one this one!   

           I have an application that outputs data as a CSV file for the Royal Mail DMO system.   For some reason, the DMO system would not read the file properly; it read the first line, but nothing else - and yet if opened in Excel, the data looked perfect.

           After much looking and head scratching, we discovered that the CSV file that was being output from Filemaker (as Windows ANSI) didn't have proper carriage returns on the end of each line.  Applications like Excel & Word seem to be "intelligent" enough to pick the data up properly, but in it's raw form the DMO system just won't read the file.

           Yet if the file is simply opened in Word & re-saved... with nothing at all changed... then the carriage returns are correctly saved at the end of each line of data and it works.

           As a work-around that will do for now, but ongoing I need to find a way to properly output the csv file from the application.

           Anybody come across this before, or got any ideas?

        • 1. Re: No LF / CR on Exported CSV files??
          philmodjunk

               Are these missing Carriage returns within data of a single field? FileMaker has to replace those characters with a different character (Vertical tab?) or the data will not be in correct format for CSV output.

               When you open this data in Excel or Word, those applications correctly interpret the characters as marking the spot for a return and display the data correctly. Word probably reverses the character substitution automatically.

          • 2. Re: No LF / CR on Exported CSV files??
            ChrisDeith

                 They are missing at the end of each line of data, so a file for instance with 7 lines of data is "read" by DMO as having only a single line but with too many fields.

                 I've also now discovered that it also only occurs on the Mac version of filemaker; the same application (which runs remotely via filemaker server) produces the correct file format when it's run via a windows version of filemaker. (both copies are Filemaker 12 Pro Advanced, one on Mac and one on Windows)

                  

                 So it would appear it might be a Mac vs PC issue?

            • 3. Re: No LF / CR on Exported CSV files??
              philmodjunk

                   I suggest posting this to Report and Issue to see what the tech support people have to say. I am not aware of any PC vs. WIndows differences that match what you describe here and I would expect such to generate bug reports in Report an Issue long before now.

              • 4. Re: No LF / CR on Exported CSV files??
                ChrisDeith

                     Thanks Phil - having found the work-around using Word on the Mac - and given that most of the time dispatches are done on the Windows machine anyway - we can probably live with it.

                     Could I be cheeky however and ask your advice on another issue, which I'm sure you will have a very quick-and-simple answer to??!!blush

                      

                     I have around 16,000 invoices in the database, with around 120 different descriptions of the shipping type (they are all downloaded from a shoping cart).  In order to classify the shipping, I want to assign each description to a set of around 4-5 codes (eg Basic, Recorded, Express etc).  I have therefore setup a "group" file with the descriptions, and a relationship back to the descriptions in the invoice file.

                     If I setup a drop-down menu on a layout, and use the descriptions field from the invoices file, it automatically gives me a list of each uniquely different description.  I can then assign the appropriate shipping group code.  BUT....

                     What I want to do is display not a drop down, but a table of shipping descriptions that only shows each different value?  I can then work through them and assign the appropriate group and quickly see if there are any blanks.

                     How do I create a layout that doesn't list every single line - all 16000 of them - but only lists them once for each different value?

                     A simple solution I suspect....?   blush

                      

                     thanks

                • 5. Re: No LF / CR on Exported CSV files??
                  philmodjunk

                       Use a list view layout. Remove the body layout part from this layout. Replace it with a sub summary layout part "when sorted by" your description field. Show all records and then sort them by this description field and you will get one row for each unique description. A button in this sub summary layout part can then perform a script that assigns that description to a particular shipping group code if you select that code in a global field in the header first, then click buttons to select them. The same script can omit that set of records from the found set so that you don't select the same description for more than group.

                  • 6. Re: No LF / CR on Exported CSV files??
                    ChrisDeith

                         Hi Phil

                         I tried this but am clearly doing something wrong; I've not yet got to the point of adding the global field and buttons as at present I'm just trying to get the list layout to show each unique description.  I've appended some screenshots and hopefully you can spot what I'm doing wrong?

                          

                         thanks...

                    • 7. Re: No LF / CR on Exported CSV files??
                      philmodjunk

                           Your records are not sorted.

                           To repeat: Show all records and then sort them by this description field and you will get one row for each unique description.