Jump to content

RFC-4180 formatted CSV file parser


Porter

Recommended Posts

After many frustrating experiences with patched together CSV parsers (admittedly of my own creation), I've finally broken down and developed a CSV file parser that follows the RFC-4180 format: http://tools.ietf.org/html/rfc4180

 

That is to take into account double quotes and commas inside double quotes and multiple-line entries. This is essential if you plan on storing user-input text data or storing numbers that may have a comma as a decimal point.

 

From what I can tell, this is the same format that Open Office, Libre Office and Microsoft Excel use for their CSV file export. Therefore, using this format, you can export from LabVIEW to CSV, open it in Excel, make changes, export from Excel to CSV then import it back to LabVIEW without everything being broken by a missing double quotation mark or an extra comma.

 

Attached is the VI to parse a CSV formatted string to a 2D array of strings for LV 2013, 2012 and 8.6. I've also attached an example CSV file (inside Test1.zip) exported from LibreOffice to demonstrate some of the tricky cases that most CSV parsers can't handle.

 

Please give it a try and let me know your thoughts.

 

Update: I've posted the finished library to the Code Repository Database & File IO catagory here http://lavag.org/files/file/239-robust-csv/

Edited by Porter
  • Like 2
Link to post

I posted something similar on the NI forums several years ago.

 

Someone pointed out to me there that some apps will generate opening and closing quotes (rather than just straight quotes) when saving as CSV.

 

Here is some opening and closing quotes data based on the wikipedia entry for CSV files example that you could test with:

 

Year,Make,Model,Description,Price1997,Ford,E350, “ac, abs, moon”,3000.001999,Chevy,“Venture ““Extended Edition”””,“”,4900.001999,Chevy,“ Venture ““ Extended Edition, Very Large”””,“”,5000.001996,Jeep,Grand Cherokee,“MUST SELL!air, moon roof, loaded”,4799.00

 

My VI can be found here:

 

http://forums.ni.com/t5/LabVIEW/Read-csv-file-with-double-quotes/m-p/1591640#M580390

Link to post

Thanks for the info Phillip.

 

It's unfortunate that I didn't find your post earlier. Your VI is about 8 times faster than mine. Converting to a byte array and replacing CR/LF with ASCII record separators and commas with ASCII unit separators is a very good idea. Using indexed for loops instead of while loops to build the array must also make a big difference.

 

I was also using trim white space on every cell which is actually not correct.

 

I modified your VI slightly to fit my application:

- removed file open and path input/output (I don't want that functionality)

- added support for field delimiters other than the comma. Tested with tab and semicolon.

- force all end of lines to LF before processing. This avoids causing a blank row from a CR+LF.

 

Would you like me to post the modified VI here?

 

Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By Porter
      View File Robust CSV
      This package contains VIs for reading and writing to CSV files that follow the CSV format outlined in RFC-4180 (see http://tools.ietf.org/html/rfc4180). Using this package, you can properly import CSV files that were exported from spreadsheet programs such as Microsoft Excel, Open Office or LibreOffice Calc as well as export CSV files that will be compatible with any program capable of interpreting CSV files.
       
      This implementation of the CSV format is capable of handling escaped fields such as those containing commas (or other field delimiters), multiple lines, and double quotes.
       
      Notes:
      - Starting from V1.1.0, library files are located in "<LabVIEW>\vi.lib\LAVA\Robust CSV" and example is located in "<LabVIEW>\examples\LAVA\Robust CSV".
      Development version available on GitHub: https://github.com/rfporter/Robust-CSV
      Submitter Porter Submitted 02/21/2014 Category Database & File IO LabVIEW Version 8.6 License Type BSD (Most common)  
    • By Porter
      This package contains VIs for reading and writing to CSV files that follow the CSV format outlined in RFC-4180 (see http://tools.ietf.org/html/rfc4180). Using this package, you can properly import CSV files that were exported from spreadsheet programs such as Microsoft Excel, Open Office or LibreOffice Calc as well as export CSV files that will be compatible with any program capable of interpreting CSV files.
       
      This implementation of the CSV format is capable of handling escaped fields such as those containing commas (or other field delimiters), multiple lines, and double quotes.
       
      Notes:
      - Starting from V1.1.0, library files are located in "<LabVIEW>\vi.lib\LAVA\Robust CSV" and example is located in "<LabVIEW>\examples\LAVA\Robust CSV".
      Development version available on GitHub: https://github.com/rfporter/Robust-CSV
    • By Aishwarya Saravanan
      My project involves designing a front end for a synchronous generator using LabVIEW.   It is actually data acquisition and display of signals in a file.   The problem I face when building a program is that:   1)the sampling rate is set to 100 samples with 1 kHz sampling frequency.These signals are generated by daq assistant and i have used merge signals to merge these signals from daq assistant as well  as signals from other radio buttons and Boolean as well. The problem is that when written on a file I am able to see only one instance of Boolean status for every 100 samples daq assistant creates.   2) now iam able to write only 0/1 as status of Boolean in the file.How will I write on /off into file?

    • By Huqs
      Hello Labview Users, 
      I happen to have thousands of csv data file that I work with. The only way I recognize them is putting their characteristics in the file name. Which brings the problem of making the names too long and Microsoft doesn't like to accept long name. So I wanted to build a database for all my files. I am in the preliminary stage of building it ( I have attached the file and some of you may have seen it before). 
      What I want to do is, have all my files in the database with random names and list them based on their characteristics. I want to do that in my application in the place of 'file' box. So that I can click on the file and run it (double-click on the file in application to make them work in active file). based on the parameters listed on the database I want to filter them to find any specific file. How the interface of database should look like is shown blow (Image) . 
      It doesn't have to be a real database, just a directory application. I am trying to make it without the database toolkit.  If anyone can help me out and guide me out or guide me in the right direction then that would be great. Thanks. 

      Multicolumn list box v1.5.vi
    • By Gab
      Hello,
      Is it possble to put signatureline into excel footer using Active X ?
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.