Solved

AdoQuery, Adotable, Save to a csv file.

Posted on 2006-11-01
9
2,466 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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