Hello all, I am needing to import data from a proprietary corporate website that only exports to CSV. The problem is that their CSV doesn''t seem to be compatible with Ruby''s CSV Library. It is made to open in Excel and does open fine in Excel, and we currently process it with ColdFusion''s cfx_text2Query, but I need a ruby solution to make this integrate with Rails. The error I get from the Ruby CSV Library is IllegalFormatError. The file has empty fields and fields with line breaks in it, as well as many fields with commas inside of quotes. It also seems to use the excel notation of showing a number as a string (e.g. ="62363" ) I have also tried this another way using csv2xml and generating xml, it works fine and I get the xml (which looks good to the eye), but when I try to parse it, rexml gives me a <UNDEFINED> .. </> error in console (nothing shows in the view). I''ve also tried one of the other Ruby CSV libraries, and it gives the same "IllegalFormatError" message, so I guess the last thing to do would have to be to re-write some code to parse the csv file, but it''s a rather complex file, and I figured just manipulating the data with .gsub would be better. Any help would be appreciated! Thanks, Jason Ketterman P.S. The file is rather large, but here is a link to the original exported csv in case there are any other questions: http:// area51.ketterman.tv/import.csv
Jason Ketterman wrote:> The error I get from the Ruby CSV Library is IllegalFormatError. > The file has empty fields and fields with line breaks in it, as well as > many fields with commas inside of quotes. It also seems to use the > excel notation of showing a number as a string (e.g. ="62363" )Looks like the number format and spacing is breaking it. The following works: "U","51","1007","42","O" The following doesn''t: "U","51",="1007","42","O" You''ve also got a space between the closing " and the field separating comma in the MDU field, which gives the same error. Get rid of them, and it parses ok for me. HTH, -- Alex
Awesome it''s working now! Thanks alot, I never would''ve figured that the space between the quote and comma was throwing up the error. Jason On Sep 21, 2005, at 10:20 AM, Alex Young wrote:> Jason Ketterman wrote: > >> The error I get from the Ruby CSV Library is IllegalFormatError. >> The file has empty fields and fields with line breaks in it, as >> well as >> many fields with commas inside of quotes. It also seems to use the >> excel notation of showing a number as a string (e.g. ="62363" ) >> > Looks like the number format and spacing is breaking it. > > The following works: > "U","51","1007","42","O" > > The following doesn''t: > "U","51",="1007","42","O" > > You''ve also got a space between the closing " and the field separating > comma in the MDU field, which gives the same error. > > Get rid of them, and it parses ok for me. > > HTH, > -- > Alex > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >