MS Access - Exporting Text file with reserved character in heading

[Roe882]

How can I export a text file that has a # (reserved character) in one of my column headings using the DoCmd.TransferText acExportDelim procedure?

[matthewspatrick]

It should let you do it. What is the error?

[Roe882]

Patrick:
Thanks for responding so quickly. Microsoft (MS) advises that it is best that we do not use reserved characters, but I must include it as the text file is being updated to an environment that I have no access or control over its format. However, MS posted a function that they say should do the trick. However, on testing it, at the line: DoCmd.TransferText acExportDelim, , strTempFileName, True -- I received a Run-time error '3027' Cannot update. Database or Object is read-only. The read-only attributes per this error does not exists in my database. I ran a few tests with minor modifications to Microsoft's function and was able to get rid of that error message. The revised code is as follows :

Option Compare Database
Option Explicit
Public Function FixExportedFieldNames(strTableName As String, StrFileName As String)

Dim fso As New FileSystemObject
Dim ts1, ts2 As TextStream
Dim strTxtLine, strTempFileName As String
Dim strTempTable As String
strTempTable = "tblCoreGpAcctUpload"
strTempFileName = "C:\FixFieldNamesTemp.txt"

DoCmd.TransferText acExportDelim, , strTempTable, strTempFileName, True


'Open Files
Set ts1 = fso.OpenTextFile(StrFileName)
Set ts2 = fso.OpenTextFile(strTempFileName, ForWriting, True, TristateFalse)

'Replace the period on the first line with a number sign
strTxtLine = ts1.ReadLine
strTxtLine = Replace(strTxtLine, ". ", "#")
ts2.WriteLine strTxtLine

'Loop while not at the end of the file
Do While Not ts1.AtEndOfStream
strTxtLine = ts1.ReadLine
ts2.WriteLine strTxtLine
Loop

'Close the files
ts1.Close
ts2.Close
DoEvents

'Overwrite original file with new file
fso.CopyFile strTempFileName, StrFileName, True
fso.DeleteFile strTempFileName
End Function


Then I call the above function as follows:

Private Sub CommandOH_Click()
Dim strTableName As String
Dim StrFileName As String
Call FixExportedFieldNames(strTableName:="qryCoreGpUloadAppend", _
StrFileName:="C:\ExpGPAcctUpload")
End Sub

A Reference is set to MS Scripting Runtime Library.

With the above the code runs now with a Run-time error '53'; File not found at the below line:

Set ts1 = fso.OpenTextFile(StrFileName)

It apparently is not reading the text file saved to the C drive. How can I get Access to read the file and then write to it. Or is there an easier way of doing this.

Any help would be appreciated.

Thanks again,
Rowena

Edited 26-Jul-06 by geekgirlau. Reason: insert vba tags

[geekgirlau]

I haven't played with your code, but the command will only work if there is a file named "C:\ExpGPAcctUpload" - if your file is actually "C:\ExpGPAcctUpload.txt", the macro will fail.

By the way, I've edited your post to add vba tags. When you post code, select the code text and click on the "VBA" button to add the tags - it makes it much easier to read, and therefore much more likely that you'll get a response.

And welcome to the board!

[stanl]

As I understand this, a table exists in Access you want to export as delimited (I assume CSV). One alternative to the filesystemobject would be to

1. query the table as a Recordset
2. Create a string of concatenated field names (and change what needs to be changed)
3. Concatenate #2 with getstring() and load into an ADO Stream
4. Save the Stream as the desired output file.

sounds complicated but actually faster and with less code. If it is possible to post just a few rows of the Access Table, this will make more sense.

.02
Stan

[Roe882]

Thanks all.

Stan: Attached is a few lines from the table (I omitted a few of the columns):

"Account Name" "Nat Acct" "GP Account #" "B/S - P/L"
"Allocations" "6804" "680401910000000" "1"
"Allocations" "6804" "680401920000000" "1"
"Allocations" "6804" "680401920100000" "1"

-Sorry, I wasn't able to properly line up the fields above, so I have separated each field by quotes (").

I will try your above suggestion, because I tested my above code once again by exporting a copy of the strFileName, then calling the function, but it outputs the file without the "#" character in the heading. It still outputs "." instead of the "#".

I will update and check back for any continued advice and help.

Thanks again. Your advice and help is greatly appreciated.
Rowena

[stanl]

I'll probably get criticized for this, but I don't have the time to write an Access VBA module, so I used Winbatch. Still, there is enough 'VBA code' to illustrate the methodology. I took your sample data and created an Access Table (which hopefully attached) named accts in accts.mdb. I then ran this script


cMDB=strcat(dirscript(),"accts.mdb")
If ! FileExist(cMDB) Then Exit
cOut = StrCat(dirscript(),"accts.txt")
If FileExist(cOut) Then FileDelete(cOut)
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%"
oRS = CreateObject("ADODB.Recordset")
oRS.Open("accts",cConn,1,3,2)
cTxt=""
For i= 0 To oRS.Fields.Count-1
cTxt=StrCat(cTxt,oRS.Fields(i).Name,",")
Next
cTxt = StrSub(cTxt,1,StrLen(cTxt)-1)
FilePut(cOut,StrCat(cTxt,@CRLF,oRS.GetString(, , ",", @CRLF)))
oRS.Close()
oRS=0
Exit


which (in about 1/10,000 of a second) produced



Account Name,Nat Acct,GP Account #,B/S - P/L
Allocations,6804,680401910000000,1
Allocations,6804,680401920000000,1
Allocations,6804,680401920100000,1


If your output file requires "" qualifiers, getstring() can handle that. I'm sure someone can retrofit the code to pure VBA, but I have to get on with generating 12,000 Excel Range Images....

Stan