?
Solved

asp how to split a csv file when an apostrophe is present

Posted on 2007-10-21
4
Medium Priority
?
542 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:Mach03
  • 3
4 Comments
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20120345
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
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20120352
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
0
 

Author Comment

by:Mach03
ID: 20167039
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
0
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 2000 total points
ID: 20167230
Hi,

Set re = New RegExp
re.Pattern = "(""\w+,) (\w+"")"

Set oInStream = objFSO.OpenTextFile("HostImport.csv", 1, false)
Do Until oInStream.AtEndOfStream
     i = i + 1
     sLine = oInStream.readLine
     'Take out the comma space in e.g. "Smith, John"
     sLine = re.Replace(sLine, "$1<space>$2")
     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)

       'Put back any comma space that was taken out previously
       If InStr(strValue(n), "<space>") > 0 Then strValue(n) = Replace(strValue(n), "<space>", " ")
    Next
 
     If Not IsNull(strValue(0)) OR Not IsEmpty(strValue(0)) Then
      <insert the line>
    End iF
Loop
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Integration Management Part 2
Loops Section Overview
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question