Link to home
Start Free TrialLog in
Avatar of Jose C
Jose CFlag for United States of America

asked on

Access: Export to CSV with Query Data

I have two queries called:  
   query #1:  Charges_header
   query #2:  Charges_body

I need to do export these two queries into ONE text CSV file.

For example:

Line1:    [Charges_header]!batchid     ,     [Charges_header]!Count     ,,,,,,,,
Line2:    ,,,,,,,,,
Line3:    Name     ,     Number     ,     Exp     ,     Amt     ,,,,
Line4:   [Charges_body]!Name     ,     [Charges_body]!Number     ,     [Charges_body]!Exp ,,,,,
Line5:   [Charges_body]!Name     ,     [Charges_body]!Number     ,     [Charges_body]!Exp ,,,,,
etc. etc. for as many records as there are in [Charges_body]

From what I understand, this is done in an event in a form by using the onclick in a button but i don't know how to make the csv so your guidance is appreciated.
Avatar of jerryb30
jerryb30
Flag of United States of America image

Can you post the query syntax, as well as sample results from them? (One line per query is OK.)
Avatar of Jose C

ASKER

jerryb30, thank you for your reply.

by query syntax do you mean the sql of the query?  the sql would be kind of bulky but i can show you what the results of each query and then the results within the csv should look like.

Charges_header results:
column name:            bachid                          count
query result:              batchid=20121106       8

charges_body results:
column name:            Name                           Number            Exp           Amt
query result:              John Smith                   1234567890      1113        123.40
                                 etc. through 8 records as reflected in the header count result

hope that helps!
Yes, that helps. So, query 1 is like a header for the file
and then there is the header data for the second query
then the results of query2
I am curious about the extra commas, although I suspect that they are for the further use of the csv in another process.
What is the point of the commas not associated with data elements? At the end of the query1 and query2 lines, as well as lines 2 and 3?
Avatar of Jose C

ASKER

jerryb,

yes, query1 is the header for the csv and it uses data from query2 and then there is an actual header followed by the data from query2.

the extra commas are to complete the number of fields required in the csv.  i may have miscounted in my example when adding commas.  but in effect, this csv is sent off to someone for processing and they require the header, including the extra 8 commas (for a total of 10 fields - 2 data fields of batchid and count plus 8 more commas)

but in this example, there are 10 fields so even the blank line2 has to have 10 commas and then the data fields have commas for any blank data within the records.
In a csv, 10 fields = 9 commas.
OK, I will work on it.
ASKER CERTIFIED SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jose C

ASKER

that's the data!  thanks and i'll wait for the code. (obviously the Name and Number and Exp would change in more of the records but this works for testing)

the final csv actually has about 70 fields but if i can figure out how to make a small one i can extrapolate to make the production CSV we seek.

thanks for your help!
Wait for the code?
Avatar of Jose C

ASKER

sorry, i hadn't seen the function after your example.  i'll give it a try.
Avatar of Jose C

ASKER

when i press the button i created nothing happens but before i even get to that i'm having trouble with one of the vstrings.

how large can a vstring be?

i'm trying to create one that looks like the following but it doesn't appear to fit on one line:

vstring="merchantReferenceCode*,purchaseTotals_currency*,purchaseTotals_grandTotalAmount*,billTo_firstName*,billTo_lastName*,billTo_customerID,billTo_email*,billTo_street1*,billTo_street2,billTo_city*,billTo_state*,billTo_country*,billTo_postalCode*,card_accountNumber*,billTo_phoneNumber,card_expirationMonth*,card_expirationYear*,card_cardType*,card_cvIndicator,card_cvNumber,ccAuthService_commerceIndicator,comments,merchantDefinedData_field1,merchantDefinedData_field2,merchantDefinedData_field3,merchantDefinedData_field4,shipTo_firstName,shipTo_lastName,shipTo_street1,shipTo_street2,shipTo_city,shipTo_state,shipTo_country,shipTo_postalCode,otherTax_alternateTaxAmount,otherTax_alternateTaxIndicator,otherTax_alternateTaxID,invoiceHeader_amexDataTAA1,invoiceHeader_amexDataTAA2,invoiceHeader_amexDataTAA3,invoiceHeader_amexDataTAA4,purchaseTotals_dutyAmount,purchaseTotals_freightAmount,invoiceHeader_userPO,otherTax_localTaxAmount,otherTax_localTaxIndicator,invoiceHeader_merchantVATRegistrationNumber,otherTax_nationalTaxAmount,otherTax_nationalTaxIndicator,purchaseTotals_discountAmount,invoiceHeader_purchaserCode,invoiceHeader_purchaserOrderDate,invoiceHeader_purchaserVATRegistrationNumber,ccCaptureService_purchasingLevel,shipFrom_postalCode,invoiceHeader_summaryCommodityCode,invoiceHeader_supplierOrderReference,invoiceHeader_taxable,invoiceHeader_vatInvoiceReferenceNumber,otherTax_vatTaxAmount,otherTax_vatTaxRate,item_0_alternateTaxAmount,item_0_alternateTaxID,item_0_alternateTaxRate,item_0_alternateTaxTypeApplied,item_0_alternateTaxType,item_0_unitPrice,item_0_commodityCode,item_0_discountAmount,item_0_discountIndicator,item_0_discountRate,item_0_grossNetIndicator,item_0_localTax,item_0_productSKU,item_0_nationalTax,item_0_productCode,item_0_productName,item_0_quantity,item_0_taxAmount,item_0_taxRate,item_0_taxTypeApplied,item_0_totalAmount,item_0_unitOfMeasure,item_0_vatRate,item_1_alternateTaxAmount,item_1_alternateTaxID,item_1_alternateTaxRate,item_1_alternateTaxTypeApplied,item_1_alternateTaxType,item_1_unitPrice,item_1_commodityCode,item_1_discountAmount,item_1_discountIndicator,item_1_discountRate,item_1_grossNetIndicator,item_1_localTax,item_1_productSKU,item_1_nationalTax,item_1_productCode,item_1_productName,item_1_quantity,item_1_taxAmount,item_1_taxRate,item_1_taxTypeApplied,item_1_totalAmount,item_1_unitOfMeasure,item_1_vatRate,item_2_alternateTaxAmount,item_2_alternateTaxID,item_2_alternateTaxRate,item_2_alternateTaxTypeApplied,item_2_alternateTaxType,item_2_unitPrice,item_2_commodityCode,item_2_discountAmount,item_2_discountIndicator,item_2_discountRate,item_2_grossNetIndicator,item_2_localTax,item_2_productSKU,item_2_nationalTax,item_2_productCode,item_2_productName,item_2_quantity,item_2_taxAmount,item_2_taxRate,item_2_taxTypeApplied,item_2_totalAmount,item_2_unitOfMeasure,item_2_vatRate,item_3_alternateTaxAmount,item_3_alternateTaxID,item_3_alternateTaxRate,item_3_alternateTaxTypeApplied,item_3_alternateTaxType,item_3_unitPrice,item_3_commodityCode,item_3_discountAmount,item_3_discountIndicator,item_3_discountRate,item_3_grossNetIndicator,item_3_localTax,item_3_productSKU,item_3_nationalTax,item_3_productCode,item_3_productName,item_3_quantity,item_3_taxAmount,item_3_taxRate,item_3_taxTypeApplied,item_3_totalAmount,item_3_unitOfMeasure,item_3_vatRate,item_4_alternateTaxAmount,item_4_alternateTaxID,item_4_alternateTaxRate,item_4_alternateTaxTypeApplied,item_4_alternateTaxType,item_4_unitPrice,item_4_commodityCode,item_4_discountAmount,item_4_discountIndicator,item_4_discountRate,item_4_grossNetIndicator,item_4_localTax,item_4_productSKU,item_4_nationalTax,item_4_productCode,item_4_productName,item_4_quantity,item_4_taxAmount,item_4_taxRate,item_4_taxTypeApplied,item_4_totalAmount,item_4_unitOfMeasure,item_4_vatRate,item_5_alternateTaxAmount,item_5_alternateTaxID,item_5_alternateTaxRate,item_5_alternateTaxTypeApplied,item_5_alternateTaxType,item_5_unitPrice,item_5_commodityCode,item_5_discountAmount,item_5_discountIndicator,item_5_discountRate,item_5_grossNetIndicator,item_5_localTax,item_5_productSKU,item_5_nationalTax,item_5_productCode,item_5_productName,item_5_quantity,item_5_taxAmount,item_5_taxRate,item_5_taxTypeApplied,item_5_totalAmount,item_5_unitOfMeasure,item_5_vatRate,item_6_alternateTaxAmount,item_6_alternateTaxID,item_6_alternateTaxRate,item_6_alternateTaxTypeApplied,item_6_alternateTaxType,item_6_unitPrice,item_6_commodityCode,item_6_discountAmount,item_6_discountIndicator,item_6_discountRate,item_6_grossNetIndicator,item_6_localTax,item_6_productSKU,item_6_nationalTax,item_6_productCode,item_6_productName,item_6_quantity,item_6_taxAmount,item_6_taxRate,item_6_taxTypeApplied,item_6_totalAmount,item_6_unitOfMeasure,item_6_vatRate,item_7_alternateTaxAmount,item_7_alternateTaxID,item_7_alternateTaxRate,item_7_alternateTaxTypeApplied,item_7_alternateTaxType,item_7_unitPrice,item_7_commodityCode,item_7_discountAmount,item_7_discountIndicator,item_7_discountRate,item_7_grossNetIndicator,item_7_localTax,item_7_productSKU,item_7_nationalTax,item_7_productCode,item_7_productName,item_7_quantity,item_7_taxAmount,item_7_taxRate,item_7_taxTypeApplied,item_7_totalAmount,item_7_unitOfMeasure,item_7_vatRate,item_8_alternateTaxAmount,item_8_alternateTaxID,item_8_alternateTaxRate,item_8_alternateTaxTypeApplied,item_8_alternateTaxType,item_8_unitPrice,item_8_commodityCode,item_8_discountAmount,item_8_discountIndicator,item_8_discountRate,item_8_grossNetIndicator,item_8_localTax,item_8_productSKU,item_8_nationalTax,item_8_productCode,item_8_productName,item_8_quantity,item_8_taxAmount,item_8_taxRate,item_8_taxTypeApplied,item_8_totalAmount,item_8_unitOfMeasure,item_8_vatRate,item_9_alternateTaxAmount,item_9_alternateTaxID,item_9_alternateTaxRate,item_9_alternateTaxTypeApplied,item_9_alternateTaxType,item_9_unitPrice,item_9_commodityCode,item_9_discountAmount,item_9_discountIndicator,item_9_discountRate,item_9_grossNetIndicator,item_9_localTax,item_9_productSKU,item_9_nationalTax,item_9_productCode,item_9_productName,item_9_quantity,item_9_taxAmount,item_9_taxRate,item_9_taxTypeApplied,item_9_totalAmount,item_9_unitOfMeasure,item_9_vatRate"
vstring should be the combination of the field values (rs2!fieldname1 & "," and rs2.fieldname2 & ","), etc. so, without the full knowledge of the query/table fields, it is difficult replicate. It is possible to build the vstring by going through each field, based on the number of fields in the recordset.
Can you post a db with the results of query2 (as a table) ? One line? Dummy data is OK.
There is a limit to building a string in a single line in the vba editor, but a couple of ways to concatenate multiple lines.
Avatar of Jose C

ASKER

jerryb,

you've got me on the right track so i'll close out this question.  i'll create a new question once i have the code formatted to meet the output i need and ask for help in getting the button to work as well as the vstring to be longer

thanks!
Sounds good. Thanks.