3 Replies Latest reply on Sep 29, 2009 2:51 PM by comment_1

    Issues while importing a .csv file

    pratyk

      Title

      Issues while importing a .csv file

      Post

      Hi,

       

      I have a .csv import being generated everyday which has "" as text qualifier and a comma as the delimiter. Now, there are some fields in the records that are essay type responses. The total number of fields in a record is about 150. Now, since they are essay type questions, it often happens that people use " xxx, qqqq" in the responses .. 

       

      however, this creates a problem. say the response in the exported file field comes across as  (start of field) " text text "xxx, qqq" some more text " (end of field)

       

      When FileMaker imports this record, It starts at the first double quote (the qualifier) and reaches "xxx where it thinks that the response ends and everything following the comma i.e. qqq some more text, is pushed down to the next field. So in essence it pushes down things by 1 record (for 1 comma) .. 

       

      Is there a way to solve this problem besides using a delimiter like ~ or ^ ? 

       

      THanks. 

        • 1. Re: Issues while importing a .csv file
          comment_1
             I don't understand your description of the file's contents. Only unquoted commas are field delimiters - if the field contains commas within quotes, Filemaker will import it correctly.
          • 2. Re: Issues while importing a .csv file
            pratyk
              

            Hi,

             

            I am sorry if i wasn't clear the first time. 

             

            So, in the SQL database, the response is like this:

             

            field1 = I would like my badge to read "Arnold, The Benovelent" and that is pretty much it.

             

            Now, When it gets exported with "" as qualifiers, it will be 

             

            "field1","I would like my badge to read "Arnold, The Benovelent" and that is pretty much it."

             

            However, when i import the file in FM, it  looks at the above string and imports it as:

             

            1st field is field1

             

            2nd field is  I would like my badge to read Arnold

             

            3rd field is  The Benovelent and that is pretty much it.

             

            instead of 

             

            1st field is field1  and 2nd field as  I would like my badge to read "Arnold, The Benovelent" and that is pretty much it.

             

             

            Any idea how to remedy this?

             

             

             

            • 3. Re: Issues while importing a .csv file
              comment_1
                

              I am still not sure I follow this, but it sounds like your source is not a valid CSV file. In the CSV format, if you have data such as:

               

              Record: 1

              Value: This "word" is quoted.

               

              Record:2

              Value: This text, it has a comma.

               

              your file should look like this:

               

              "1","This ""word"" is quoted." "2","This text, it has a comma."

               

              Note the double quotes around the quoted word. See also:

              http://en.wikipedia.org/wiki/Comma-separated_values#Specification