[solved] 500.000 record to Sqlite
lazarus / 16-Databases

----------------------------------------------------------------------
Boekelund · 2021-08-22 12:42

Import 500.000 record from Csv file to Sqlite.
Record with 40 items pro line, seperator “;”.
Csv first line with field name.
Field content in record with different size.

I can make DB and import with fixet field size.
But how to get the field size for the different items ,
some items are 10 in size, other 200 in size.

I can import to stringgrid and autosize col, but it takes long time.

Any idears to speed up process to get field size? ( max size for  col)

----------------------------------------------------------------------
howardpc · 2021-08-22 13:26

I don't know if it would speed up the process or not. But you could
pre-process with a utility function like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses
Classes, SysUtils;   function GetTxtFieldSizes(const aCSVFile: String;
aSeparator: Char=';'): TSizeArr;  var    sl, lineSl: TStringList;   
i, fieldCount, j: Integer;  begin    SetLength(Result{%H-}, 0);    if
not FileExists(aCSVFile) then Exit;    sl := TStringList.Create;   
lineSl := TStringList.Create;    try      lineSl.Delimiter :=
aSeparator;      lineSl.StrictDelimiter := True;     
sl.LoadFromFile(aCSVFile);      for i := 0 to sl.Count-1 do       
begin          lineSl.DelimitedText := sl[i];          case i of     
     0: begin                 fieldCount := lineSl.Count;            
   SetLength(Result, fieldCount);                 for j := 0 to
lineSl.Count-1 do                   Result[j] := Length(lineSl[j]);  
           end;            otherwise              Assert(fieldCount =
lineSl.Count, aCSVFile+' has invalid data at line '+(i+1).ToString); 
           for j := 0 to lineSl.Count-1 do                if
Length(lineSl[j]) > Result[j] then                  Result[j] :=
Length(lineSl[j]);          end;        end;    finally     
lineSl.Free;      sl.Free;    end;  end;

----------------------------------------------------------------------
Boekelund · 2021-08-22 16:56

Howardpc
Wow!
600.000 record with 47 fields,(size 7 - 357) in 2-3 sec .

That’s speed.

Best
Bo