We help IT Professionals succeed at work.

AdoQuery, Adotable, Save to a csv file.

Grant Fullen
Grant Fullen asked
on
Medium Priority
3,316 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
Comment
Watch Question

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.

Author

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

Author

Commented:
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
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.
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;

Author

Commented:
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
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Mayor Thanks fo much for writeing that function. A Life Saver.
Thanks
Grant
No Problem.

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

Best regards,

The Mayor.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.