Reading CSV files...

[chihuahualand]

I've seen topics suggesting "Separate"... this only works on the simplest of data.

I've seen this
http://www.mjtnet.com/forum/viewtopic.php?t=1467
(which is oddly incomplete)

I started building the code in MS but without reasonable psuedocode. I keep think I'm worrying about embedded quotes and commas too much.

It sure would be nice to have a ReadCSVfile command within MS

in lieu of that, does anybody have some reasonable code?

I'm going to restart the project using vbscript,

[jpuziano]

I've seen this
http://www.mjtnet.com/forum/viewtopic.php?t=1467
(which is oddly incomplete)

I see what you mean, the VBScript looks unfinished, there is a VBSTART but no VBEND.

Marcus, do you have the complete script for that example and if so, could you re-post it? Please and thanks.

[JRL]

I posted a sample using that VBScript HERE

[chihuahualand]

A revised version
This version doesn't load the entire file into an array, rather, it returns the next record with the fields separated by linefeeds. The first record returned is either field names or the first line of data (depending on how your file is constructed). The Microsoft text driver assumes a header line, so there's a wee bit of programming to compensate.

Code: Select all

VBSTART
'An example file.  Save in My Documents
'Remember to remove comments at start of line if pasting into editor for example file
'header1,header2,header3,header4
'r1c1,r1c2,r1c3
'r2,c1",r2c2,r2c3
'r3c1,"r""3,c""2",r3c3
'r4c1,r4c2,"r,4"",""c3"

rcount = 0
tcount = 0
Set oShell = CreateObject( "WScript.Shell" )
uDocs=oShell.ExpandEnvironmentStrings("%UserProfile%")
Set oShell = nothing
if trim(uDocs)="" then
	udocs = "C:\My Documents\"
else
	uDocs = uDocs & "\My Documents\"
end if

Set rs = createobject("ador.recordset")
strConnect =  "Driver={Microsoft Text Driver (*.txt; *.csv)};" &  "DefaultDir=" & uDocs & ";"

Function csv(filename)
csv = ""
rcount = rcount + 1
if rcount=1 then
	rs.open "select * from " & filename, strConnect, 3
	for each f in rs.fields
		csv = csv & f.name & chr(10)
	next
elseif rcount=2 then
	for each f in rs.fields
		csv = csv & f.value & chr(10)
	next
else
	rs.movenext
	for each f in rs.fields
		csv = csv & f.value & chr(10)
	next
end if
csv = left(csv, len(csv)-1)
end function

Function TotalRecords(filename)
	Set tmp = createobject("ador.recordset")
	tmp.open "select * from " & filename, strConnect, 3
	TotalRecords = tmp.recordcount+1
	Set tmp = nothing
End Function

VBEND

VBeval>TotalRecords("csv.csv"),x
MessageModal>x

VBeval>csv("csv.csv"),x
Separate>x,LF,s
MessageModal>s_1

VBeval>csv("csv.csv"),x
Separate>x,LF,s
MessageModal>s_1

VBeval>csv("csv.csv"),x
Separate>x,LF,s
MessageModal>s_1

[Marcus Tettmar]

See:
http://www.mjtnet.com/forum/viewtopic.php?p=23575

And:
http://www.mjtnet.com/blog/2009/03/12/r ... csv-files/

[jpuziano]

I've seen this
http://www.mjtnet.com/forum/viewtopic.php?t=1467
(which is oddly incomplete)

I see what you mean, the VBScript looks unfinished, there is a VBSTART but no VBEND.

Marcus, do you have the complete script for that example and if so, could you re-post it? Please and thanks.

Thanks for fixing the code in the above mentioned post in Scripts and Tips...

Import data from CSV file into multidimensional array

...and adding the link there to the new blog post showing all the great ways we have to do this.

And thanks to chihuahualand for letting us know there was a problem with the original code posted there... because now its fixed.