4 Replies Latest reply on Nov 22, 2012 4:07 PM by gobbles

    importing from CSV file errors

    gobbles

      I am creating a CSV file with google app scripts and then importing this into filemaker pro 11.

       

      the format is something like:

       

      timestamp,firstname,lastname,dob,comment,...etc

       

      for an example like:

       

      Tue Oct 30 2012 15:35:39 GMT+1100 (EST),john,doe,31/1/1930/,I really like choc chip cookies, etc

       

      It works perfectly.

       

      Because there are several comment fields that allow multiple lines on the user form however, some times google scripts spits out a CSV of the form:

       

      Tue Oct 30 2012 15:35:39 GMT+1100 (EST),john,doe,31/1/1930/,I really

      like

      choc

      chip

      cookies, etc

       

      When the later is imported into filmaker pro 11 I am seeing a new record being produced for each line. In cases where there is a blank line an entire blank record is being produced.

       

      Is there a way for me to fix this internally in filemaker pro or do I have to solve the problem at the google script end?

       

      thanks for any help!

        • 1. Re: importing from CSV file errors
          psijmons

          You can substitute the break characters with a space before you extract your information, for example:

          Substitute ( $YourGoogleString ; "¶" ; " " )

          • 2. Re: importing from CSV file errors
            steve.winter

            Howdy

             

            You will need to do some data cleansing before importing. A csv file (in any application, not just FileMaker) treats every line as a new record, and every comma as a new column, so you'll need to get all of the 'comments' into a single line, or if the line breaks are important to the data, then you can also quote the text string and it will be imported correctly (keep reading)

             

            Note also that you'll need to be careful about commas in the content users enter. For example, if the user had entered

                 I like chic chip cookies, mars bars, and snickers

            then those commas would be seen as indicators to split 'mars bars' and 'and snickers' into new columns/fields in your DB.

             

            The usual approach to preventing intentional  commas from being treated as new fields is to wrap all text strings in "", then separate those with commas. Of course you have to take care with those too when dealing with user input, because a user might type a " in their text string, in which case you need to 'escape' that with "" i.e. replace a single " with two ""

             

            All of this will need to be done before the data is sent to FM. You mention that you're creating the csv with google apps scripts, so I'm sure that would be possible there.

             

            The upshot is that if you want to import line breaks, and allow for commas and quotes in user strings you'll need to end up with something like this;

             

            fred,spoon,"This is a new multiline comment, which has commas, and a quote.

             

            The man said ""I really like choc chip cookies, mars bars, and snickers."""

            mary,spoon,a short comment

             

            When saved as a csv file this text (which is in the attached file) would generate two records in FM, with line breaks, commas and quotes in your comment field W5 (which was what was wanted

             

            Cheers

            Steve

             

             

            Steve Winter

            Matatiro Solutions Limited

            steve@matatirosolutions.co.uk

            p:  +44 23 8064 4181

            m: +44 77 7852 4776

            USA: +1 415 315 9912

            2a St. Mary's Road

            Bishopstoke SO50 6BP

             

            Registered in England and Wales: 6300320

            Registered Office: 44 Southchurch Road, Southend, SS1 2LZ

            VAT Registration Number: 916 8809 86

            IMPORTANT: This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.

            • 3. Re: importing from CSV file errors
              comment

              gobbles wrote:

               

              Is there a way for me to fix this internally in filemaker pro or do I have to solve the problem at the google script end?

               

              You have to solve this at the sending end. The solution is very simple, though: just quote the cells that  contain returns. In fact, you can do what Filemaker does when it exports CSV: quote every field, regardless of type or content.

               

              Another option is to export vertical tabs instead of returns.

              • 4. Re: importing from CSV file errors
                gobbles

                These replies have all been extremely useful.

                 

                I had started simply replacing carriage returns in the CSV with blank spaces at the google script end but based on the examples given above I think I will be a bit more thorough in my solution.

                 

                 

                thank you!