-
Mar 13th, 2018, 02:15 AM
#1
Thread Starter
New Member
Keeping Speech Marks In a Field When Using ADODB To Read/Write a CSV
Hi,
Another project of mine required looking up account numbers from an Excel spreadsheet but the machine does not have Excel on it, so ADODB was the way to go. For the below code I found using a select statement would be easier and quicker to simply sort a comma separated CSV file in this new project. However, there is a field in this database that contains a full address separated by commas and enclosed by speech marks. (aka text qualifiers) Is there a way to keep the speech marks that are wrapping the field when using ADODB? Because when I output the data, or write it to console, the qualifiers are gone and the data is not incorrect. (FYI I'm using MS VB 2010 to debug my vbscript)
Code:
Dim cn, rs, strPathtoTextFile, fso, ts, Line
Const adOpenStatic = 3
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Const adCmdText = &H1
'Use 'Set' when in Workflow
cn = CreateObject("ADODB.Connection")
rs = CreateObject("ADODB.Recordset")
strPathtoTextFile = "C:\Users\my data location\"
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited""")
'Sort recordset by Contract number
rs.Open("SELECT * FROM Statement.csv ORDER BY Contract", _
cn, adOpenForwardOnly, adLockOptimistic, adCmdText)
'Create file system obj and output file
fso = CreateObject("Scripting.FileSystemObject")
ts = fso.OpenTextFile(strPathtoTextFile & "output.csv", 2, True)
Line = ""
'Store header in Line
For Each tmp In rs.Fields
Line = Line & tmp.Name & ","
Next
Dim afield
afield = rs("BR ADD").Value
Console.Write(afield)
'Write header while omitting a comma from been added to end of string
ts.WriteLine(Left(Line, Len(Line) - 1))
'Write recordset in batches of 1000 to increase performance
'GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
Do Until rs.EOF
ts.Write(rs.GetString(, 1000, ",", vbCrLf, ""))
Loop
'Clean up
Line = ""
rs.close()
rs = Nothing
ts.close()
cn.Close()
Your advice is much appreciated.
Regards,
S
Last edited by SeanVb; Mar 13th, 2018 at 02:23 AM.
Reason: formatting
-
Mar 13th, 2018, 04:38 PM
#2
Re: Keeping Speech Marks In a Field When Using ADODB To Read/Write a CSV
You could try using a Tab delimited .csv file instead of Comma delimited. That should allow you to read in the fields with quotes and apostrophes intact. If that part works, and you can read in the data with punctuation, then you should be able to write it back out the same way, to a Tab delimited file.
-
Mar 14th, 2018, 02:39 AM
#3
Thread Starter
New Member
Re: Keeping Speech Marks In a Field When Using ADODB To Read/Write a CSV
Originally Posted by jdc2000
You could try using a Tab delimited .csv file instead of Comma delimited. That should allow you to read in the fields with quotes and apostrophes intact. If that part works, and you can read in the data with punctuation, then you should be able to write it back out the same way, to a Tab delimited file.
Hi jdc,
Thanks for your reply. However I prefer to manipulate the data as I received it. At the expense of performance (+-2 seconds), I managed a known workaround. Using FSO I created a temp file, I then looped through the input data and used the Split function to break the record down into an array. I then targeted the array indexes and escaped the qualifiers, used Join and wrote the records to said temp file. Now that the qualifiers were escaped, the temp file was used via ADO to sort the data and the field in question retains its qualifiers.
Code:
x = 0
Do Until inputObjFile.AtEndOfStream
ReDim Preserve newDataLine(x)
If x <> 0 Then
'Modify & write records
oldDataLine = Split(inputObjFile.ReadLine, ",")
oldDataLine(29) = """""" & oldDataLine(29)
oldDataLine(32) = oldDataLine(32) & """"""
newDataLine(x) = Join(oldDataLine, ",")
NewRecordsFile.Write(Left(newDataLine(x), Len(newDataLine(x)) - 1) & vbCrLf)
Else
'Write header
NewRecordsFile.Write(inputObjFile.ReadLine & vbCrLf)
End If
x = x + 1
Loop
NewRecordsFile.close()
inputObjFile = Nothing
NewRecordsFile = Nothing
inputObjFSO = Nothing
Now I knew I could have done this all along. I just hoped there was a more elegant way that I was not aware of.
So if anyone does know a way, please do share.
Regards,
S
Last edited by SeanVb; Mar 14th, 2018 at 05:17 AM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|