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

[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.

 

[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?

 

[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

 

[MarkK]

Or late bound, use...

    With CreateObject("Scripting.FileSystemObject")

...instead of...

    With New Scripting.FileSystemObject

Mark

 

[static]

I don't disagree. As with everything to do with databases and VBA it depends on the number of records involved.

If the sample is representative then do as you like.

If lots of records are being exported from 2 tables in unison then using built in functionality would probably be faster even if it needs some post processing.