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

Re: [Script] Import-Export data

Hi Dmitry,


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

Great stuff.................
I was looking for it as it's quite important for me.


I wanted to add a bit of user control, as to specify file name, location etc via windows open and save dialogs. I think I have done it (hopefully correctly) thanks to your kind help providing such script earlier.


Two issues I have faced and couldn't resolve.
1. I wanted include second table data in export and import but couldn't get it working?
2. I wanted to add duplicate counts on info message when importing with duplicate check, but no idea how to do it. I put a comment in the script too about it.


I'd deeply appreciate if you could please fix the attached sample project in respect of the issues stated above.

Post's attachments

Attachment icon Import-Export Dmitry - Work.zip 34.14 kb, 12 downloads since 2018-01-08 

Adam
God... please help me become the person my dog thinks I am.