Create Manual Header Line For Exported Csv File

[diannosd]

Good afternoon, Using code snippets I have found, I have managed to create a nice filter on a form. I am now trying to export the results of the query to a csv file. The code below works fine except, I don't know how to write the manual header to the csv file.

I currently have

'Create and write the header line
StrHeader = Chr(34) & "," & Chr(34)
StrHeader = Chr(34) & " Group No" & Chr(9) & "Group Name" & Chr(34)

At the moment, I just have Group No Group Name appearing in the first line but all in the first column. Can some one please help?

This is all the code I am using

Dim dbsNorthwind As DAO.Database
Dim rstRecords As DAO.Recordset
Dim StrHeader As String

 Set dbsNorthwind = CurrentDb

   Set rstRecords = dbsNorthwind.OpenRecordset("SELECT Qry_Corporate_Accounts.* FROM Qry_Corporate_Accounts " & BuildFilter)
   Open "C:\SEAN.CSV" For Output As #1
 
'Create and write the header line
StrHeader = Chr(34) & "," & Chr(34)
StrHeader = Chr(34) & " Group No" & Chr(9) & "Group Name" & Chr(34)

Print #1, StrHeader

  Do While Not rstRecords.EOF
    Write #1, rstRecords![Group No], rstRecords![Corporate Name]
    rstRecords.MoveNext
  Loop   
   
   rstRecords.Close
   dbsNorthwind.Close

   Set rstRecords = Nothing
   Set dbsNorthwind = Nothing
   Close #1

 

[DJkarl]

Good afternoon, Using code snippets I have found, I have managed to create a nice filter on a form. I am now trying to export the results of the query to a csv file. The code below works fine except, I don't know how to write the manual header to the csv file.

I currently have

'Create and write the header line
StrHeader = Chr(34) & "," & Chr(34)
StrHeader = Chr(34) & " Group No" & Chr(9) & "Group Name" & Chr(34)

At the moment, I just have Group No Group Name appearing in the first line but all in the first column. Can some one please help?

This is all the code I am using

Dim dbsNorthwind As DAO.Database
Dim rstRecords As DAO.Recordset
Dim StrHeader As String

 Set dbsNorthwind = CurrentDb

   Set rstRecords = dbsNorthwind.OpenRecordset("SELECT Qry_Corporate_Accounts.* FROM Qry_Corporate_Accounts " & BuildFilter)
   Open "C:\SEAN.CSV" For Output As #1
 
'Create and write the header line
StrHeader = Chr(34) & "," & Chr(34)
StrHeader = Chr(34) & " Group No" & Chr(9) & "Group Name" & Chr(34)

Print #1, StrHeader

  Do While Not rstRecords.EOF
    Write #1, rstRecords![Group No], rstRecords![Corporate Name]
    rstRecords.MoveNext
  Loop   
   
   rstRecords.Close
   dbsNorthwind.Close

   Set rstRecords = Nothing
   Set dbsNorthwind = Nothing
   Close #1

Lets look at these lines first:

'Create and write the header line
StrHeader = Chr(34) & "," & Chr(34)
StrHeader = Chr(34) & " Group No" & Chr(9) & "Group Name" & Chr(34)

Problem #1) The first line is useless as the StrHeader variable is completely re-written in the next line.

Problem #2) If you are going to use text delimiters (chr(34)) you need to enclose field names individually with them.

Problem #3) Chr(9) isn't a comma, so you have no comma between your comma sperated values.

StrHeader = Chr(34) & " Group No" & Chr(34) & Chr(44) & Chr(34) & 
"Group Name" & Chr(34)

 

[gemma-the-husky]

i dont understand - when saving a csv, isnt there an option switch on the vba command docmd.transfertext to include column headings?

i've read your post again - unless theres a special reason, dont build the text fiel manually. just use a stored query, and use the

docmd.transfertext command

loads of formatting options, one of which is output column headings - a lot less painful than handling text files

also

docmd.transferspreadsheet produces an excel file

 

[rolaaus]

the docmd.transfertext has a parameter called HasFieldNames

If you are importing, it will treat them as field names for your tables (matching them up accordingly if you have corresponding field names in your table)

If you are exporting, it creates a header column for your text file.

 

[diannosd]

Thanks DJkarl,

I will try your suggestion on Tuesday when back at work. Thanks

gemma-the-husky, rolaaus for your suggestions. I really wanted to use a stored query because I know the Transferspeadsheet is much easier to use but...

..with my limited knowledge....

I used the code from the Samples section of this site to build a filter and it does not a stored query. It uses

SELECT Qry_Corporate_Accounts.* FROM Qry_Corporate_Accounts " & BuildFilter

where buildfilter is a series of different criteria on a unbound form. If you know how I can Transferspreadsheet using the above method please say because I did try and look for this approach.

 

[rolaaus]

I didn't read through the entire thread, but was responding to Husky that asked if there was a fieldname option in transfer text.

if you are wanting to export a file based on a dynamic set of criteria that the user can configure via your form, then I am pretty sure you have the right way with opening up a file and writing directly to it. Maybe someone more experience might have a better way, but unless I see an example of another way , that's how I would do it.

 

[gemma-the-husky]

i didnt ask if there was a fieldname option

what i meant was

a) (i know the poster didnt ask about importing) if you are importing to an existing table , the column names have to match - ie the column names in the excel/csv sheet have ot agree to the column names in the access table [i think this is the case offhand]

b) with regard ot exporting a selected non-standard set of fields, you could do it by manually constructing the text file. alternatively you could create a temporary querydef, and use that as the base for the export.I suppose if the lines you want to get IN the export file are not all formatted the same, then doing it with a query is not an option