Here's a little fix I just wrote for an application for a custom format
import. The incoming file is a .csv generated by MickeySoft Excel. The
code below will scan a text buffer "S" returning a parsed field value.
You must maintain a global referenced variable "RP" for retaining the
current buffer position between calls, or change the function call to
pass it.
Unfortunately for some, the code is Borland Object Pascal. But, hey,
any good programmer can write "C" in any language.
Warning: This is just finished code that has been alpha tested only.
And it is Quick and dirty, so I expect a couple of hundred messages
containing solutions of two lines of code or less solving the same
problem :-)
Fred
-----------------------
function ExtractData(S: string): string;
var
j: Integer;
FS: String;
begin
FS := '';
if (RP = 1) and (S[RP] = FSeprator) then
Inc(RP)
else
begin
if S[RP] = FSeprator then
Inc(RP);
if S[RP] = FDelimiter then
begin
Inc(RP);
repeat
begin
for j := RP to Length(S) do
if S[j] = FDelimiter then
break;
if (S[j] = FDelimiter) and (S[j+1] = FDelimiter) then
Inc(j);
FS := FS + Copy(S, RP, j - RP);
Inc(j);
RP := j;
end
until (S[j] = FSeprator) or (j >= Length(S));
end
else
begin
if s[RP+1] = FSeprator then
Inc(RP)
else
begin
for j := RP to Length(S) do
if S[j] = FSeprator then
break;
FS := FS + Copy(S, RP, j - RP);
end;
RP := j;
end;
end;
Result := FS;
end;
---------------------
-----Original Message-----
From: Antonio Francino [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 27, 2005 7:13 PM
To: [email protected]
Subject: [sqlite] csv .import problems
2 problems when importing data in sqlite3 with .import:
1. It leaves quotes in. If data field is enclosed in quotes, they are
'doubled'. (As reported in this list on Feb 16th)
2. When a string contains a comma, it is interpreted as separator, and
an error is issued, of the type -"4 fields were expected, and there were
6 "
There is a workaround.
For (1), taking all quotes out with a tect processor, and .importing in
".mode list". But not easy if some string data contain a comma, since
then format gets corrupted.
For (2), change the separator character to something else such as | or
TAB.
But it is a pity that csv format is not correctly read in. Anyway, that
happens with most database managers I've seen.
Antoni
