Access VBA formating data from rows into a specified format

Question

In access I use sql to pull the following from a table in this format:

Invoice InvLine     Part    Qty   Value Customer    

123         1       ABC     1       1   HYK     
123         2       BBB     1       1   HYK     
123         3       EEE     1       1   HYK     
222         1       PPP     1       1   HYK     
222         2       HHH     1       1   HYK     
222         3       WWW     1       1   HYK

I need to export the above into the following format:

Invoice 123 Customer    HYK 

Inv Line    1   Part    ABC QTY 1   Value   1 
Inv Line    2   Part    BBB QTY 1   Value   1
Inv Line    3   Part    EEE QTY 1   Value   1

ValueTotal  3

csv is what i need to export it to with semi colons instead of commas :/ — phillipmai, Feb 05 '16 at 15:10
http://stackoverflow.com/questions/14700192/vba-docmd-transfertext-exporting-query-to-csv-with-user-defined-file-path (you can ignore the answers as the original question has a good illustration of the VBA you need). — Matt Hall, Feb 05 '16 at 15:15
This is not a suitable data format for a csv file. Is there really an application that wants to read a csv file in this format? Refer [XY-Problem](http://meta.stackexchange.com/a/66378/305412) — Andre, Feb 05 '16 at 15:22
Yes really a application tat wants to read a csv file in this format And thanks everyone i'm going to try a report how I want it and hopefully the file that I extract from this is able to be used by the application. — phillipmai, Feb 05 '16 at 15:53

score 0 · Answer 1 · answered Feb 05 '16 at 14:58

0

You will not be able to do what you're asking. The desired output is a combination of a transpose and standard dataset. You can print it in a report and export that report to a PDF file or something, but you're not going to get an export in the exact format you want.

I'd suggest creating a report. Group it by Invoice and Customer and drop those two fields into the report header. Then drop the Inv Line, Part, QTY and Value fields into the report details section, adding some text labels where appropriate.

Unfortunately my employer has disabled our ability to upload images or I'd show you exactly what I meant.

answered Feb 05 '16 at 14:58

Johnny Bones

8,786
7
52
117

I could write a report in the format I need but then my issue is getting that report exported into a csv file. – phillipmai Feb 05 '16 at 15:10
It won't happen. You're asking for the field names to be part of the data set. I mean, it's conceivable but would take an excessive amount of coding. You would have to read the table object, pull the field names into an array, write the first piece of data from your query into a new table, write one piece of the array into the table, write the next piece of data, etc... and do that in a loop so it runs through all the records in your dataset. And then you would export the table to a CSV. – Johnny Bones Feb 05 '16 at 15:50
I've created a report and exported it to a txt file but named as a csv using DoCmd.OutputTo acOutputReport, "report", acFormatTXT, o:\fillename.csv, False. It isn't exactly perfectly formatted when it comes out but it might do what I need. – phillipmai Feb 05 '16 at 17:20

score 0 · Accepted Answer · edited May 23 '17 at 12:30

To write this as CSV, I suggest a pure VBA solution.

To write text files: either use FileSystemObject or the old fashioned Open statement.

Open a recordset with your original data format.

Write the "header" (Invoice, Customer) from the first record.

Then loop the recordset, write one line per record, concatenating constant strings and recordset values.

S = "Inv Line " & RS!InvLine & ";Part " & RS!Part    ' etc.
oFile.WriteLine S

Keep a running sum of the values and write them to the last line ("ValueTotal").

Done.

Access VBA formating data from rows into a specified format

2 Answers2