HI,
I have the following code which works fine except for when a value(s) is
null at end of record when it is read. For example, when a csv has 7
possible fields. But most of the records don't have the 7th field. My
function will place a comma at end of record, but if it isn't the last field
as noted by header I get a subscript error.
The first two work fine because you see it has a comma when the field was
blank or it has all 7 fields. But the second and third don't.
Example records from csv:
header1,header2,header3,header4,header5,header6,header7
testa,8,"testa,testa",01.66,deptname0,6,Y
testt,1,"tester,test",01.55,deptname1,5,
testm,2,"mine, test",01.56,deptname2,68
testy,,,TERMINATED,
code:
do until SourceFile.AtEndOfStream
SourceFields = SplitCSV(SourceFile.ReadLine)
wscript.stdout.write SourceFields(0) & " " & SourceFields(1) & " " &
SourceFields(2) & " " & SourceFields(3) & " " & SourceFields(4) & " " &
SourceFields(5) & " " & SourceFields(6) &vbcrlf '''''remove
loop
function SplitCSV(ByRef strLine)
const Quote = """"
const Quote2 = """"""
const Comma = ","
dim arrFields, blnIgnore, intFieldCount, intCursor, intStart, strChar,
strValue
if (len(trim(strLine)) = 0) then
SplitCSV = array()
exit function
end if
blnIgnore = false
intFieldCount = 0
intStart = 1
arrFields = array()
strLine = strLine & ","
for intCursor = 1 to len(strLine)
strChar = mid(strLine,intCursor,1)
select case strChar
case QUOTE
blnIgnore = not blnIgnore
case Comma
if (not blnIgnore) then
reDim preserve arrFields(intFieldCount)
if (intCursor - intStart > 0) then
strValue = mid(strLine,intStart,intCursor - intStart)
if (left(strValue, 1) = Quote) then
arrFields(intFieldCount) =
replace(mid(strValue,2,len(strValue)-2),Quote2,Quote)
else
arrFields(intFieldCount) = strValue
end if
else
arrFields(intFieldCount) = ""
end if
intFieldCount = intFieldCount + 1
intStart = intCursor + 1
end if
end select
next
SplitCSV = arrFields
end function
If a value read is not in csv as it should be based on the header I would
like to just add "" to the field. Where would I make this change in my
function?
Thank you in advance.