Link to home
Start Free TrialLog in
Avatar of Mach03
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("HostImport.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?
Avatar of NicksonKoh
NicksonKoh
Flag of Singapore image

Hi Mach03,

I would trim off the " as follows

Set oInStream = objFSO.OpenTextFile("HostImport.csv", 1, false)
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
Mach03,

I would trim off the " as follows

Set oInStream = objFSO.OpenTextFile("HostImport.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
Avatar of Mach03
Mach03

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
ASKER CERTIFIED SOLUTION
Avatar of NicksonKoh
NicksonKoh
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial