csv .import problems

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

csv .import problems

Antonio Francino
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

Reply | Threaded
Open this post in threaded view
|

RE: csv .import problems

Fred Williams

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:[hidden email]]
Sent: Tuesday, September 27, 2005 7:13 PM
To: [hidden email]
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