Handling embedded quotes in csv files
-
Hi, all. I have a problem reading csv files with quotes embedded in a field, below is a sample of the data I'm trying to read, I have no control over the formatting of this file. "A",32140,"Router Cutters & Accessories" "A",32141,"Hinge Hole Cutters & End Mills" "A",32148,"Step Drills" "A",32149,"Subland Drills" "A",32150,"Long Drills" "A",32151,"Drill Blanks" "A",32152,"Stub Drills" "A",32153,"Extra Length Drills" "A",32154,"Taper Pin Drills" "A",32155,"Blacksmiths or 1/2" Shank Drills" 'this line is the problem "A",32156,"Centre Drills" "A",32157,"Socket Drills" "A",32158,"Heavy Duty Drills" "A",32159,"Micro Drills" "A",32160,"MT Shank Drill" "A",32161,"MT Shank Extra Length" "A",32162,"MT Shank Heavy Duty" "A",32163,"MT Shank Long Drill" "A",32164,"1/4" Shank Drills" 'so is this one "A",32165,"Spotting Drill" "A",32166,"Short Carbide Tipped Drill" "A",32167,"MT Shank Carbide Tipped Drill" the code is: Dim _TCode as string, _ItemCode as long, _Description as string FileOpen(filenumber, _Path & File.FileName, OpenMode.Input) While Not EOF(filenumber) Input(FileNumber, _TCode) Input(FileNumber, _ItemCode) Input(FileNumber, _Description) wend =error "read past eof":(( is there anybody who can help me to read these two lines without error. Many thank in anticipation.
-
Hi, all. I have a problem reading csv files with quotes embedded in a field, below is a sample of the data I'm trying to read, I have no control over the formatting of this file. "A",32140,"Router Cutters & Accessories" "A",32141,"Hinge Hole Cutters & End Mills" "A",32148,"Step Drills" "A",32149,"Subland Drills" "A",32150,"Long Drills" "A",32151,"Drill Blanks" "A",32152,"Stub Drills" "A",32153,"Extra Length Drills" "A",32154,"Taper Pin Drills" "A",32155,"Blacksmiths or 1/2" Shank Drills" 'this line is the problem "A",32156,"Centre Drills" "A",32157,"Socket Drills" "A",32158,"Heavy Duty Drills" "A",32159,"Micro Drills" "A",32160,"MT Shank Drill" "A",32161,"MT Shank Extra Length" "A",32162,"MT Shank Heavy Duty" "A",32163,"MT Shank Long Drill" "A",32164,"1/4" Shank Drills" 'so is this one "A",32165,"Spotting Drill" "A",32166,"Short Carbide Tipped Drill" "A",32167,"MT Shank Carbide Tipped Drill" the code is: Dim _TCode as string, _ItemCode as long, _Description as string FileOpen(filenumber, _Path & File.FileName, OpenMode.Input) While Not EOF(filenumber) Input(FileNumber, _TCode) Input(FileNumber, _ItemCode) Input(FileNumber, _Description) wend =error "read past eof":(( is there anybody who can help me to read these two lines without error. Many thank in anticipation.
Dim sLine,sa(),sCode,sItemCode,sDescription Open "TESTFILE" For Input As #1 Line Input #1, sLine ' Read line into variable. Do While Not EOF(1) sa = split(sLine,",") sCode = Replace(sa(0),"""","") sItemCode = Replace(sa(1),"""","") sDescription = Replace(sa(2),"""","") Call ProcessRow(sCode,sItemCode,sDescription) Line Input #1, sLine ' Read line into variable. Loop Close #1
-
Dim sLine,sa(),sCode,sItemCode,sDescription Open "TESTFILE" For Input As #1 Line Input #1, sLine ' Read line into variable. Do While Not EOF(1) sa = split(sLine,",") sCode = Replace(sa(0),"""","") sItemCode = Replace(sa(1),"""","") sDescription = Replace(sa(2),"""","") Call ProcessRow(sCode,sItemCode,sDescription) Line Input #1, sLine ' Read line into variable. Loop Close #1
Thanks for your reply, I thought of using the split function to split the line at commas, but I also thought that whould be a problem with embedded commas. If thats the case I'm replacing one problem with another. Regards Alan
-
Thanks for your reply, I thought of using the split function to split the line at commas, but I also thought that whould be a problem with embedded commas. If thats the case I'm replacing one problem with another. Regards Alan
alanhauxwell wrote:
I thought of using the split function to split the line at commas, but I also thought that whould be a problem with embedded commas. If thats the case I'm replacing one problem with another.
You will probably need to parse the string that you read in... a , followed by " is end of field end of line is end of field or some such thing... Hopefully, you will never get a comma and a double-quote adjacent to each other in your data ;) Hope this helps