Solved

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

Posted on 2014-07-22
16
281 Views
Last Modified: 2014-07-27
Hello,

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      cpsiseneg1@gmail.com      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.
0
Comment
Question by:pcalabria
  • 7
  • 3
  • 3
  • +1
16 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40212962
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.
0
 

Author Comment

by:pcalabria
ID: 40213072
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.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 40213078
you have to create first the export Specification using the WIZARD

 steps to create the Export spec

 1.right click on the table
 2.select 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

or

 DoCmd.TransferText acExportFixed, "ExportSpecName", "TableName", "C:\myText.txt", True
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 40213081
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.

Jim.
0
 

Author Comment

by:pcalabria
ID: 40213954
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!
ReallyBadQuery.txt
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40214014
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"
0
 

Author Comment

by:pcalabria
ID: 40214071
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.
0
 
LVL 57
ID: 40214076
<<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
          Debug.Print prm.name
        Next prm

or

Debug.Print qdf.Parameters(x).Name

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

Jim.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 40214191
you missed "acExportDelim" or was it a typo in posting

DoCmd.TransferText acExportDelim, "CSV EXPORT SPECIFICATION" , "qryEbayUploadOnlyPhotos2", strExportFileName & ".csv", True
0
 

Author Comment

by:pcalabria
ID: 40214304
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...

Jim,
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... :-(
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 40214332
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.
0
 

Author Comment

by:pcalabria
ID: 40214358
Jim,
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.
0
 
LVL 57
ID: 40214378
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.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40214431
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.
0
 

Author Comment

by:pcalabria
ID: 40220478
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!
0
 

Author Closing Comment

by:pcalabria
ID: 40222762
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!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now