Creating a csv from a query

[darinmc]

I am using access, I have managed to use OutpuTo, using a query, to a CSV file. The problem is that the data is not displayed in the way i need it in csv format or xls.

It appears in 1 line

Fields are... empno, expr1, hrs, empno, expr2, Overtimehrs etc..

eg: How it appears...
empno 1 hrs empno 2 Overtime1Hrs empno 3 Overtime2Hrs
i would like to export it in format

eg: How I would like it....
empno 1 hrs
empno 2 Overtime1Hrs
empno 3 Overtime2Hrs

Would I have to use a loop or carriage return somewhere in the query?
Please can you help?

 

[Crowley16]

not really the best way, but you can try to append a vbcrlf after each column's result, i.e.

select fld1 & vbcrlf as fld1nme from ...

a better way would be to make the columns in your query appear as rows, maybe with a crosstab of some sort...

--------------------
Procrastinate Now!

 

[darinmc]

Hi, thx but not really sure or understand that, far from an expert lol
At the moment I need to export my data in *.csv format from a query. I Could play with the data after the export, but rather it done once off.
I need some code, either in SQL or direct from a query.

It puts the data in the format...
EmpNo, 1, B/Hrs, EmpNo, 2, OT1Hrs, EmpNo, 3, OT2Hrs

I need it to go straight into the csv file as
EmpNo, 1, B/Hrs,
EmpNo, 2, OT1Hrs,
EmpNo, 3, OT2Hrs,

Is this easily Done? Appreciate ur help.
Darin

 

[Pampers]

For output to csv, you can use transfertext-method, like:

DoCmd.TransferText acExportDelim, "ContainerMovesEDI2", _
"qryContainerMovesEDI1", strInputFileName

Pampers
Keeping it simple can be complicated

 

[darinmc]

Hi pamper and Crowley16
I'm sorry, i sort of see where ur coming from BUT i dont really understand where the code would tell the data to drop to the beginning of a new line?
I.E. There are 9 fields that are presently displaying on 1 row, I need 1st three on top row, then second three on 2nd row, and last 3 on 3rd row, then it must move to the next record..

Would I be using an SQL statement?
Would it be possible to give a sample code, I.E a Query named - qryTest and in it Field1, field2 .... to Field9???

Sorry, i'm fairly new to the coding as u can see lol
Thanks D

 

[Pampers]

Hi darinmc,
I understand. You can put this:

SELECT [BookingID] & Chr(13) & Chr(10) & [BookingNo] AS Expr1
FROM tblBooking;

in your query to create rows like:

BookingID
BookingNo
BookingID
BookingNo
....

the & Chr(13) & Chr(10) are the same as vbcrlf.

If you run the query in your query design view, nothing will happen, but if you export it to a csv or txt-file, the linebreak will be inserted. I tested it with this code:

Dim strInputFileName As String
strInputFileName = "C:\Test3.txt"
DoCmd.TransferText acExportDelim, "SecondQuarterSpecs", "SecondQuarter3", strInputFileName.

SecondQuarterSpecs are export specifications; you must/can set those by selecting the query, rightclick, export, choose: csv.txt-format, advanced options. Takes a bit of time to understand how to get the Export Specs set.

SecondQuarter3 is the query you want to export

Pampers
Keeping it simple can be complicated