Export MS Access Query to CSV File

[chadh]

Hello, I'm having some troubles exporting a query to a csv file. I am able to use the doCMD.TransferText to output the query to the csv file, however, I cannot get it to use the Export Specifications and on some queries data is showing up in what looks like HEX. Below is an example:

SQL Statement for Invoice_Header Query:

SELECT [Forms]![Form1]![Invoice_ID] AS inv_id, [Forms]![Form1]![Trans_Date] AS tran_date, Sum(Item_Lot_Query.total_gross) AS total_gross
FROM Item_Lot_Query;

The SQL statement for the query Item_Lot_Query is:

SELECT Invoice_Raw_Data.Trans_Date, Invoice_Raw_Data.Item_Num, Mid$([Invoice_Raw_Data]![Item_Num],6,7) AS gpitem, GL_Codes.[gl dsitribution], Invoice_Raw_Data.Lot_Code, Invoice_Raw_Data.Shipped_Qty, IIf([Invoice_Raw_Data]![Tot_Gross Amt]=0,0.01,[Invoice_Raw_Data]![Tot_Gross Amt]) AS total_gross, Invoice_Raw_Data.[Order_Type Code]
FROM GL_Codes INNER JOIN Invoice_Raw_Data ON GL_Codes.Item = Invoice_Raw_Data.Item_Num
WHERE (((Invoice_Raw_Data.[Order_Type Code])<>"INTL") AND ((Invoice_Raw_Data.Charge_To)<>"") AND ((Invoice_Raw_Data.[Reason Code])="SALE"))
ORDER BY Mid$([Invoice_Raw_Data]![Item_Num],6,7), Invoice_Raw_Data.Lot_Code;

Invoice_Raw_Data is a table linked to a CSV file that I get from a vendor. The ultimate goal of this access db is to get the data into a format that can be accepted by another system.

When I run the queries with the From1 open and data entered, I get tables just how I want them.

When I run the following VB code I get the CSV file below

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
 
    Dim stExpName1 As String
    Dim strFile1 As String
 
    stExpName1 = "Invoice_Header_Query"
    strFile1 = "d:\GP_Project\Output\Invoice_header.csv"
    strExportSpec1 = "Export1"
 
    DoCmd.TransferText acExportDelim, TableName:=stExpName1, FileName:=strFile1, HasFieldNames:=True
 
Exit_Command11_Click:
    Exit Sub
 
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub

The From that is referenced is open when the command is run with the following information:
Inv_ID is Jun_08
Trans_date is 6/30/2008

The CSV file looks like this:
"inv_id","tran_date","total_gross"
4A 00 75 00 6E 00 5F 00 30 00 38 00 ,00 00 00 00 A0 59 E3 40 ,18129108.99

It should look like:
"inv_id","tran_date","total_gross'
"Jun_08","tran_date",18129108.99


My first question is: Why are my two parameters showing up in HEX(?) format? And how do I stop it.

Second question is, I want to set the text quallifier as {none} as can be done in the advanced export settings box. I did this and saved an export specification as Export1. When I run my export command with Export1 as the specification, I get an error:
"The data being exported does not match the format described in the Schema.ini file." I've read about this file a little bit in Microsofts KB, but I can't find it on my computer anywhere.

I would greatly appreciate any help with these problems.

Chad

[NeoPa]

My first question is: Why are my two parameters showing up in HEX(?) format? And how do I stop it.

That's two questions!

1) It is showing that way because it's exporting the correct data in Unicode format. If you lose the nulls and convert the Hex to character data you will notice the similarity to your expected output.

2) I'm not sure. Try looking around for settings in the database that specify how data is managed. It may even be a Windows setting. Sorry to be so hopeless on this, but I always ensure that I never use Unicode so I rarely come across this problem.

[NeoPa]

Second question is, I want to set the text quallifier as {none} as can be done in the advanced export settings box. I did this and saved an export specification as Export1. When I run my export command with Export1 as the specification, I get an error:
"The data being exported does not match the format described in the Schema.ini file." I've read about this file a little bit in Microsofts KB, but I can't find it on my computer anywhere.

I'm not sure about this one. It occurs to me though that it may be related to the Unicode problem.

Get that resolved first then have another look at this if it is still a problem then.

[chadh]

Thanks for the information. Not really sure how to proceed with the unicode issues.

One further bit of information I have found:
I have two queries that generate basically the same result set. The difference is that one of them has an expression to make one field negative. These two queries are then run as a union to generate a set that contains a positive and negative value. The purpose is that when the result csv is imported into another system, the negative values will be recorded in one section and positives in another. But, my point is that exporting the union query resullts in my parameters from the form displaying and exporting as text. If I export either of the component queries they have the unicode. Does this make any sense and could we use this information to fix the others?

Here are the SQL statements:
Item_Lot_Summary_InvoiceDistributions_Minus_Query:

SELECT DISTINCTROW [Forms]![Form1]![Invoice_ID] AS inv_id, Item_Lot_Query.[gl dsitribution] AS gl_account, Sum(-[total_gross]) AS dist_amt, IIf([Forms]![Form1]![Dist_Code_M]=1,1,2) AS dist_code
FROM Item_Lot_Query
GROUP BY [Forms]![Form1]![Invoice_ID], Item_Lot_Query.[gl dsitribution], IIf([Forms]![Form1]![Dist_Code_M]=1,1,2), Item_Lot_Query.gpitem, Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code
ORDER BY Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code, Item_Lot_Query.gpitem;

Item_Lot_Summary_InvoiceDistributions_PLUS_Query

SELECT DISTINCTROW [Forms]![Form1]![Invoice_ID] AS inv_id, IIf([Forms]![Form1]![Dist_Code_P]>0,"02-1210-000-0000",0) AS gl_account, Sum(Item_Lot_Query.total_gross) AS dist_amt, IIf([Forms]![Form1]![Dist_Code_P]=2,2,1) AS dist_code
FROM Item_Lot_Query
GROUP BY [Forms]![Form1]![Invoice_ID], IIf([Forms]![Form1]![Dist_Code_P]>0,"02-1210-000-0000",0), IIf([Forms]![Form1]![Dist_Code_P]=2,2,1), Item_Lot_Query.gpitem, Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code
ORDER BY Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code, Item_Lot_Query.gpitem;

Item_Lot_Summary_InvoiceDistributions_UNION_Query

SELECT Item_Lot_Summary_InvoiceDistributions_Minus_query.inv_id, Item_Lot_Summary_InvoiceDistributions_Minus_query.gl_account, Item_Lot_Summary_InvoiceDistributions_Minus_query.dist_amt, Item_Lot_Summary_InvoiceDistributions_Minus_query.dist_code
FROM Item_Lot_Summary_InvoiceDistributions_Minus_query
 
UNION ALL SELECT Item_Lot_Summary_InvoiceDistributions_PLUS_query.inv_id, Item_Lot_Summary_InvoiceDistributions_PLUS_query.gl_account, Item_Lot_Summary_InvoiceDistributions_PLUS_query.dist_amt, Item_Lot_Summary_InvoiceDistributions_PLUS_query.dist_code
FROM Item_Lot_Summary_InvoiceDistributions_PLUS_query;

Thanks again,
Chad

[NeoPa]

I don't think this is really related to WHY the data comes out as Unicode.

It would be easier if we knew exactly how you were going about your exporting, but I can say that there is an option in the manual export process (File / Export) that allows you to select the Code Page from a list.

However you are executing this, it would seem that one of the Unicode options has been selected.

[chadh]

Thanks for the reply. I'm executing this export from a command button in my form. I have tried doing a manual export on my queries, however, the manual export does not work. I get the error there are too few parameters. For whatever reason, when doing the manual export, the queries do NOT pull the parameters from the text boxes in my form. I can't explain this activity.

Using a query with no parameters, I went into the advanced export settings and created a new export specification. I made sure that the encoding was not set to unicode. I tried the "windows" encoding as well as US-ASCII. I also changed the text qualifier to {none}. When I run my vb now, it exports with out error, but the unicode problem is still there and all text fields have "" around them. This indicates to me that the command is not using the export spec I created. Is it possible to find where this export spec is saved and look at it to verify it is accurate? Or better yet, is is possible to put the export specifications directly into the export command?

I have tried running my database and commands on another computer just to rule out any issues with my setup and the same results were produced.

Here is the command I am running:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
 
    Dim stExpName1 As String
    Dim stExpName2 As String
    Dim stExpName3 As String
    Dim stExpName4 As String
    Dim strFile1 As String
    Dim strFile2 As String
    Dim strFile3 As String
    Dim strFile4 As String
 
    stExpName1 = "Invoice_Header_Query"
    stExpName2 = "Item_Lot_Summary_Invoice_Line3_Query"
    stExpName3 = "Item_Lot_Summary_INvoice_Lot_Query"
    stExpName4 = "Item_Lot_Summary_InvoiceDistributions_UNION_Query"
    strFile1 = "d:\GP_Project\Output\Invoice_header.csv"
    strFile2 = "d:\GP_Project\Output\Invoice_Line3.csv"
    strFile3 = "d:\GP_Project\Output\INvoice_Lot.csv"
    strFile4 = "d:\GP_Project\Output\Invoice Distributions.csv"
    strExportSpec1 = "Export1"
 
    DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName1, FileName:=strFile1, HasFieldNames:=True
    DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName2, FileName:=strFile2, HasFieldNames:=True
    DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName3, FileName:=strFile3, HasFieldNames:=True
    DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName4, FileName:=strFile4, HasFieldNames:=True
 
 
 
Exit_Command11_Click:
    Exit Sub
 
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub

[NeoPa]

I looked at this and it seems to me that you have done everything correctly.

As a quick test I set up something similar in my test database. I set up a Spec (Import/Export Specification) similar to yours and exported this manually first.

The results were (exactly as expected) :

TCode,Recycling,Tonnage
Mon01,0,100
Mon01,1,200
Mon02,1,50
Mon02,1,150
Mon02,0,300

[TCode] is a text field, [Recycling] is a Yes/No field and [Tonnage] is numeric (See attached JPEG - Click it to see clearer copy).

So far so good.

[NeoPa]

Having got that far, I ran the following code to test that the same results would be produced when doing it from code :

Call DoCmd.TransferText(TransferType:=acExportDelim, _
                        SpecificationName:="qryTonnage Export Spec", _
                        TableName:="qryTonnage", _
                        FileName:="C:\Temp\qryTonnage.Csv", _
                        HasFieldNames:=True)

I'm afraid when I did this I found that it worked perfectly. The resultant file was exactly the same as the first one, and 100% as it should be using those parameters.

[NeoPa]

To look at (Best not to fiddle in this area) what is stored for your specifications you will need to follow these steps :

1. Select Tools / Options / View (tab) then check System objects.
2. [MSysIMEXSpecs] stores information about the overall specifications in your database.
3. [MSysIMEXColumns] stores information about each column controlled within the specifications in your database.

[chadh]

Thanks for your help. I think some of my problem lies with trying to insert the text parameters from the form into the query. My guess is I'm trying to do something Access really doesn't support. Since, I'm trying to add these parameters into the query, I cannot use the import/export wizard on the queries I actually want to export because when I run the wizard I get prompted "Too few parameters. Expected (2)." Normally, when I run the query and have the form open with the parameters entered, the queries open fine. And when I export them with a vb command, the export works, aside from the formatting issues, of course.

So, I've had to use a query without any parameters to create the export spec. Could this be the source of the problem? And if it is, how does one make an export spec on a query that causes errors in the wizard?

thanks again,
Chad

[NeoPa]

Is the form open when you run the Export?

I guess it must be but can you confirm.

[chadh]

Yes, that's the strange thing. The form is open with data entered. When I run the query and get a data table (with the parameters showing as expected), so the references to the form must be correct. My only guess is perhaps this is a bug in Access. I'm running Access 2003 SP3.

Chad

[NeoPa]

If you use the form values as selection criteria then I doubt you would have a problem. Can you confirm you are trying to export a value which comes from the form?

As this would leave the data type undefined I can see why the wizard might have a problem with it. If this is the problem we can look at ways of getting around it.

[chadh]

I have some new information. In the query, I wrapped the parameters inside the cstr() function and they exported as text and not the unicode, which in fact was binary. The purpose of these form values is not selection criteria. I need a way of putting user-defined text with each record. The original data set is a linked table that will change frequently. This information is not included in the data set, it is the same for each record. For example, the data reflects invoice line items for an entire month. It is being compiled and will be entered in bulk into MS Great plains which will treat the import as one large invoice. The user is to supply an invoice id that needs to be in each imported record. My limited Access knowledge lead me to putting these values into the query as parameters. If there is a better way to do this, I'm all for it.

I have not tried manually exporting the query after adding the cstr() functions. Ran out of time last night.

Chad

[NeoPa]

If the form control values are actual values to export, then it is probably only the Wizard part which has a problem with it.

Try it with values which are already defined and see if you can get the wizard to complete. Remember, it is only setting up the saved Export Spec that you need the wizard for. Once that has been prepared you can then try it in code with the form control values again.

[chadh]

Just wanted to let you know the problem has been resolved. I hard-coded values into the queries and was able to export them using the wizard and create the Export Specs. That combined with using the cstr() on the form values. Has given me exactly what I want.

No I just need to figure out how to make line numbers on two of the queries. But I'll start another thread for that one.

Thanks again for your help,
Chad

[NeoPa]

No worries. Pop a link in here when you've done it and I'll have a look for you.

I expect you'll be disappointed though. SQL doesn't do relative records I'm afraid.

[chadh]

Creating Line or Row numbers on query results