I’m using the below code (that I borrowed from a different post) to export data from a view using a scheduled agent to a comma-delimited file. The agent runs fine and exports the data, but the output doesn’t contain quotation marks around the data fields which is bad because some of my fields contain commas and new lines.
Can someone add code to my agent that quotes every field? I’m not a LS guy. I don’t care about the data types because I’ll be manipulating the data using a different program. Just need quotes. I tried using Write instead of Print but all that did was put 1 " at the beginning of each row and 1 " at the end of each row.
Thanks
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim nbcol As Integer
Dim k As Integer
Dim newline As String
Dim fileName As String
Dim fileNum As Integer
Set db = s.CurrentDatabase
Set view = db.GetView( "Utilities\All Contacts for Export" )
nbcol = Ubound(view.Columns)
Set doc = view.getfirstdocument
fileName = "C:\RevenueAppend\AllContacts.csv"
fileNum = Freefile()
Open fileName For Output As fileNum
k = 0
While Not (doc Is Nothing)
newline = ""
For k = 0 To nbcol
newline = newline + doc.ColumnValues(k)
If k <> nbcol Then newline = newline + ","
Next
Print #fileNum, newline
Set doc = view.GetNextDocument(doc)
Wend
Close fileNum
End Sub
Subject: Export scheduled agent with quotes surrounding all fields
How about this?
newline = newline + |“| + doc.ColumnValues(k) + |”|
Subject: RE: Export scheduled agent with quotes surrounding all fields
Thanks for the info. I’m getting closer. One of my columns (maybe more) has carriage returns (new line) in it. All fields are being quoted (Yay!), but I’d prefer the carriage returns to be non-existent in the final CSV file. When I export the same exact view via File->Export this is not a problem. All of the new lines are gone. Any further ideas?
Subject: RE: Export scheduled agent with quotes surrounding all fields
Please read about the Replace function. One other case you might want to consider, is if your data contains quote characters. In that case, just slapping quotes around it isn’t going to give you a valid output; you would need to “escape” the internal quotes somehow. Exactly how, depends on the application that will consume the output. And depending on that, you may need to also escape other characters (e.g. if " is used to represent a quote in the data, you’ll also need to escape , probably as \).
Subject: RE: Export scheduled agent with quotes surrounding all fields
I took a long and hard look at the Replace function, but since I’m not that familiar with LS, the changes that I made, of course, didn’t work. Below is the updated code. Why can’t LS simply have a function that mimics the behavior of exporting when you choose File → Export? Anyone have any recommendations?--------------------
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim nbcol As Integer
Dim k As Integer
Dim newline As String
Dim fileName As String
Dim fileNum As Integer
Dim findarray(1) As String
Dim replacearray(1) As String
findarray(0) = Chr(10)
findarray(1) = Chr(13)
replacearray(0)= " "
replacearray(1)= " "
Set db = s.CurrentDatabase
Set view = db.GetView( "Utilities\All Orders for Export" )
nbcol = Ubound(view.Columns)
Set doc = view.getfirstdocument
fileName = "C:\RevenueAppend\AllOrders.csv"
fileNum = Freefile()
Open fileName For Output As fileNum
k = 0
While Not (doc Is Nothing)
newline = ""
For k = 0 To nbcol
newline = Replace (newline, findarray, replacearray)
newline = newline + |"| + doc.ColumnValues(k) + |"|
If k <> nbcol Then newline = newline + ","
Next
Print #fileNum, newline
Set doc = view.GetNextDocument(doc)
Wend
Close fileNum
End Sub
Subject: RE: Export scheduled agent with quotes surrounding all fields
Actually, I don’t think File / Export works all that well with quotes in the data either.
Subject: Solution: Export scheduled agent with quotes surrounding all fields
HOLY COW!! (no offense) Thanks everyone. I was able to get it to work. Here’s the full code:
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim nbcol As Integer
Dim k As Integer
Dim newline As String
Dim fileName As String
Dim fileNum As Integer
Dim findarray(1) As String
Dim replacearray(1) As String
findarray(0) = Chr(10)
findarray(1) = Chr(13)
replacearray(0)= " "
replacearray(1)= " "
Set db = s.CurrentDatabase
Set view = db.GetView( "Utilities\All Orders for Export" )
nbcol = Ubound(view.Columns)
Set doc = view.getfirstdocument
fileName = "C:\RevenueAppend\AllOrders.csv"
fileNum = Freefile()
Open fileName For Output As fileNum
k = 0
While Not (doc Is Nothing)
newline = ""
For k = 0 To nbcol
newline = Replace (newline, findarray, replacearray)
newline = newline + |"| + doc.ColumnValues(k) + |"|
If k <> nbcol Then newline = newline + ","
Next
Print #fileNum, newline
Set doc = view.GetNextDocument(doc)
Wend
Close fileNum
End Sub