Recent

Author Topic: [solved] 500.000 record to Sqlite  (Read 1389 times)

Boekelund

  • New member
  • *
  • Posts: 6
[solved] 500.000 record to Sqlite
« on: August 22, 2021, 02:42:21 pm »
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)
« Last Edit: August 22, 2021, 06:57:30 pm by Boekelund »

howardpc

  • Hero Member
  • *****
  • Posts: 3829
Re: 500.000 record to Sqlite
« Reply #1 on: August 22, 2021, 03:26:43 pm »
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][+][-]
  1. uses Classes, SysUtils;
  2.  
  3.   function GetTxtFieldSizes(const aCSVFile: String; aSeparator: Char=';'): TSizeArr;
  4.   var
  5.     sl, lineSl: TStringList;
  6.     i, fieldCount, j: Integer;
  7.   begin
  8.     SetLength(Result{%H-}, 0);
  9.     if not FileExists(aCSVFile) then Exit;
  10.     sl := TStringList.Create;
  11.     lineSl := TStringList.Create;
  12.     try
  13.       lineSl.Delimiter := aSeparator;
  14.       lineSl.StrictDelimiter := True;
  15.       sl.LoadFromFile(aCSVFile);
  16.       for i := 0 to sl.Count-1 do
  17.         begin
  18.           lineSl.DelimitedText := sl[i];
  19.           case i of
  20.             0: begin
  21.                  fieldCount := lineSl.Count;
  22.                  SetLength(Result, fieldCount);
  23.                  for j := 0 to lineSl.Count-1 do
  24.                    Result[j] := Length(lineSl[j]);
  25.                end;
  26.             otherwise
  27.               Assert(fieldCount = lineSl.Count, aCSVFile+' has invalid data at line '+(i+1).ToString);
  28.               for j := 0 to lineSl.Count-1 do
  29.                 if Length(lineSl[j]) > Result[j] then
  30.                   Result[j] := Length(lineSl[j]);
  31.           end;
  32.         end;
  33.     finally
  34.       lineSl.Free;
  35.       sl.Free;
  36.     end;
  37.   end;

Boekelund

  • New member
  • *
  • Posts: 6
Re: 500.000 record to Sqlite
« Reply #2 on: August 22, 2021, 06:56:37 pm »
Howardpc
Wow!
600.000 record with 47 fields,(size 7 - 357) in 2-3 sec .

That’s speed.

Best
Bo

 

TinyPortal © 2005-2018