How to remove charactes and ignore delimiter?

Question

I have a csv-file that contains contacts. These contacts shall be imported via a vb-Script into an Exchange-Server. The problem is that the source file contains symbols which are interpreted as delimiter:

Germany,Seehger,Warehouse,Seehger,"Schmitt, Michael",,Michael,,,,,michael.schmitt@seehger.de,,,,61462,Rosbach,,Frankfurter Strasse 243,,Warehouse
,,,,"Backup, Network",,Network,,,,,NBackup@xyz.com,,,,,Backup,,,,

In the first line it is "Schmitt, Michael". In the second line it's "Backup, Network". How can I remove the quotation marks and ensure that the commata is not interpreted as a delimiter.

My first approach was to use a regular expression to clean the data before it's imported put that's not possible.

This is my script at the moment:

Dim objFSO, objQuelldatei, objOu, objADKontakt, objKontakt
Dim strQuelldatei, strKontaktOU, strZeileTemp, strtemp
Dim aUserdaten

Const ForReading = 1   

strKontaktOU = "LDAP://ou=KontakteTest,dc=exp,dc=xyz,dc=de"

strQuelldatei = "C:\test.csv"

Set objOu = GetObject(strKontaktOU)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objQuelldatei = objFSO.OpenTextFile(strQuelldatei, ForReading)
    Do Until objQuelldatei.AtEndOfStream 
        strZeileTemp = objQuelldatei.Readline 

            If InStr(strZeileTemp, Chr(34) >
               'Werte per Komma trennen und in eindimensionales Array schreiben
               aUserdaten = Split(strZeileTemp, ",") 

               emailExists = False
                   If Not emailExists Then

                   strtemp = aUserdaten(4)
                   wscript.echo strtemp

                   Set objKontakt = objOu.Create("contact", "CN=" &strtemp)

                   objKontakt.put "co" , aUserdaten(0)
                   objKontakt.put "company" , aUserdaten(1)
                   objKontakt.put "department" , aUserdaten(2)
                   objKontakt.put "description" , aUserdaten(3)
                   objKontakt.put "displayName" , aUserdaten(4)
                   objKontakt.put "facsimileTelephoneNumber" , aUserdaten(5)
                   objKontakt.put "givenName" , aUserdaten(6)
                   objKontakt.put "homePhone" , aUserdaten(7)
                   objKontakt.put "info" , aUserdaten(8)
                   objKontakt.put "initials" , aUserdaten(9)
                   objKontakt.put "ipPhone" , aUserdaten(10)
                   objKontakt.put "mail" , aUserdaten(11)
                   objKontakt.put "mobile" , aUserdaten(12)
                   objKontakt.put "pager" , aUserdaten(13)
                   objKontakt.put "physicalDeliveryOfficeName" , aUserdaten(14)
                   objKontakt.put "postalCode" , aUserdaten(15)
                   objKontakt.put "sn" , aUserdaten(16)
                   objKontakt.put "st" , aUserdaten(17)
                   objKontakt.put "streetAddress" , aUserdaten(18)
                   objKontakt.put "telephoneNumber" , aUserdaten(19)
                   objKontakt.put "title" , aUserdaten(20)
                   objKontakt.SetInfo
            End If                 
    Loop 
objQuelldatei.Close 

Answer 1

Dont use split etc with csv files. Use ADODB. Here is an example:

Option Explicit

Const PATH = "C:\folder\where\csv\sits"
Const FILE = "filename.csv"

Dim cn,rs
Dim val

Set cn = CreateObject("ADODB.Connection")

'if the first line is column names such as UserName, Phone etc, set HDR=YES
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & PATH & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=Delimited"""

Set rs = cn.Execute("Select * From [" & FILE & "]")

While Not rs.EOF
  'if HDR=YES then get columns like this:
  val = rs.Fields.Item("UserName").Value 'where UserName is a header
  'if HDR=NO then get columns like this:
  val = rs.Fields.Item("F2").Value 'gets the second column
  If Not isNull(val) Then
    'do something with the value
    WScript.Echo val
  End If
  rs.MoveNext
Wend 

cn.close
Set cn = Nothing
Set rs = Nothing
WScript.Quit

Hopefully this will point you in the right direction and prevent a 'delimiter headache'