problem in reading a CSV file

• • Reply

  13 years ago

  by Mtisu

  Sungeni Maleta

  , Malawi

  Joined 13 years ago

  Hi alll,

   I have a problem in reading a csv file. My probelm is when i read a comma seperated text file  into a csv file,

  i got exta columns as there are commas between text. for example my text file is like this..........

  /........................../

  philipsco.ltd,usa,james

  k.w.p. philipines,co.ltd, new jersey, Rosy

  Samsung co,ltd, japan, rekhamithal

  /....................../

  when i read this file, comma betweem philipines,co.ltd is divided into two columns which has to be in one column.Pls help me.......i need it urgently

   

  Report abuse
• • Reply

  13 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 years ago

  Well that's just not a proper CSV file then.  How are you supposed to magically work out which commas are between field values and which are within if there's nothing to differentiate them?  You can't, so that's not a valid CSV file.

  If field values in a CSV file do, or may, contain commas themselves then the proper way to write the file is to enclose every field value in double-quotes.  Now you know that any comma within a pair of double-quotes is part of a field value, while any outside are delimiters.  Any double-quotes within field values get escaped with another double-quote.  Now you can read the entire file and never have any doubt what belongs to what field.  As your file is that is impossible.  The best you could do is guess, which is worse than nothing at all. 

  Report abuse
• • Reply

  13 years ago

  by jefftullin

  Jeff Tullin

  , United Kingdom

  Joined 13 years ago

  If you are not in control of the source file, you have a couple of choices.

  a) Complain to the person who generated it and get them to fix it as described above.

  "Field, the first","Second","Third"

  b) If there are always supposed to be 3 fields, and the spurious commas only show up in the first field:

  Read the line in.

  Use Split to split the input into fields, with a comma delimiter

  If there are 4 fields in the result,

  field 1 = inputfield1 & inputfield2

  Field2 = inputfield3

  Field3 = inputfield4

  otherwise, 1=1, 2=2, and 3 = 3 as you would expect.

  If commas can show up anywhere, there really is no hope for this file.

   

   

  Report abuse