By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Export MS Access Query to CSV File

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Forms]![Form1]![Invoice_ID] AS inv_id, [Forms]![Form1]![Trans_Date] AS tran_date, Sum(Item_Lot_Query.total_gross) AS total_gross
  2. FROM Item_Lot_Query;
The SQL statement for the query Item_Lot_Query is:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM GL_Codes INNER JOIN Invoice_Raw_Data ON GL_Codes.Item = Invoice_Raw_Data.Item_Num
  3. WHERE (((Invoice_Raw_Data.[Order_Type Code])<>"INTL") AND ((Invoice_Raw_Data.Charge_To)<>"") AND ((Invoice_Raw_Data.[Reason Code])="SALE"))
  4. 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2. On Error GoTo Err_Command11_Click
  3.  
  4.     Dim stExpName1 As String
  5.     Dim strFile1 As String
  6.  
  7.     stExpName1 = "Invoice_Header_Query"
  8.     strFile1 = "d:\GP_Project\Output\Invoice_header.csv"
  9.     strExportSpec1 = "Export1"
  10.  
  11.     DoCmd.TransferText acExportDelim, TableName:=stExpName1, FileName:=strFile1, HasFieldNames:=True
  12.  
  13. Exit_Command11_Click:
  14.     Exit Sub
  15.  
  16. Err_Command11_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_Command11_Click
  19.  
  20. 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
Sep 22 '08 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Sep 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Sep 23 '08 #3

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Item_Lot_Query
  3. 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
  4. ORDER BY Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code, Item_Lot_Query.gpitem;
Item_Lot_Summary_InvoiceDistributions_PLUS_Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Item_Lot_Query
  3. 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
  4. ORDER BY Item_Lot_Query.[Order_Type Code], Item_Lot_Query.Lot_Code, Item_Lot_Query.gpitem;
Item_Lot_Summary_InvoiceDistributions_UNION_Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Item_Lot_Summary_InvoiceDistributions_Minus_query
  3.  
  4. 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
  5. FROM Item_Lot_Summary_InvoiceDistributions_PLUS_query;
Thanks again,
Chad
Sep 25 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
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.
Sep 26 '08 #5

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2. On Error GoTo Err_Command11_Click
  3.  
  4.     Dim stExpName1 As String
  5.     Dim stExpName2 As String
  6.     Dim stExpName3 As String
  7.     Dim stExpName4 As String
  8.     Dim strFile1 As String
  9.     Dim strFile2 As String
  10.     Dim strFile3 As String
  11.     Dim strFile4 As String
  12.  
  13.     stExpName1 = "Invoice_Header_Query"
  14.     stExpName2 = "Item_Lot_Summary_Invoice_Line3_Query"
  15.     stExpName3 = "Item_Lot_Summary_INvoice_Lot_Query"
  16.     stExpName4 = "Item_Lot_Summary_InvoiceDistributions_UNION_Query"
  17.     strFile1 = "d:\GP_Project\Output\Invoice_header.csv"
  18.     strFile2 = "d:\GP_Project\Output\Invoice_Line3.csv"
  19.     strFile3 = "d:\GP_Project\Output\INvoice_Lot.csv"
  20.     strFile4 = "d:\GP_Project\Output\Invoice Distributions.csv"
  21.     strExportSpec1 = "Export1"
  22.  
  23.     DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName1, FileName:=strFile1, HasFieldNames:=True
  24.     DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName2, FileName:=strFile2, HasFieldNames:=True
  25.     DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName3, FileName:=strFile3, HasFieldNames:=True
  26.     DoCmd.TransferText acExportDelim, SpecificationName:=strExportSpec, TableName:=stExpName4, FileName:=strFile4, HasFieldNames:=True
  27.  
  28.  
  29.  
  30. Exit_Command11_Click:
  31.     Exit Sub
  32.  
  33. Err_Command11_Click:
  34.     MsgBox Err.Description
  35.     Resume Exit_Command11_Click
  36.  
  37. End Sub
Sep 29 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
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) :
Expand|Select|Wrap|Line Numbers
  1. TCode,Recycling,Tonnage
  2. Mon01,0,100
  3. Mon01,1,200
  4. Mon02,1,50
  5. Mon02,1,150
  6. 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.
Attached Images
File Type: jpg ExpSpec.Jpg (28.7 KB, 578 views)
Sep 29 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
Having got that far, I ran the following code to test that the same results would be produced when doing it from code :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferText(TransferType:=acExportDelim, _
  2.                         SpecificationName:="qryTonnage Export Spec", _
  3.                         TableName:="qryTonnage", _
  4.                         FileName:="C:\Temp\qryTonnage.Csv", _
  5.                         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.
Sep 29 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Sep 29 '08 #9

P: 12
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
Oct 2 '08 #10

NeoPa
Expert Mod 15k+
P: 31,494
Is the form open when you run the Export?

I guess it must be but can you confirm.
Oct 3 '08 #11

P: 12
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
Oct 6 '08 #12

NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 7 '08 #13

P: 12
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
Oct 7 '08 #14

NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 7 '08 #15

P: 12
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
Oct 8 '08 #16

NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 8 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.