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
            if not Results.Fields.Fields[i].isNull then
            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;
            end else sValue := 'NULL';

            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, 973 downloads since 2018-01-08 

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

Re: [Script] Import-Export data

Dear Dmitriy. How to include second table data in export and import. This question was in previous post but no answer, so i would like you to help me with this script.
Thanks.

AD1408 wrote:

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.

Re: [Script] Import-Export data

You can export any number of tables to one CSV file, because you can include many table using SQL query.
But using this script, you can't import data in two table in the same time, only one by one.

Dmitry.

Re: [Script] Import-Export data

Would you like to show the link to the project if it is exist in forum?

Re: [Script] Import-Export data

ivan wrote:

Would you like to show the link to the project if it is exist in forum?

How to export data from two tables?
Please, create new topic where you can describe in details your question.

Dmitry.

Re: [Script] Import-Export data

Не подскажете как в конце экспорта вывести сообщение о количестве экспортированных записей?