# Using FasterCSV to clean CSV file **URL:** \ **Category:** Ruby\ **Created:** [November 17, 2009, 10:12pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111 "2009-11-17T22:12:16Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 17, 2009, 10:12pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/1 "2009-11-17T22:12:16Z")
Hello all, I was wondering, can FasterCSV be used to clean a csv file of unwanted characters, endline spaces, tab spaces and other characters? I have a csv file 6,000 lines long and it comes from another department. I have been working to read this file into a database via FasterCSV. Went I start to read the file in, I get the dreaded “FasterCSV::MalformedCSVError” error message. I’ve worked with this in the past and the solution was to “clean” the data. EOL spaces and other undesired characters were scattered within the file. Is it possible to use FasterCSV for this task? JohnM ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 17, 2009, 10:26pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/2 "2009-11-17T22:26:09Z")
On Nov 17, 2009, at 3:12 PM, John M. wrote: > I was wondering, can FasterCSV be used to clean a csv file of unwanted > characters, endline spaces, tab spaces and other characters? I believe the answer to this question is yes. I’ve already shown you how to modify headers as they are read. There’s a similar system for normal fields. Now, this does not modify the file on disk, obviously. If you want to do that, it would be best to write out a new and corrected file. You could then rename the new file to replace the old, if needed. > I have a csv file 6,000 lines long and it comes from another department. > I have been working to read this file into a database via FasterCSV. > Went I start to read the file in, I get the dreaded > “FasterCSV::MalformedCSVError” error message. I’ve worked with this in > the past and the solution was to “clean” the data. EOL spaces and other > undesired characters were scattered within the file. FasterCSV does need valid CSV data, yes. If you have non-valid CSV data, it will need to be cleaned before FasterCSV can read it. > Is it possible to use FasterCSV for this task? If your data is valid CSV, FasterCSV can read and transform it. If your data is not valid CSV, you will need to fix it before FasterCSV can read it. Hope that helps. James Edward G. II ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 17, 2009, 10:46pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/3 "2009-11-17T22:46:09Z")
Thanks for the reply James. I have read and imported into DB simple CSV data in the past and never had problems like this file. > FasterCSV does need valid CSV data, yes. If you have non-valid CSV > data, it will need to be cleaned before FasterCSV can read it. The question of validity is confusing. When a file is saved via MS-Excel isn’t it valid? I understand that there are several different formats (MS-DOS, Windows, MAC) when considering saving a CSV. Which one should be chosen? John ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 17, 2009, 11:01pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/4 "2009-11-17T23:01:44Z")
John M. wrote: > Thanks for the reply James. > > I have read and imported into DB simple CSV data in the past and never > had problems like this file. > > > FasterCSV does need valid CSV data, yes. If you have non-valid CSV > > data, it will need to be cleaned before FasterCSV can read it. > > The question of validity is confusing. When a file is saved via > MS-Excel isn’t it valid? I wouldn’t trust any Microsoft program to produce valid data in any interchange format. > I understand that there are several different formats (MS-DOS, Windows, > MAC) when considering saving a CSV. Which one should be chosen? Those have to do with text encodings and line breaks. They’re probably beside the point. (And you mean “Mac”, not “MAC” [= Media Access Card].) > John ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 18, 2009, 12:36am UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/5 "2009-11-18T00:36:41Z")
On Nov 17, 2009, at 3:46 PM, John M. wrote: > > FasterCSV does need valid CSV data, yes. If you have non-valid CSV > > data, it will need to be cleaned before FasterCSV can read it. > > The question of validity is confusing. When a file is saved via > MS-Excel isn’t it valid? I expect assume FasterCSV would read the files Excel saves, yes. (Hope I don’t end up regretting that.) > I understand that there are several different formats (MS-DOS, Windows, > MAC) when considering saving a CSV. Which one should be chosen? I doubt it matters. The only different should be line endings, which FasterCSV is going to try and guess anyway. I’m betting your problem is one of encodings. Do you have some non-ASCII data in the spreadsheet? If that data is not UTF-8, it could be tripping up FasterCSV’s parser. James Edward G. II ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 18, 2009, 11:52pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/6 "2009-11-18T23:52:40Z")
John M. wrote: > Well, > As you suggested below, it was a problem with encoding. > > > I’m betting your problem is one of encodings. Do you have some > > non-ASCII data in the spreadsheet? If that data is not UTF-8, it could > > be tripping up FasterCSV’s parser. > > What I did was I took the file and opened it in ‘TextEdit’ a Mac version > of MS-Notepad. TextEdit isn’t a Mac version of Notepad. It’s a text editor that comes with the OS, but the similarities end there. > Saved the file in UTF-8 and everything took off. > I’m going to try the same task in MS-Notepad (when I fire up the Windows > VM). It may not work. Notepad is ridiculously underpowered compared to TextEdit. > Thank you again for all your support. > > John ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 18, 2009, 11:17pm UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/7 "2009-11-18T23:17:55Z")
Well, As you suggested below, it was a problem with encoding. > I’m betting your problem is one of encodings. Do you have some > non-ASCII data in the spreadsheet? If that data is not UTF-8, it could > be tripping up FasterCSV’s parser. What I did was I took the file and opened it in ‘TextEdit’ a Mac version of MS-Notepad. Saved the file in UTF-8 and everything took off. I’m going to try the same task in MS-Notepad (when I fire up the Windows VM). Thank you again for all your support. John ---
**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\ **Post date:** [November 19, 2009, 12:49am UTC](https://www.ruby-forum.com/t/using-fastercsv-to-clean-csv-file/179111/8 "2009-11-19T00:49:59Z")
On Nov 18, 2009, at 4:18 PM, John M. wrote: > Well, > As you suggested below, it was a problem with encoding. Glad to hear you figured it out. If you want to programmatically change the encoding, see Ruby’s standard Iconv library: [http://blog.grayproductions.net/articles/encoding\_conversion\_with\_iconv](http://blog.grayproductions.net/articles/encoding_conversion_with_iconv) James Edward G. II