Need code to output a query as a comma delimed CSV file


I am working on an application that requires I programmatically create a CSV file from a query that I have written.

I am using Access 2K on Windows XP Pro workstations.

I have tried the following statement, which does not work properly.

DoCmd.TransferText acExportDelim, , "qryEbayUploadOnlyPhotos2", strExportFileName & ".csv", True

Can anyone help?

Here is an example of what happens when I use the above line:

The first line of data in my spreadsheet looks like this:

VerifyAdd      58169      1      Auction      6.3      8.19      7      0      NJ, USA      1      ITT Semiconductor Corporation      YT1035520FP LFS      USPSFirstClass      0      1      Flat      3      0124-000003      ReturnsAccepted      Days_14      Buyer      MoneyBack

The first row of the CSV file looks like this:

56 00 65 00 72 00 69 00 66 00 79 00 41 00 64 00 64 00  41 00 75 00 63 00 74 00 69 00 6F 00 6E 00       6.3      8.19      37 00       30 00       4E 00 4A 00 2C 00 20 00 55 00 53 00 41 00       31 00       63 00 70 00 73 00 69 00 73 00 65 00 6E 00 65 00 67 00 31 00 40 00 67 00 6D 00 61 00 69 00 6C 00 2E 00 63 00 6F 00 6D 00       ITT Semiconductor Corporation      YT1035520FP LFS      55 00 53 00 50 00 53 00 46 00 69 00 72 00 73 00 74 00 43 00 6C 00 61 00 73 00 73 00       30 00       31 00       46 00 6C 00 61 00 74 00       33 00       0124-000003      52 00 65 00 74 00 75 00 72 00 6E 00 73 00 41 00 63 00 63 00 65 00 70 00 74 00 65 00 64 00       44 00 61 00 79 00 73 00 5F 00 31 00 34 00       42 00 75 00 79 00 65 00 72 00       4D 00 6F 00 6E 00 65 00 79 00 42 00 61 00 63 00 6B 00

Note:  I botched some of this code when posting, but you'll get the idea.
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
On the transfertext command, note the second argument is the specification, which you can create in Access and then save.  This allows you to control the format to a certain degree.  If you leave the argument blank, you get a default for the .CSV file.

 Next step up is to use VBA and write the file directly.  Then your in complete control, line by line.

Dale FyeCommented:
I notice that you have a term:  NJ, USA

in the first line of your data.  Is that one field, or two?  The embedded comma, without adding quotes around your strings will result in that being interpreted as two fields in the CSV file.
pcalabriaAuthor Commented:
Yes, the comma does appear in column M,
however, there must be a way of creating CSV files that is comma proof.

For example, if I use DoCmd.TransferDatabase I am able to export as an Excel file without problem.
I can then open the file in Excel and save as comma delimited CSV, without trouble, and without problems with fields that contain commas.

There must be a "smarter" utility/method for Access!
The CSV file creating needs to be programmatic.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
you have to create first the export Specification using the WIZARD

 steps to create the Export spec

 1.right click on the table export > Text file
    click on Browse and locate the destination folder
 3. (you can accept the proposed name or change it)
 click Save, then click OK
 4. In the export text wizard select the type (Delim / Fixed width)
 5. Follow the wizard, before clicking on Finish
      5a .Click Advanced
 6. In the Export Specification dialog box Field Information List, correct any descrepancies

 7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myText.txt", True


 DoCmd.TransferText acExportFixed, "ExportSpecName", "TableName", "C:\myText.txt", True
pcalabriaAuthor Commented:
This one has me really confused!
Thanks for your help, but I don't have a solution yet.

The Export does not work.   I'm getting the following message:
Too Few Parameters.
Expecting 16
When I click the SAVE button while following Reys directions.

I tried exporting a different query, and everything worked as described.

There seems to be something Access does not like about exporting my query.  Indeed, its an awful query.  

Hey experts, let me warn you in advance, this is the most ugly query I have ever written.  It breaks every rule I can think of, but it does work property as long as I don't use the Access Query Builder to try to change anything!

I had to create the query with code.  Please don't blame me... I only wrote the query, someone else wrote the file specification!

Jim, I never thought about using VBA to create the file, and it might take a bit for me to generate its code, but I agree, this would be a definite option!

Regardless, this error message has me curious, I really want to know why this thing doesn't work!
Rey Obrero (Capricorn1)Commented:
are you saving the SQL string as a query?

to create a query using  strSQLtext

dim qd as dao.querydef, db as dao.database
set db=currentdb

set qd=db.createquerydef("MyQ", strSQLtext)

after you run the codes

then try to export the query "MyQ"
pcalabriaAuthor Commented:
Yes, I have already saved the SQL string as a query and that part seems to work correctly.
I can then execute the query in code, as desired (DoCmd.OpenQuery "qryEbayUploadOnlyPhotos2")
Everything seems fine, unless I open the query in design view, in which case I can not close the query, and must Ctl-Alt-Del out (ugly?).

The problem, as of the writing of this question, was that the Docmd.TransferText line, as listed in my original post, does not create the correct output.

Since the original post, I have just screwed something else up...
After reading Jims post, I decided that I would use a different query (one that does not cause any errors), to create a query export specification.

I clicked a query called Query21 and followed the process you (Rey) described earlier.  When I got to the advanced tab, I clicked it, and created and saved an Export Specification that I called CSV Export Specification.  I saved the export specification, then went back to my original code.

I then when back to the original code:

DoCmd.TransferText acExportDelim, , "qryEbayUploadOnlyPhotos2", strExportFileName & ".csv", True

and changed it to:
DoCmd.TransferText "CSV EXPORT SPECIFICATION" , "qryEbayUploadOnlyPhotos2", strExportFileName & ".csv", True

Which give a Type Mismatch.
I tried replacing the double quotes with single quotes and with brackets an the query does not work, with a variety of errors.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Too Few Parameters.>>

 When you execute a query in code, all the work is left up to you.

 When you execute a query through Access, it will prompt you for any un-resolved reference (something it sees that it can't get a value for).

 When your working in code, your expected to provide the value for each reference or you will get "Too Few Parameters."

So that means either:

1. You've mis-spelled a field or table name

2. You've referred to something like a form control, which won't get resolved because Access is not handling the query execution (VBA is) and you need to provide that value.

 The querydef has a parameters collection, which you can loop through.  If the parameter is a control reference, you can cheat and do this:

  Dim prm as Parameter

        Set dbs = CurrentDb
        Set qdef = db.QueryDefs("Qry_OverdueAppointments")
       For Each prm in qdf.Parameters
          prm.Value = Eval(prm.Name)
        Next prm

 and use Eval().   Eval() forces VBA to resolve the parameter name and as long as the form is open, it can get the value.

 You can see what parameters it's looking for by doing:

       For Each prm in qdf.Parameters
        Next prm


Debug.Print qdf.Parameters(x).Name

 Once all parameters are resolved (have a value), then the query will execute without error.

Rey Obrero (Capricorn1)Commented:
you missed "acExportDelim" or was it a typo in posting

DoCmd.TransferText acExportDelim, "CSV EXPORT SPECIFICATION" , "qryEbayUploadOnlyPhotos2", strExportFileName & ".csv", True
pcalabriaAuthor Commented:
Ugh.. Still not working..
Rey, I did forget the acExportDelim
After fixing it, I get
I attached a doc file that shows the code and error.  Basically, it will not work because the data does not match the schema file... which makes sense, because I used a different query to create the schema file... dah...

I'm working on your code now... throughout the years you have taught me so much.  My continued thanks...

BTW... when I tried to create an Export Specification again, something that worked an hour ago,
I get a message that Access can't find the Wizard, or there is an incorrect setting in the Window Registry.  Re-Run MS Access to reisnstall the wizard.

I'm not sure that I have access to the original disk any more... especially since I'm using RDP to get into the system.
Not sure what else is broken... :-(
Dale FyeCommented:
Re: "Access can't find the Wizard, or there is an incorrect setting in the Window Registry.  Re-Run MS Access to reisnstall the wizard."

You may be able to simply repair your installation of Office, rather than reinstall.  What version of Office are you using?  The only time I've ever encountered that error is on a clients machind which has 64 bit office installed.
pcalabriaAuthor Commented:
Tried your code and I am confused at the results. A screen shot is attached.
I had to fix what I believe are a couple of types to get the code to work... changed qdf to qdef, and the code runs and prints to the debug window as expected.

When the code hits the line (in which I changed qdf to qdef)

Debug.Print qdf.Parameters(x).Name

I get an item not found in this collection message.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need to change the "x" to a number 0,1,2,3.

The parameters collection is 0 based, and you will have one for each parameter.  Your error message mention 16, so you will have 0 - 15 to look at.

Dale FyeCommented:
Was there a screenshot attached to this post?  I'm using FireFox and don't see one, which makes it the third time today I've looked at a post that says there was either an image or file attached, that I could not see on my screen.
pcalabriaAuthor Commented:
Dale...I did attach a screen shot, but don't see it now. I don't know if they restrict the type of files we attach??? It was inside an MS Word Doc.

Something is definitely messed up with Access.  I've been try to run the repair utility, but I'm hundreds of miles away and the Access 2K disc is no where nearby.

Jim, couldn't figure out anything at all.  I'll spend more time on this during the weekend if I don't find another solution.  I tried deleting everything in the query one by one, but always received the error.

I'm beginning to think that using vba  might be the thing to do.  Does anyone have any code to create a CSV file from a Querry?  It sounds like it might be straight forward, but that though always seems to get me in trouble!
pcalabriaAuthor Commented:
Looks like I have a problem with the wizard in my install of Access 2K.  As I do not have access to the installation disc as I'm in a remote location, I will need to write code in vba.

I've decided to take Jim's advice and do this, and will open a new question to get the help I need.

Thanks everyone!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.