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.TransferT ext 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_Dat a 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_gr oss"
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_gr oss'
"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]

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