?
Solved

AdoQuery, Adotable, Save to a csv file.

Posted on 2006-11-01
9
Medium Priority
?
2,709 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…

589 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