11 Replies Latest reply on Sep 4, 2013 4:15 PM by JoeSand

    Making a semicolon delineated CSV file

    JoeSand

      Title

      Making a semicolon delineated CSV file

      Post

           Hi all!  A search of the forum tells me that Filemaker (11) still has no provision for creating a semicolon delineated CSV file.  Is this true?  Assuming it is, I'm interested in what created (read "easy") ways people have gotten around this.

           In the "old days" I remember a plug-in that allowed multiple writes to the same text file, so I would have built it up one line at a time.  Alternatively, I could create, say, a global field, write one line at a time to it, then export the field to a file.

           I need to export about 30 fields, most with only a few characters, some with a paragraph or two, for about 8000 records... basically it's all the product data being exported to PrestaShop, so I'm not sure if this would tax the capacity of a single field.

           Any thoughts are appreciated

        • 1. Re: Making a semicolon delineated CSV file
          philmodjunk

               You can put gigabytes of data into a single text field so you shouldn't have a problem putting all text in one field. No version of FileMaker yet offers any option but commas for a Comma Separated Value export.

               Another option might be to export the CSV file with commas and then use a system script to replace the commas with semi-colons.

          • 2. Re: Making a semicolon delineated CSV file
            davidanders

                 Word, and many other text applications have a Find | Replace All functions - assuming there are no commas in your data.

            • 3. Re: Making a semicolon delineated CSV file
              JoeSand

                   Can you export to a field first... or modify the text file after it's been exported?  I don't know how one would do this with native fuctionality...

                    

              • 4. Re: Making a semicolon delineated CSV file
                raybaudi

                     "I need to export about 30 fields, most with only a few characters, some with a paragraph or two, for about 8000 records..."

                     What about exporting ( as tab separated text files ) a calculated text field for each of the 8000 records, with calc:

                     Field1 & ";" & Field2 & ";" & Field3 ... & ";" & Field30

                • 5. Re: Making a semicolon delineated CSV file
                  JoeSand

                       That's an interesting idea... export one field and there are no delineators used, I assume, so it doesn't matter what format you use!

                       I'll give this a try...  It would probably put quotes around each line though... as long as the first and last fields are text fields, I could work around that though.

                       I'm a stickler for automating the process... I don't want to use Word, Excel, Notepad++ or any other external program if I can avoid it.  I've looked into using Web Services with Beezwax's free plugin to avoid the whole "export-import" thing, but that would probably be a whole new tangled web...

                        

                  • 6. Re: Making a semicolon delineated CSV file
                    philmodjunk

                         Well you can use an external program and still have the process fully automated, but I agree that this makes for a more complex and less portable solution.

                    • 7. Re: Making a semicolon delineated CSV file
                      JoeSand

                           OK, here's something I can't explain:  I constructed a calc field that creates one line of semicolon deliminated fields for each record, and tried exporting them.  I created this string with quotes around each field, i.e.  "field1";"field2"; etc.

                           When I then do a CSV export of this single field it doubles up all the quotes... ""field1"";""field2""; etc.  If I modify the string to remove all the quotes, then the export stays the same... it doesn't add quotes.  If, however, I export it as tab delineated, then it's fine. 

                           So it appears that, using tab deliniated export, this method will work, but I don't understand why CSV export doesn't.  Thoughts?

                      • 8. Re: Making a semicolon delineated CSV file
                        philmodjunk

                             CSV automatically quotes the data to be exported as that is an expected part of the file format. If you include quotes like this, FIlemaker assumes that the quote characters are part of the data to be exported and "quotes the quotes".

                        • 9. Re: Making a semicolon delineated CSV file
                          JoeSand

                               I expected quotes around the single field I was exporting, but don't expect it to parse the string of characters in this field, and guess what I'm doing.  I I constructed this string of semicolon delineated data omiting the first and last quotes, expecting these to be added on export.

                               So, if this is expected behaviour, why doesn't the tab export do the same thing?

                          • 10. Re: Making a semicolon delineated CSV file
                            philmodjunk

                                 It's a different file format with different specifications to meet in order for the file to be successfully imported into many different applications.

                            • 11. Re: Making a semicolon delineated CSV file
                              JoeSand

                                   OK... Well in any case, I've solved my problem, and clicked the "best answer" closest to what I ended up doing.  Thanks for the  help!