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
17 19908 NeoPa 32,570
Recognized Expert Moderator MVP
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 32,570
Recognized Expert Moderator MVP
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.
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_Summar y_InvoiceDistri butions_Minus_Q uery: - 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_Summar y_InvoiceDistri butions_PLUS_Qu ery - 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_Summar y_InvoiceDistri butions_UNION_Q uery - 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 32,570
Recognized Expert Moderator MVP
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.
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 32,570
Recognized Expert Moderator MVP
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 32,570
Recognized Expert Moderator MVP
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 32,570
Recognized Expert Moderator MVP
To look at (Best not to fiddle in this area) what is stored for your specifications you will need to follow these steps : - Select Tools / Options / View (tab) then check System objects.
- [MSysIMEXSpecs] stores information about the overall specifications in your database.
- [MSysIMEXColumns] stores information about each column controlled within the specifications in your database.
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: M O J O |
last post by:
Hi,
(I'm new to XML)
I can export data from Access as an XML file (with schema integrated in
the XML file). I can read it into a DotNet DataSet, but the schema is
not correct that is - I do not get the right columns.
Is there some kinda trick to convert the XML to a
DotNet-DataSet-readbable file??
|
by: Oliver Stratmann |
last post by:
Hello all,
is there another way to export data to a csv-file (comma-separated format)?
We tried the EXPORT-command and got the following error
"
SQL1325N The remote database environment does not support the command
or one of the command options.
Explanation: An attempt has been made to issue a DB2 workstation database
specific command or command option against a host database through DB2
|
by: Keon |
last post by:
Hoi,
I'm using a database with alot of records in 1 table (more than 3000). If i
want to export this table to excel i only get it till record 2385. Do
someone know how i can solve this probleme?
To export my tables i use folowing code:
StTotaal = stLocatieName & "\Archief\" & Year(Date) & Month(Date) &
Day(Date) & "_Gegevens.xls" 'location and name where the table must be
saved
|
by: GAVO-UK |
last post by:
Using Ms Access 2003
I am trying to export a query to a delimited txt file containing some
CURRENCY fields, which I want to export without the currently symbol
and with five decimals. I have tried everything to get the currency
fields with the format I want after exporting but!! NO LUCK!!!
This is what I have tried.
In the source table the fields are formatted with a DataType=
|
by: Simon |
last post by:
Dear reader,
With the export command you can export a query to Excel.
By activate this command a form pop's up with the following text:
| |
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am trying to export to Excel using a command in an Access Form.
RowID strFY AccountID CostElementWBS
1 2008 1 7
2 2008 1 7
I want to...
|
by: cut123 |
last post by:
I have a query that I would like to export to different Excel documents based on the product. For each type of product I want all pertinent data to export to a different Excel document. There will be over 65,000 rows of data so I can't export it to Excel and break it out in Excel. I would rather not create a separate query for each one, any suggestions?
Thanks for your help
cut123
|
by: jim |
last post by:
Hi All,
I need to export a query to a text file but i need each field delimited
by a new line. Cant seem to find a way to do it.
any ideas..
thanks
jim
|
by: ezra |
last post by:
I have an access report based on a bunch of queries that does a quarterly summary of some research related stats. Now my boss wants this report to export into excel based on a specific format she has developed. This formatted excel sheet also needs to keep all previous quarters information stored in it and just add subsequent quarters. I don't know where to start!
|
by: Amin Bardai |
last post by:
How about this:
I want to export results of an MS-Access query into an existing XLS template using TransferSpreadsheet method?
I can create a new XLS file but I just want to append records from my query into an existing XLS template.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |