Export 2 queries to 1 csv (columns not the same) (1 Viewer)

[anski]

I need to export 2 queries to 1 csv file. The 2 queries do not have the same number of columns. I cannot use Union because this requires I have the same column numbers. I cannot make alias columns or null columns because the resulting exported file will have commas to signify a blank field. The result should be like this:

Hdr John 2 02/14/1989 Manager NY (from query 1)
Dtl Eric 06/04/1989 John (from query 2)
Dtl Lisa 11/15/1995 John (from query 2)

 

[MarkK]

Create a Scripting.FileSystemObject and execute its CreateTextFile method, which returns a Scripting.TextStream object. Then use the WriteLine method of the TextStream object to write individual lines to the file.
hth
Mark

 

[anski]

thanks. i am new to filesystem. i assume that the "writing" of lines involves additional codes to end up with a csv file. it is not like you copy text from one file and paste to another file? i use the transfertext to export to a delimited csv file. there is no coding or formatting for me to do on the source file. (i hope you understand my concern.)

can you post a sample code that i can base mine on?

 

[isladogs]

You can also create a union query from your two queries
To do so you add one or more dummy fields to each query as necessary to ensure you have the same number of fields with the same datatypes.
For example, add an empty number field as the third field in query 2 and set the value to 0
You also need to manage the final two fields from query 1 and the last field from query 2.

Note that the field names in the first part of the union are used whilst those in the second part are ignored.

It's easy enough to do but whether it's better than using file system object for the task is another matter.

 

[anski]

thanks ridders. i really wish i can use a union query because i find union query easy but , like what i said in my first post, i cannot use dummy fields because when i export the query, access will consider the dummy fields as columns. the resulting exported file will have commas to signify a blank field.

 

[isladogs]

thanks ridders. i really wish i can use a union query because i find union query easy but , like what i said in my first post, i cannot use dummy fields because when i export the query, access will consider the dummy fields as columns. the resulting exported file will have commas to signify a blank field.

Yes, just as it would for any blank fields in the 'real' data.
But it doesn't matter as it will use the field headers to create the columns of export.
In fact it wouldn't matter if you had a field where every record was blank. It should still work

 

[anski]

the final file should look like this:
"hdr","john",2,02/14/1989,"manager","ny"
"dtl","eric",06/04/1989,"john"
"dtl","lisa",11/15/1995,"john"

with what you are proposing, this is what access will do:
"hdr","john",2,02/14/1989,"manager","ny"
"dtl","eric",,06/04/1989,"john"
"dtl","lisa",,11/15/1995,"john"

i do not want that extra comma.

 

[isladogs]

the final file should look like this:
"hdr","john",2,02/14/1989,"manager","ny"
"dtl","eric",06/04/1989,"john"
"dtl","lisa",11/15/1995,"john"

with what you are proposing, this is what access will do:
"hdr","john",2,02/14/1989,"manager","ny"
"dtl","eric",,06/04/1989,"john"
"dtl","lisa",,11/15/1995,"john"

i do not want that extra comma.

Actually you would need to do more than that as you have 6 fields in query 1 and 5 in query 2.
I don't understand your issue about empty fields. As already stated, the csv file will handle them perfectly so the export will work fine.

Having said all that, the FSO method described by Mark will also work just as well. Your choice!

 

[static]

Concatenation?

select 
	'hdr' as a,
	firstname,
	somenumber & chr(34) & chr(44) & chr(34) & somedate as b,
	jobtitle & chr(34) & chr(44) & chr(34) & location as c 
	from table1
union
select 
	'dtl' as a,
	firstname,
	somedate as b,
	manager as c 
	from table2

 

[anski]

^thanks, static. ALMOST there. if i do your suggestion, access will put quotes around each text field. i have decided to use alias fields and then do a replace command on the alias fields after the export.

 

[static]

I think that's irrelevant?
I inserted quote marks in the middle of the "text" in anticipation of that.

You could concatenate every field into one sub field.

 

[Pat Hartman]

You can't jus mush the fields together. Access will enclose the whole string with quotes and that will make the file not render correctly

If you want to write files with differing record formats, you have to do it using WRITE. The TransferText methods assume (correctly) that all rows conform to the same pattern.

 

[static]

Who are you responding to?

If I have a field with a value of

foo","bar

and Access outputs it to file as

"foo","bar"

that's correct isn't it?

 

[Pat Hartman]

If you have a field with the contents:

"foo", "bar"

And export that using TransferText, what you end up with is:

"textfld1", "textfld2", ""foo", "bar"", "textfld4", etc

So you end up with 4 fields rather than 5. You can't fool Mother Nature or Access.

If you want to use TransferText of a union query, all the rows must have the same column count, and the columns must have the same data type in every row. So you can't have a date in column 3 of row 4 and text in the first three rows. The date field will be considered a string rather than a date.


If your union mis orders columns, you may not get an error but the results can be strange.

Select first, last street
Union select street, first, last;

The query will produce rows with three columns except sometimes the first column is first name and sometimes it is street. The query engine doesn't know you have made a mistake since the data type is the same for all three columns.

 

[June7]

If you don't have the dummy field resulting in blanks for a column, where do you expect that '2' value to be placed? The result you look for is not a valid data structure as the data types are not properly aligned. What do you expect to do with this mushed data? Exactly why is the blank fields an issue?

 

[static]

I did a quick test

CurrentDb.QueryDefs("query1").SQL = "select 'foo' & chr(34) & chr(44) & chr(34) & 'bar' from table1"
DoCmd.TransferText acExportDelim, , "query1", "C:\Users\jon\Desktop\x.txt", 0


and got "foo"",""bar" for every record.

I would think it would be quicker to open the file and replace "","" with "," than to output each field from a recordset.

 

[MarkK]

Using TransferText here is getting in your way, making the solution more complicated than it needs to be. The simplest solution is to open a file directly, write to it from your first recordset, then open a second recordset, and write to the file again. Then close the file. Done.
IMO
Mark

 

[static]

Depends if the team members need to be listed under manager or not.

edit:

Imean if you were outputting multiple teams

manger1
teammember1
teammember2
manager2
etc

you would need 2 recordsets.

 

[MarkK]

My point is that if you want a bunch of random data with a variable structure written to a file, then don't use TransferText. Rather, create a new file, and write whatever you want, directly to that file.
Mark

 

[MarkK]

Here's how easy it is to create a file, and write to it, and pop it open...

Sub TestWriteToFile()
    Dim i As Integer
    Dim fn As String
    
[COLOR="Green"]    'construct a path[/COLOR]
    fn = CurrentProject.PATH & "\NewTestFile.csv"
[COLOR="green"]    'create the file[/COLOR]
    With New Scripting.FileSystemObject
        With .CreateTextFile(fn, True)
[COLOR="green"]            'write a bunch of lines to it[/COLOR]
            For i = 1 To 6
                .WriteLine "This is line " & i
            Next
            .Close
        End With
    End With
    
[COLOR="green"]    'admire your work[/COLOR]
    FollowHyperlink fn

End Sub