none
Export an Access Table to a csv file

    Question

  • Hi...

    I'm trying to export a table that I have in a Access Db to a csv file. For now, I'm exporting it to a Excel file, but I can't make it to a csv file. Can someone help me?

    This is the code that I'm using to export the data to excel...First the query...

    DoCmd.RunSQL "SELECT [Files]  INTO Table2 FROM Table1;"

    and then the code line:

    DoCmd.TransferSpreadsheet acExport, 8, "Table2", "C:\Table2", True

    or if anybody can tell how to do it from the excel file that'll be great too!

    Thanks for your time.

     

    Monday, May 15, 2006 10:59 PM

Answers

  • In this case you can use the following command:

    DoCmd.TransferText acExportDelim, "Standard Output", _
        "External Report", "C:\Txtfiles\MyText.csv"

    Tuesday, May 16, 2006 7:27 AM
  • Hi Galford,

    Are you doing this as a one-off exercise or are you trying to do this as part of an application?

    Assuming Access 2003, If you are doing it as a one-off then try this:

         Open the table

         File (Menu) --> Export

         Save as Type 'Text Files'

    Don't use the 'save formated' option

    That will bring up the Export Text Wizard

         Choose Delimited, click next

         Choose 'Comma' delimiter and " as Text qualifier.

         Choose 'Include Field names on First Row' if you want column headings

         Click next

         Give it a name and click finish

    That will give you a CSV file.

    If you want to do it as part of the application take a look at docmd.TransferText in MS Access help.

    I hope this puts you on the right path.

    Regards

    Nigel

    Tuesday, May 16, 2006 7:29 AM

All replies

  • In this case you can use the following command:

    DoCmd.TransferText acExportDelim, "Standard Output", _
        "External Report", "C:\Txtfiles\MyText.csv"

    Tuesday, May 16, 2006 7:27 AM
  • Hi Galford,

    Are you doing this as a one-off exercise or are you trying to do this as part of an application?

    Assuming Access 2003, If you are doing it as a one-off then try this:

         Open the table

         File (Menu) --> Export

         Save as Type 'Text Files'

    Don't use the 'save formated' option

    That will bring up the Export Text Wizard

         Choose Delimited, click next

         Choose 'Comma' delimiter and " as Text qualifier.

         Choose 'Include Field names on First Row' if you want column headings

         Click next

         Give it a name and click finish

    That will give you a CSV file.

    If you want to do it as part of the application take a look at docmd.TransferText in MS Access help.

    I hope this puts you on the right path.

    Regards

    Nigel

    Tuesday, May 16, 2006 7:29 AM
  • the function given by you works, but i would like to export fields delimited by * and I would also like the strings to be exported without the quotation marks (" ")

    so instead of:

    1,"sample text",0,0

    i would like to have:

    1*sample text*0*0



    is it possible? Smile
    Tuesday, April 24, 2007 7:03 PM
  • I have recently converted a database from Access 97 to Access 2003 which used the TransferTest command

    DoCmd.TransferText acExportDelim, , "Query name", "c:\path\filename"  -  which worked fine.

    However, if I try to run this line of code in Access 2003 I get a message "Cannot update. Database or object may be read-only".

    Anybody know how to make this work? 

    Saturday, April 28, 2007 4:26 PM
  • I am trying to export a table into a .csv format, but when I do, I lose the leading four zeroes on one of the fields. I need the four leading zeros to stay put when exporting. Does anyone know if this is possible?
    Tuesday, May 29, 2007 7:29 PM
  • what is the data type of the table ?

     

    Is it integer?

    Wednesday, May 30, 2007 9:06 AM
  • The table consists of numerous fields, but this one in particular is a text field. When the table was initially imported into the database, it was a Number field. Since we needed the 11-digit number, we then created an Update Query to add back in the leading zeroes, but had to change it over to a text field. Now when I export it out of Access into a .csv file, the leading zeroes are removed automatically by the system again.

    Wednesday, May 30, 2007 3:55 PM
  • Hi,

     

    I am trying to do something similar, but I need to have the quotation marks (" ") around all fields, including those that are empty.

     

    i.e.  "1","sample text","0"," "

     

    I know its possible as I have seen it done.  I just can't seem to recreate it myself.

     

    Help!

    Monday, September 15, 2008 8:41 AM
  • I have a Problem in ACCESS 2003. Im trying to export a table to a csv file, but its imposible to export a number with four decimals, Access converts it in a cientific notation.

     

    ¿Does anyone knows how to avoid this way of work....?   I don't want to export it as text.

     

    Example     Price : 0,0019      export as 1,9e-03.

     

     

     

    Thursday, October 16, 2008 2:08 PM
  • I need to export a table from an Access database as a csv file. Two of the fields are empty with no value in it, but with double quotes as the place hloder. I can see "" populates those two fields when starting using export text wizard, but once the export text wizard finishes, the "" are gone from those fields.
    how can I export the table with "" populating empty fields?
    any help is highly appreciated.
    Tuesday, December 08, 2009 9:39 PM
  • DoCmd.TransferText acExportDelim, "KlasExportspecificatie", "Resultaten", "C:\Users\HAL\Desktop\" & klKlassen.Value & " " & sKeuze & ".csv"
     
    "KlasExportspecificatie"           is the exportspecification I created with the export wizard, containing the settings for CSV files
    "Resultaten"                           is the table from which the data is exporting
    "C:\Users\HAL\Desktop\" & klKlassen.Value & ".csv"                 is the file path to where the file is exported
    klKlassen.Value                      is the value from a list-box which contains a part of the filename

    The trick is to make a exportspecification in the Export Wizard.
    Right-click the exported table. Choose EXPORT, TXT-file.
    All checkboxes not checked.
    Use on the next page the ADVANCED-button to modify and save the exportspecification.

    That does the trick, HAL
    Monday, January 18, 2010 4:16 PM
  • HAL4u -

    Your instruction were clear and the process worked well with one exception -  I would like to include the header row from the table in the .csv file


    Even though I step through the export and include that choice before I go to the Advanced button to modify and save, the export does not include the header row from the table in the .csv file.

    Help!

    and thank you!

    Friday, May 14, 2010 6:17 PM
  • To add the header:

    DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

    HasFieldNames   Optional Variant. Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.

    ex:

    DoCmd.TransferText acExportDelim, "Standard Output", _
        "External Report", "C:\Txtfiles\MyText.csv",-1

     

    Wednesday, November 17, 2010 9:38 AM
  • bvh_Be,  you're the man,  this definitely takes care of my issue with the headers not being exported.  Thanks much.

    Thursday, March 03, 2011 6:50 PM
  • Not quite. This doesn't work with Access 2003 or newer
    Thursday, March 10, 2011 10:35 PM
  • i am using access 2003, as mentioned "This doesn't work with Access 2003 or newer".Is there a way where i can print the header column in the csv file
    Report As Abuse
    Thursday, May 26, 2011 1:47 PM
  • I'm also using access 2003, and it works great.

    As mentioned, for the headers you need to add ,-1 in the end.

    EX: 

    DoCmd.TransferText acExportDelim, "ExportSpecificationNameAsYouSavedIt", "TableNameThatYouWantToExport", "C:\FolderName\FileName.csv",-1

    Good Luck.

    Tuesday, July 26, 2011 9:57 AM
  • Hi,

    Does anyone know a solution to this?

    I need to have quotation marks (" ") around all fields, including those that are empty.

    Thanks.

    Monday, December 26, 2011 5:41 PM
  • Try exporting using a make-table query.

    Create your select, but then alter it to read as follows:
    INTO [Text;FMT=Delimted;HDR=Yes;DATABASE=C:\<path you want to place file>;].[<output file name>#txt]
    FROM <source query/table>;

    When you run it, a schema.ini file will be created in the destination directory, along with an output file

    The default is CSV, and the quotes are included

    If you don't want the headings, you can alter the schema.ini file as follows:

    ColNameHeader=True <change this to false>

     

    Saturday, January 07, 2012 4:24 AM
  • In this case you can use the following command:

    DoCmd.TransferText acExportDelim, "Standard Output", _
        "External Report", "C:\Txtfiles\MyText.csv"

    When I run that I get the error "The text file specification "Standard Output" does not exist. You cannot import, export, or link using the specification." It looks to me like you first have to set up a text export specification named "Standard Output"; it doesn't seem to be built in.

    • Edited by GJL65 Tuesday, November 26, 2013 9:31 AM
    Tuesday, November 26, 2013 9:28 AM