How to read CSV files with TStringList.CommaText when the value between commas contains a space (Views: 33)
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; |