Access 2007 query export - add line feed 1

[mlev]

I have a vbscript that connects to an Access 2007 database, exports data from a query, and writes it to a text file. The text file is then downloaded onto our web proxy device, which is Linux-based.

Access adds a carriage return (cr) at the end of each record returned by the query. But the proxy device wants a line feed (lf).

How can I add a line feed after each record in the Access query? I don't see a place to add formatting like this in the Access 2007 query designer. Could I add it in the vbscript? I add vbCrlf's at the end of the category, but I need them at the end of each record returned by the query and I don't know how to do that.

Segment of the script:

' Identify source database and target file for export'
db = "\\10.1.1.10\proxy-local.accdb" 
TextExportFile = "\\10.1.1.10\proxy_list.txt" 

 
Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
 
' Identify Microsoft database provider for Access 2007'
cn.Open _ 
      "Provider=Microsoft.ACE.OLEDB.12.0; " & _
      "Data Source=" & db 
   
' Run the query to select the nocache category from the Data table'
strSQL = "SELECT * FROM nocache"  
rs.Open strSQL, cn, 3, 3 
 

' Write the query output to a text file'
Set fso = CreateObject("Scripting.FileSystemObject") 
Set f = fso.CreateTextFile(TextExportFile, True) 
strHeader = "define category nocache" 
a = strHeader & vbCrLf & rs.GetString & "end" & vbCrLf 
f.WriteLine a  
f.Close

Ay rites lolcode?

 

[tsuji]

>a = strHeader & vbCrLf & rs.GetString & "end" & vbCrLf
>f.WriteLine a
Like this.
[tt]a = strHeader & [red]vbLf[/red] & [blue]replace(rs.GetString,vbcr,vblf)[/blue] & "end" & [red]vbLf[/red]
f.[red]Write[/red] a
[/tt]

 

[mlev]

Sorry, I wasn't quite clear. The variable "rs.GetString" provides the full text output of the query, which is a list of websites. That list is output from Access and written to the textfile. It shows up in that file as a string of text divided only by carriage returns. In VI, the character "^M" appears at the end of each line. (015 13 0x0d CR, carriage return, Control-M)
The output is like this:

define category nocache
adp.com^Mapp10.schooldude.com^Mcampus.careersafeonline.com^Mcitrix.hbi.org^Memaint.com^Mfma.lesolsoncompany.com^Mgalax
yl.webex.com^Mgotomeeting.com^Miclasses.org^Mico.iclasses.org^Minside.mtctrains.com^Mminact.com^Mmtctrains.com^Mraytheon.vportal.net^M

http://www.webex.com^Mend

I need those carriage returns replaced with Lf or CrLf. The ^M isn't something I scripted -- just the way the SELECT query is returned from Access.

Ay rites lolcode?

 

[mlev]

tsuji - My apologies -- I completely overlooked the replace line in your file. Yes! Thank you!

Ay rites lolcode?

 

[tsuji]

Thanks! The maintenance of the site prevent me from posting sooner a followup.

The better approach to take is to use the built-in functionality of the adodb.recordset, sure. The separators, column and row, are controlled built-in by the position parameter:
[tt]
String=RecordSet.GetString([StringFormat],[NumRows],[ColumnDelimiter],[RowDelimiter],[NullExpr])[/tt]

ColumnDelimiter is default to vbtab and RowDelimiter is default to vbcr. Hence, to use the desired RowDelimiter, the line should be better scripted like this.
[tt]
a = strHeader & vbLf & [blue]rs.GetString(,,,vblf)[/blue] & "end" & vbLf
[/tt]
To have csv proper, modulo other complication, it would look like this.
[tt]
a = strHeader & vbLf & [blue]rs.GetString(,,",",vblf)[/blue] & "end" & vbLf
[/tt]

 

[mlev]

Very cool. I'll have to read up more on the adodb.recordset.

Ay rites lolcode?