[SOLVED] Reading CSV file with extra commas inside quotes..

[Ouiji]

I have found several posts about reading .csv file which are comma delineated. Unfortunately none of them take into account that when data inside the csv file has a comma in it already, it is places into Quotes "". This keeps throwing off my import and I can't seem to get past it.. any ideas?

Thanks,
-ouiji

Example:
Account Name,Type,Date,Product

"Delphi Pages, INC",Web Based Help,06/16/2012,Delphi Help

[The comma already in the name throws off the import]

[Marsheng]

This is a long way round but use the idea for your own formula

In your input line, search for "
then truncate your input line up to and including the "
search for " again then delete all data after and including "
you now have the text left. Search for , and replace it with something never used eg &*.
Now recreate the original string with the new string created and do the import.

When complete search through the imported data and replace &* with ,

If there are more than one set of " in the original string you will have to go through each one.

[Ouiji]

I appreciate the reply, I tried something similar to what you are suggesting, but it was going character by character changing the commas inside the quotes to another unused character like |. Unfortunately the csv files are all 7-12mb+ and the time it takes to load and process is ridiculous. I will give your method a try and see if it comes out any quicker.

Thanks again,
- ouiji

[Marsheng]

The only way to make it quicker is to make some valid assumptions.
If no " in source data, go to next record/line
if " and no, go to next record/line
If there is only one string per record/line, once you have done the &* replace go to the next line of source data.

7-12 mb That is what computers are good for.

[Donovan]

The Jedi Component Set has a component that would be perfect here called a JVCSVDataset.

It has the ability to load a CSV file and will ignore any commas inside inverted commas, check it out.

http://www.delphi-jedi.org/

[OldTurtle]

I found this procedure in Internet (I had the same problem) and works well.

procedure ReadCSVRecord(Line : string; Strings : TStringList);
var P : PChar;
Field : string;
Quote : boolean;
begin
Strings.Clear;
if length(Line) > 0 then
begin
P := @Line[1];
repeat
Field := '';
if P^ = '"' then
begin
Inc(P);
repeat
while P^ <> '"' do
begin
Field := Field + P^;
Inc(P);
end;
Quote := False;
while P^ = '"' do
begin
Inc(P);
if Quote then Field := Field + '"';
Quote := not Quote;
end;
until (P^ = #0) or Quote;
end
else
begin
while (P^ <> #0) and (P^ <> ',') do
begin
Field := Field + P^;
Inc(P);
end;
end;
Strings.Add(Field);
if P^ = #0 then exit;
Inc(P);
until P^ = #0;
Strings.Add('');
end;
end;

[Ouiji]

Thanks everyone, really appreciate the replies. Everything is working great.

- ouiji