Topic: [Script] Import-Export data

Completely universal script to export/import data from a table. Just specify database fields in parameters of procedure.


procedure Form1_bExport_OnClick (Sender: string; var Cancel: boolean);
begin
    Export('SELECT lastname, firstname, salary, dateofbirth, isSmoke, comment FROM employees', '1.csv');
end;

procedure Form1_bImport_OnClick (Sender: string; var Cancel: boolean);
begin
    Import('employees', 'lastname,firstname,salary,dateofbirth,isSmoke,comment', '1.csv');
end;

procedure Form1_bImportCheckDup_OnClick (Sender: string; var Cancel: boolean);
begin
    Import('employees', 'lastname,firstname,salary,dateofbirth,isSmoke,comment', '1.csv', 'lastname,firstname');
end;                                             


                                                 
procedure Export (SQL: string; FileName: string);
var
    Results: TDataSet;
    i,c: integer;
    sl: TStringList;
    sValue, sValues: string;
begin

    SQLQuery(sql, Results);
    sl := TStringList.Create;
    c := Results.FieldCount-1;

    while not Results.eof do
    begin
        sValues := '';
        for i := 0 to c do
        begin
            sValue := Results.Fields.Fields[i].AsString;

            if (Results.Fields.Fields[i].DataType = ftFloat) or (Results.Fields.Fields[i].DataType = ftLargeint) then
                sValue := ReplaceStr(sValue, ',', '.')
            else
            begin
                sValue := ReplaceStr(sValue, ',', ',');
                sValue := ReplaceStr(sValue, #13#10, '|');
                sValue := '''' + escape_special_characters(sValue) + '''';
            end;

            sValues := sValues + sValue+',';
        end;

        if sValues <> '' then SetLength(sValues, Length(sValues)-1);
        sl.Add(sValues);
        Results.next;
    end;

    sl.SaveToFile(FileName);
    sl.Free;
    Results.Close;
end;

procedure Import(TableName, Fields: string; FileName: string; CheckDuplicateFields: string = '');
    function ArrayInCheck(a: array of string; sValue: string): boolean;
    var
        i,c: integer;
    begin
        result := False;
        c := Length(a)-1;
        for i := 0 to c do
        begin
            if LowerCase(a[i])=LowerCase(sValue) then
            begin
                result := true;
                break;
            end;
        end;
    end;
var
    iField, cField: integer;
    iLine, cLine: integer;
    sl: TStringList;
    arrValues: array of string;
    arrFields: array of string;
    arrFieldsDuplicate: array of string;
    sValue, sValues: string;
    sWhereDuplicate: string;
begin
    if CheckDuplicateFields <> '' then arrFieldsDuplicate := SplitString(CheckDuplicateFields, ',');

    sl := TStringList.Create;
    sl.LoadFromFile(FileName);
    arrFields := SplitString(Fields, ',');
    cLine := sl.Count-1;

    for iLine := 0 to cLine do
    begin
        sValues := '';
        arrValues := SplitString(sl[iLine], ',');
        if Length(arrFields) <= Length(arrValues) then
        begin
            sWhereDuplicate := '';
            cField := Length(arrFields)-1;
            for iField := 0 to cField do
            begin
                sValue := arrValues[iField];
                sValue := ReplaceStr(sValue, '&comma;', ',');
                sValue := ReplaceStr(sValue, '|', #13#10);
                sValues := sValues + sValue+',';


                if ArrayInCheck(arrFieldsDuplicate, arrFields[iField]) then
                    sWhereDuplicate := sWhereDuplicate +'('+ arrFields[iField]+'='+sValue + ') AND ';
            end;

            if sValues <> '' then
            begin
                SetLength(sValues, Length(sValues)-1);

                if sWhereDuplicate <> '' then
                begin
                    SetLength(sWhereDuplicate, Length(sWhereDuplicate)-5);
                    if SQLExecute('SELECT COUNT(id) FROM '+TableName+' WHERE '+sWhereDuplicate)=0 then
                        SQLExecute('INSERT INTO '+TableName+' ('+Fields+') VALUES ('+sValues+')');
                end else
                    SQLExecute('INSERT INTO '+TableName+' ('+Fields+') VALUES ('+sValues+')');
            end;

        end;
    end;

    sl.Free;
end;


Example: http://myvisualdatabase.com/forum/misc. … download=1

Dmitry.

2 (edited by prahousefamily 2017-09-12 02:24:57)

Re: [Script] Import-Export data

Grate Function But have little Question

  • How To Export With Head Column ??? or How Add head column in file export ???



  • if (Results.Fields.Fields(i).DataType = ftFloat) or (Results.Fields.Fields(i).DataType = ftLargeint) then
                sValue := ReplaceStr(sValue, ',', '.')

    Error ??? if value float
    1,000.00  >>> 1.000.00 Not Correct

My Visual Database : I Love You
Easy For Beginner Student For Me

Re: [Script] Import-Export data

prahousefamily wrote:
  • How To Export With Head Column ??? or How Add head column in file export ???

procedure Form1_bExport_OnClick (Sender: string; var Cancel: boolean);
var
    sl: TStringList;
begin
    Export('SELECT lastname, firstname, salary, dateofbirth, isSmoke, comment FROM employees', '1.csv');
    sl := TStringList.Create;
    sl.LoadFromFile('1.csv');
    sl.Insert(0, 'lastname, firstname, salary, dateofbirth, isSmoke, comment');
    sl.SaveToFile('1.csv');
    sl.Free;
end;

prahousefamily wrote:
  • if (Results.Fields.Fields(i).DataType = ftFloat) or (Results.Fields.Fields(i).DataType = ftLargeint) then
                sValue := ReplaceStr(sValue, ',', '.')

    Error ??? if value float
    1,000.00  >>> 1.000.00 Not Correct


1,000.00 it's not correct number for CSV file, how you get csv file with this number?

Dmitry.

4 (edited by prahousefamily 2017-09-13 01:34:37)

Re: [Script] Import-Export data

Thank You Dmitry. For Answer

prahousefamily wrote:
if (Results.Fields.Fields(i).DataType = ftFloat) or (Results.Fields.Fields(i).DataType = ftLargeint) then
            sValue := ReplaceStr(sValue, ',', '.')
Error ??? if value float
1,000.00  >>> 1.000.00 Not Correct

I Try Check 
1,000.00 it's not correct number for CSV file because if value Float,Integer display 1000.00 not show 1,000.00   Thank you  Dmitry.  For ReCheck Value



And have little Question

IF use syntax

'SELECT * FROM TableNameA left outer join TableNameB on TableNameA.id = TableNameB.id'

in Function

Export( 'SELECT * FROM TableNameA left outer join TableNameB on TableNameA.id = TableNameB.id' ,  '1.csv' );

How To Add ColumnName ???

select *
My Visual Database : I Love You
Easy For Beginner Student For Me

Re: [Script] Import-Export data

prahousefamily wrote:

And have little Question

IF use syntax

'SELECT * FROM TableNameA left outer join TableNameB on TableNameA.id = TableNameB.id'

in Function

Export( 'SELECT * FROM TableNameA left outer join TableNameB on TableNameA.id = TableNameB.id' ,  '1.csv' );

How To Add ColumnName ???

select *

Please explain your question in more details.

Dmitry.

Re: [Script] Import-Export data

Thank You Dmitry.

Now Can Fix Problem Add Column Name In  Export data From Dmitry example code

 sl.Insert(0, 'lastname, firstname, salary, dateofbirth, isSmoke, comment');

Can  apply code modify to type loop

z := Results.FieldCount-1;
    For x := 0 to z do
    begin
        hsValue  := Results.Fields[x].FieldName;
        hsValues := hsValues + hsValue+',';
    end;
    if hsValues <> '' then SetLength(hsValues, Length(hsValues)-1);
    sl.Insert(0,hsValues);

Thank You Dmitry.  Again

My Visual Database : I Love You
Easy For Beginner Student For Me