How to read CSV files with TStringList.CommaText when the value between commas contains a space

Problem/Question/Abstract:

When reading comma separated files with TStringList.CommaText, there is a problem, if the value between commas contains a space it is broken up to two separate values. Any suggestion on how to avoid this?

Answer:

Solve 1:

I had a similar problem and wrote a function that replaces all commas (,) with carriage returns (#13). So you can do:

StringList1.Text := CommaSeparate(TheCSVString);

function CommaSeparate(const szString: string): string;
var
  iLength: integer;
  i: integer;
  bDoubleQuotesActive: boolean;
  szOutput: string;
begin
  iLength := Length(szString);
  bDoubleQuotesActive := False;
  for i := 1 to iLength do
  begin
    if szString[i] = ',' then
    begin
      if not bDoubleQuotesActive then
        szOutput := szOutput + Chr(13);
    end
    else if szString[i] = '"' then
    begin
      if bDoubleQuotesActive then
        bDoubleQuotesActive := False
      else
        bDoubleQuotesActive := True;
    end
    else
      szOutput := szOutput + szString[i];
  end;
  Result := szOutput;
end;


Solve 2:

{ ... }
interface
{So CommaText will have the same meaning as CSV}
TCommaStrings = class(TStringList)
private
  function GetCommaText: string;
  procedure SetCommaText(const Value: string);
public
  property CommaText: string read GetCommaText write SetCommaText;
end;

implementation

function TCommaStrings.GetCommaText: string;
var
  S: string;
  P: PChar;
  I, Count: Integer;
begin
  Count := GetCount;
  if (Count = 1) and (Get(0) = '') then
    Result := '""'
  else
  begin
    Result := '';
    for I := 0 to Count - 1 do
    begin
      S := Get(I);
      P := PChar(S);
      while not (P^ in [#0..' ', '"', ',']) do
        P := CharNext(P);
      if (P^ <> #0) then
        S := AnsiQuotedStr(S, '"');
      Result := Result + S + ',';
    end;
    System.Delete(Result, Length(Result), 1);
  end;
end;

procedure TCommaStrings.SetCommaText(const Value: string);
var
  P, P1: PChar;
  S: string;
begin
  BeginUpdate;
  try
    Clear;
    P := PChar(Value);
    while P^ in [#1..' '] do
      P := CharNext(P);
    while P^ <> #0 do
    begin
      if P^ = '"' then
        S := AnsiExtractQuotedStr(P, '"')
      else
      begin
        P1 := P;
        while (P^ >= ' ') and (P^ <> ',') do
          P := CharNext(P);
        SetString(S, P1, P - P1);
      end;
      Add(S);
      while P^ in [#1..' '] do
        P := CharNext(P);
      if P^ = ',' then
      begin
        repeat
          P := CharNext(P);
        until
          not (P^ in [#1..' ']);
        if P^ = #0 then
          Add('') {Trailing commas ARE another field!}
      end;
    end;
  finally
    EndUpdate;
  end;
end;