Solved

AdoQuery, Adotable, Save to a csv file.

Posted on 2006-11-01
9
2,514 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month4 days, 1 hour left to enroll

630 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