Export Rows as CSV / Covert Rows to Columns

  • Thread starter Thread starter Joshua
  • Start date Start date
J

Joshua

I have a table that I need to export as a csv but when I do it causes my one
column to be in rows and the values are not seperated by commas. Sorry if
that's confusing; it is exporting the table like this (no header):

3654
5643
1234

And I need it to export that column like this:

3654;5643;1234

But I have to do that programatically because this is an export that will be
continually made by multiple users. I know that excel allows you to transpose
which is exactly what I'm trying to programatically do in access.

Thanks in advance for any help.
 
Joshua said:
I have a table that I need to export as a csv but when I do it causes my
one
column to be in rows and the values are not seperated by commas. Sorry if
that's confusing; it is exporting the table like this (no header):

3654
5643
1234

And I need it to export that column like this:

3654;5643;1234

But I have to do that programatically because this is an export that will
be
continually made by multiple users. I know that excel allows you to
transpose
which is exactly what I'm trying to programatically do in access.

Thanks in advance for any help.
Click to expand...


Public Sub TestSub()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer

intFile = FreeFile

'this will over-write any existing file with this name in the same
'folder as the application, so be careful that there isn't a file with
'this name that you want to keep.
Open CurrentProject.Path & "\test.csv" For Output As intFile

Set db = CurrentDb

'change "TestNum" to the name of your field and "tblTest" to the name of
your table.
Set rst = db.OpenRecordset("SELECT TestNum FROM tblTest")

Do Until rst.EOF

'the semicolon at the end of this line prevents
'the print statement from starting a new line,
'this keeps everything on one line. CStr converts
'a number to a string, you can leave out the call
'to the CStr() function if your field is a text field.
Print #intFile, CStr(rst.Fields("TestNum"));

rst.MoveNext
If Not rst.EOF Then
Print #intFile, ";";
End If
Loop
rst.Close
Close #intFile

MsgBox "Finished"

End Sub
 
Back
Top