Mach03
asked on
asp how to split a csv file when an apostrophe is present
Does anyone know how to load a csv file that has apostrophies? I'm trying to upload and process a csv file and I can't think of a way around this? Here's what I have..
Set oInStream = objFSO.OpenTextFile("HostI mport.csv" , 1, false)
Do Until oInStream.AtEndOfStream
i = i + 1
sLine = oInStream.readLine
strValue = Split( sLine, ", " )
If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
<insert the line>
End iF
Loop
Howerver in the data I have:
94884, "John", "Smith", "Smith, John"
Thoughts?
Set oInStream = objFSO.OpenTextFile("HostI
Do Until oInStream.AtEndOfStream
i = i + 1
sLine = oInStream.readLine
strValue = Split( sLine, ", " )
If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
<insert the line>
End iF
Loop
Howerver in the data I have:
94884, "John", "Smith", "Smith, John"
Thoughts?
Mach03,
I would trim off the " as follows
Set oInStream = objFSO.OpenTextFile("HostI mport.csv" , 1, false)
Do Until oInStream.AtEndOfStream
i = i + 1
sLine = oInStream.readLine
strValue = Split( sLine, ", " )
'Code to get rid of " in the array strValue
For n = 0 to UBound(strValue)
strValue(n) = Trim(strValue)
If Left(strValue(n), 1) = """" Then strValue(n) = Right(strValue(n), Len(strValue(n))-1)
If Right(strValue(n), 1) = """" Then strValue(n) = Left(strValue(n), Len(strValue(n))-1)
Next
If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
<insert the line>
End iF
Loop
Cheers,
NicksonKoh
I would trim off the " as follows
Set oInStream = objFSO.OpenTextFile("HostI
Do Until oInStream.AtEndOfStream
i = i + 1
sLine = oInStream.readLine
strValue = Split( sLine, ", " )
'Code to get rid of " in the array strValue
For n = 0 to UBound(strValue)
strValue(n) = Trim(strValue)
If Left(strValue(n), 1) = """" Then strValue(n) = Right(strValue(n), Len(strValue(n))-1)
If Right(strValue(n), 1) = """" Then strValue(n) = Left(strValue(n), Len(strValue(n))-1)
Next
If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
<insert the line>
End iF
Loop
Cheers,
NicksonKoh
ASKER
That almost works except when there is a comma within the apostrophies. Like in my example data:
94884, "John", "Smith", "Smith, John"
Excempt for
3rd Column = Smith
4th Column = John
Needs to be
3rd Column = Smith, John
94884, "John", "Smith", "Smith, John"
Excempt for
3rd Column = Smith
4th Column = John
Needs to be
3rd Column = Smith, John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would trim off the " as follows
Set oInStream = objFSO.OpenTextFile("HostI
Do Until oInStream.AtEndOfStream
i = i + 1
sLine = oInStream.readLine
strValue = Split( sLine, ", " )
For n = 0 to UBound(strValue)
Next
If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
<insert the line>
End iF
Loop
Cheers,
NicksonKoh