3 Replies Latest reply on Feb 21, 2007 2:41 PM by Newsgroup_User

    Ideas of how to deal with a non-conforming CSV file?!

    Level 7
      Hi there folks,

      I've been given a CSV file of which I now wish to import into VizionDB --
      unfortunately all of the text fields are not surrounded by quotation marks
      and there's some 9,000 records in total!

      Other than going back to my client, is there anything I can do with said CSV
      file to correct the situation?

      Many thanks in advance, Mark ;-)


        • 1. Re: Ideas of how to deal with a non-conforming CSV file?!
          Level 7
          If all you need is to put quotes around the text in one or more columns
          of the spreadsheet, it is easy in Excel to add them in.

          If cell A1 has the text Mike and you want it to have the text "Mike"
          (with quotes), pick an empty cell, like B1 and put a quote in there.

          Then in C1, put this formula:

          =CONCATENATE(B1,A1,B1)

          That will make the content of C1="mike" Obviously, your columns will be
          different, but the point is, it is pretty easy to add a character to the
          start and end of any given cell (and you can drag the formula down to
          make it apply to A2,A3,A4, etc.

          Alternately, if you can get the data into Director, it is pretty easy to
          add quotes around anything. If you have a variable gInput with a value
          of 42, you can add quotes around it by calling it a string

          gInput=42
          put gInput
          -- 42
          gInput=string(gInput)
          put gInput
          -- "42"
          • 2. Re: Ideas of how to deal with a non-conforming CSV file?!
            duckets Level 1
            If you want quotes around all the fields, and you currently have quotes around none of the fields, you could probably do this using find-and-replace in a word processor.

            Open up the CSV file with 'word' or a similar word processor. The important thing is that the word processor must be able to do a find and replace on 'special' characters, like 'return'.

            Use find and replace to:

            Replace all 'comma' characters with the sequence: quote comma quote
            Replace all 'return' characters with the sequence: quote return quote

            You should then find that your CSV text has quotes around every record, but you'll have an extra quote character at the very start and very end of the file (on separate lines), which you'll need to delete by hand.

            Of course, make a backup of your data first - and this won't work if some of your columns already have quotes, or if some of your fields actually use the 'comma' character inside the field data.

            - Ben





            • 3. Re: Ideas of how to deal with a non-conforming CSV file?!
              Level 7
              Many thanks folks, apologies for the delayed response!

              I managed to set-up an Excel workbook that strips the extraneous data and
              then concatenates the required cells with quotation marks -- it all works
              beautifully!

              Many thanks once again.

              Regards, Mark ;-)