Solved

AdoQuery, Adotable, Save to a csv file.

Posted on 2006-11-01
9
2,369 Views
Last Modified: 2012-05-05
Hi all I have a access database name is mail.mdb.
Table is MailInfo. I have a adoquery name is ExportQuery. The Sql statment in the query is.Select  Distinct List From MailInfo .
I use this code to import a csv file into my database.
Begin
JvCsvDataSet1.Open;
  While not JvCsvDataSet1.Eof do
  begin
     with ADOTable1 do
     begin
       Insert;
       FieldByName('Salutation').Value :=
JvCsvDataSet1.fieldbyName('SALUTION').AsString;
       FieldByName('FirstName').Value :=
JvCsvDataSet1.fieldbyName('FIRSTNAME').AsString;
       FieldByName('LastName').Value :=
JvCsvDataSet1.fieldbyName('LASTNAME').AsString;
       FieldByName('Company').Value :=
JvCsvDataSet1.fieldbyName('COMPANY').AsString;
       FieldByName('Address1').Value :=
JvCsvDataSet1.fieldbyName('ADDRESS1').AsString;
       FieldByName('Address2').Value :=
JvCsvDataSet1.fieldbyName('ADDRESS2').AsString;
       FieldByName('City').Value :=
JvCsvDataSet1.fieldbyName('CITY').AsString;
       FieldByName('State').Value :=
JvCsvDataSet1.fieldbyName('STATE').AsString;
       FieldByName('Zip').Value :=
JvCsvDataSet1.fieldbyName('ZIP').AsInteger;
       FieldByName('Country').Value :=
JvCsvDataSet1.fieldbyName('COUNTRY').AsString;
       FieldByName('Phone').Value :=
JvCsvDataSet1.fieldbyName('PHONE').AsInteger;
       FieldByName('Email').Value :=
JvCsvDataSet1.fieldbyName('EMAIL').AsString;
       FieldByName('List').Value := CmbImportList.Text;
       Post;
       JvCsvDataSet1.Flush;
       JvCsvDataSet1.SaveToFile('yyy.csv');
       JvCsvDataSet1.Next();
     TsImportwizard.Show;
The Import works great.
I need to export all records in the adotable to a csv file. The Fields are the same as above Like the JvCsvDataSet1.fieldbyname FIRSTNAME,LASTNAME and so on. But the only rows in the database to get exported is the ones that I choose from the combobox wich returns the rows depending on the value in the list Field.
Thanks For looking or helping.
Grant
0
Comment
Question by:Grant Fullen
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 17857255
I have made a procedure to export a complete dataset to a csv-file.
Here is the complete code (make sure you have unit 'Db' in your uses-clause):

function ExportDataSetToCSVFile( DataSet: TDataSet ; ExportFileName, Delimiter : String ; AllowOverwrite: Boolean ): Integer;
var
  F         : TextFile;
  i         : Integer;
  strCsvRec : String;
  wrdRc     : Word;
  bmTmp     : TBookmark;
  blnIsOpen : Boolean;
begin
  { If ExportFile already exists, ask for overwrite-comfirmation. }
  if not FileExists( ExportFileName ) then
    wrdRc := 6 { 6 = mrYes }
  else if AllowOverwrite then
    wrdRc := 6 { 6 = mrYes }
  else
    wrdRc := MessageDlg('Export-File already exists. Overwrite ?', mtConfirmation, [mbYes, mbNo], 0);

  { If no ExportFile exists or the user comfirmed the overwrite, start exporting }
  if ( wrdRc = 7 ) then  { 7 = mrNo }
  begin
    Result := -1;
  end
  else
  begin
    { The Result = Amount of exported records. }
    Result := 0;
    AssignFile( F, ExportFileName );
    Rewrite( F );
    Try
      With DataSet do
      begin
        {
        Keep Active-Status of DataSet. If it was closed when this fucntion was
        called, it should be closed too after the export.
        }
        blnIsOpen := Active;
        if not blnIsOpen then
          Open;
        bmTmp := GetBookmark;  { Save a bookmark to return to after exporting. }
        First;
        DisableControls;  { Be sure no visual controls are updates during export. }
        { Get Column-Name for each field and write CSV-Record (Header) }
        strCsvRec := '';
        for i:=0 to Fields.Count-1 do
          strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].DisplayLabel;
        strCsvRec := Copy( strCsvRec, 2, 999 );
        Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
        { For each record of the dataset, create a SCV-record an write it to ExportFile. }
        While Not Eof do
        begin
          {
          Build the Export-Record by adding each field of the DataSet and use the
          given Delimiter.
          }
          strCsvRec := '';
          for i:=0 to Fields.Count-1 do
            strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].AsString;
          strCsvRec := Copy( strCsvRec, 2, 999 );
          Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
          Inc( Result ); { Uodate Export-Record-Counter }
          Next;
        end; {While Not Eof }
        { Return to bookmarked record. }
        GotoBookmark( bmTmp);
        FreeBookmark(bmTmp);
        { Recover the Active-State of the dataset to its original state. }
        if not blnIsOpen then
          Close;
        EnableControls; { Re-enable all visual controls, linked to the dataset. }
      end; { With DataSet }
      CloseFile( F );
    Except
      Result := -2;
      CloseFile( F );
    end; { Try Except }
  end; { if not FileExists( ExportFileName ) }
end;


Best regards,

The Mayor.
0
 

Author Comment

by:Grant Fullen
ID: 17857456
HEy that function works great it exports all the info. Thanks for writeing all that. But i need it not to export a couple of the fields. Like no autonumber, And i have 2 fields that do not need to be exported. ListID, and Notes. Can we prevent certian fields from being exported.
Thanks
Grant
0
 

Author Comment

by:Grant Fullen
ID: 17857509
The csv file needs to look like this.
SALUTION,FIRSTNAME,LASTNAME,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,COUNTRY,COMPANY,PHONE,EMAIL. This is the format of my import csv file. I i only need to xport the fields that match the value from the combobox . Like list2, or list3.
So if i choose list 3 then only records that have list3 in the List Field of the database.
Thanks
Grant
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 17857722
Weel, best thing you could do i make use of a TADOQuery and build an SQL-Statement that only retrieves the fields you want.
something like :

SELECT SALUTION, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, COMPANY, PHONE, EMAIL
FROM MailInfo
WHERE List = 'List2'
ORDER BY LASTNAME, FIRSTNAME

Then call the function from your code as follows :
Call the function like :
ExportDataSetToCSVFile( ADOQuery1, 'c:\test.csv',';',false );


BTW, The function returns the amount of exported records.
(negative result means an error).

Best regards,

The Mayor.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 17857767
The function I provided always includes a HeaderRow, which contains all the fieldnames of your dataset you provide.

If you want to be able to determine if you want HeaderRow or not, you can use the function below (same as in my previous post, but with an extra parameter 'IncludeHeaderRow'):

function ExportDataSetToCSVFile( DataSet: TDataSet ; ExportFileName, Delimiter : String ; AllowOverwrite: Boolean ; IncludeHeaderRow: Boolean ): Integer;
var
  F         : TextFile;
  i         : Integer;
  strCsvRec : String;
  wrdRc     : Word;
  bmTmp     : TBookmark;
  blnIsOpen : Boolean;
begin
  { If ExportFile already exists, ask for overwrite-comfirmation. }
  if not FileExists( ExportFileName ) then
    wrdRc := 6 { 6 = mrYes }
  else if AllowOverwrite then
    wrdRc := 6 { 6 = mrYes }
  else
    wrdRc := MessageDlg('Export-File already exists. Overwrite ?', mtConfirmation, [mbYes, mbNo], 0);

  { If no ExportFile exists or the user comfirmed the overwrite, start exporting }
  if ( wrdRc = 7 ) then  { 7 = mrNo }
  begin
    Result := -1;
  end
  else
  begin
    { The Result = Amount of exported records. }
    Result := 0;
    AssignFile( F, ExportFileName );
    Rewrite( F );
    Try
      With DataSet do
      begin
        {
        Keep Active-Status of DataSet. If it was closed when this fucntion was
        called, it should be closed too after the export.
        }
        blnIsOpen := Active;
        if not blnIsOpen then
          Open;
        bmTmp := GetBookmark;  { Save a bookmark to return to after exporting. }
        First;
        DisableControls;  { Be sure no visual controls are updates during export. }

        { If wanted, include a HeaderRow, which contains all DataSet's FieldNames }
        if IncludeHeaderRow then
        begin
             strCsvRec := '';
             for i:=0 to Fields.Count-1 do
               strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].DisplayLabel;
             strCsvRec := Copy( strCsvRec, 2, Length(strCsvRec) );
             Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
        end;

        { For each record of the dataset, create a SCV-record an write it to ExportFile. }
        While Not Eof do
        begin
          {
          Build the Export-Record by adding each field of the DataSet and use the
          given Delimiter.
          }
          strCsvRec := '';
          for i:=0 to Fields.Count-1 do
            strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].AsString;
          strCsvRec := Copy( strCsvRec, 2, Length(strCsvRec) );
          Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
          Inc( Result ); { Uodate Export-Record-Counter }
          Next;
        end; {While Not Eof }
        { Return to bookmarked record. }
        GotoBookmark( bmTmp);
        FreeBookmark(bmTmp);
        { Recover the Active-State of the dataset to its original state. }
        if not blnIsOpen then
          Close;
        EnableControls; { Re-enable all visual controls, linked to the dataset. }
      end; { With DataSet }
      CloseFile( F );
    Except
      Result := -2;
      CloseFile( F );
    end; { Try Except }
  end; { if not FileExists( ExportFileName ) }
end;
0
 

Author Comment

by:Grant Fullen
ID: 17863377
HEy thanks for your help. OK I am useing the first functiona and the sql below.
SELECT FirstName,LastName,EMail  FROM MailInfo
WHERE List ='List2'.
I need to figure out how to get the List2 to equal my cmbExportList.
And I need to uppercase all the header names, to the fields just the header names.
Thanks
Grant
0
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 25 total points
ID: 17864597
About your query :

In your code, set the Query its SQL-property like :

// Fill SQL Statement First.
ADOQuery1.SQL.Text := 'SELECT FirstName,LastName,EMail  FROM MailInfo
WHERE List = ' + QuotedStr( cmbExportList.Text );

// Now call the Export-function
ExportDataSetToCSVFile( ADOQuery1, 'c:\test.csv',';',false );


About the uppercase : What do you mean, do you want the column-names in your csv file (= Header Row) to be in uppercase ?
If so, then make a little change to the function I posted:
....
for i:=0 to Fields.Count-1 do
    strCsvRec := strCsvRec + Delimiter +
                        UpperCase(Fields.Fields[i].DisplayLabel);
....

Best regards,

The Mayor.
0
 

Author Comment

by:Grant Fullen
ID: 17871512
Mayor Thanks fo much for writeing that function. A Life Saver.
Thanks
Grant
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 17879338
No Problem.

Glad to be of some help.
Thanks for the points and the grade.

Best regards,

The Mayor.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now