CSV file losing "#" in column headers

[Mark Mariano]

Hello-
I am exporting the results of a query to a csv file, the export is working fine, the file is created, however, when I open the csv (In Excel or notepad) two of the column headers get altered. Both of these columns contain a "#" that gets replaced by a "."
When I look at the results of the query the headers are fine, if I export to .xls the headers are also fine. It is just when I go to csv.

Examples:
"File #" appears as "File ."
" Job Cost #" appears as "Job Cost ."

Any ideas on why this would happen??

Thanks


EggHeadCafe - Software Developer Portal of Choice
Excel Reports in ASP.NET 2.0
http://www.eggheadcafe.com/tutorial...472-36680847ffbe/excel-reports-in-aspnet.aspx

 

[Roger Carlson]

I'm not sure why it does, but it just does. The only workaround I've found
for it is to export your file with low-level I/O as shown below. It's
another good reason not to use special characters or spaces in your table or
field names.

Sub ExportTextFileDelimited()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Open "c:\TestOutput.txt" For Output As #1
Set cnn = CurrentProject.Connection
'strDS = cnn.Properties("data source")

rst.Open "qryExportFormatted", cnn, adOpenForwardOnly, adLockReadOnly
'print your field names
Print #1, "Field1,Field2,Field3,Field4,Field5"
rst.MoveFirst
Do While Not rst.EOF
MyString = rst!Field1 & "," & _
rst!Field2 & "," & _
rst!Field3 & "," & _
rst!Field4 & "," & _
rst!Field5
Print #1, MyString
rst.MoveNext
Loop

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.Description
Resume ExportTextFile_Exit

End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L