DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Reading CSV file with VBScript Split function

  1. #1
    Mel Guest

    Reading CSV file with VBScript Split function


    I am trying to update a database with a CSV file. I am using the VBScript
    Split function to put the values into an array. At first everything seemed
    to work, but then I noticed one of the fields (an address field) contains
    commas on a few of the records.

    When I open up the CSV file in Excel (i.e. by clicking on it in Windows Explorer),
    then all the columns come out correctly. In other words it is not confused
    by the additional commas. However when I process the file into a database
    (using VBScript Split function to put each line into an array defining a
    comma as the delimiter), the result is wrong because of the commas in the
    field values. Has anyone got any ideas as to why Excel can differentiate
    between the commas, but my code can't? I fear I'm doing something stupid.

    TIA
    Mel

  2. #2
    Troy Guest

    Re: Reading CSV file with VBScript Split function


    Mel, Try the following code. There may be an easier way, but I had to write
    my own.

    Read each line from your text file and pass it to the following function.
    Each text fields should be quoted to encapsulate the commas. when complete,
    a() will be an array of each field within a line.

    Troy Black
    Web-Ease, Inc
    www.web-ease.com


    dim a()
    function parseLine2(theLine)
    ' Troy Black, Web-Ease, Inc., www.web-ease.com
    count = 0
    quoted = false
    quoteCount = 0

    theLine = theLine & ","
    for x = 1 to len(theLine)

    r = mid(theLine,x,1)
    if r = chr(34) then
    quoted = true
    quoteCount = quoteCount + 1
    end if

    if quoteCount = 2 then
    if not mid(theLine, x + 1, 1) = "," then
    quoteCount = 1
    else
    quoted = false
    quoteCount = 0
    end if
    end if

    s = s & r

    if quoted = false and r = "," then
    if inStr(1,s,chr(34)) then
    s = replace(s,chr(34),"")
    end if
    redim preserve a(count)
    a(count) = trim(left(s,len(s) - 1))
    if a(count) = "" then a(count) = " "
    s = ""
    count = count + 1
    end if
    Next
    end function


    "Mel" <mel777@hotmail.com> wrote:
    >
    >I am trying to update a database with a CSV file. I am using the VBScript
    >Split function to put the values into an array. At first everything seemed
    >to work, but then I noticed one of the fields (an address field) contains
    >commas on a few of the records.
    >
    >When I open up the CSV file in Excel (i.e. by clicking on it in Windows

    Explorer),
    >then all the columns come out correctly. In other words it is not confused
    >by the additional commas. However when I process the file into a database
    >(using VBScript Split function to put each line into an array defining a
    >comma as the delimiter), the result is wrong because of the commas in the
    >field values. Has anyone got any ideas as to why Excel can differentiate
    >between the commas, but my code can't? I fear I'm doing something stupid.
    >
    >TIA
    >Mel



  3. #3
    Christ Guest

    Re: Reading CSV file with VBScript Split function


    I suggest that you change the options of your excel, to use the ";" as separador
    instead of the ",".

    Now if to use the function in the following way:

    Array_Var = LineFile.split(";")

    "Mel" <mel777@hotmail.com> wrote:
    >
    >I am trying to update a database with a CSV file. I am using the VBScript
    >Split function to put the values into an array. At first everything seemed
    >to work, but then I noticed one of the fields (an address field) contains
    >commas on a few of the records.
    >
    >When I open up the CSV file in Excel (i.e. by clicking on it in Windows

    Explorer),
    >then all the columns come out correctly. In other words it is not confused
    >by the additional commas. However when I process the file into a database
    >(using VBScript Split function to put each line into an array defining a
    >comma as the delimiter), the result is wrong because of the commas in the
    >field values. Has anyone got any ideas as to why Excel can differentiate
    >between the commas, but my code can't? I fear I'm doing something stupid.
    >
    >TIA
    >Mel



  4. #4
    Join Date
    Nov 2007
    Posts
    3

    Exclamation Using VBScript Split function for CSV

    Good Day,

    I have a large CSV file with test log information. I would like to use VBScript's split function to extract data from the CSV file into an array. I have reviewed the following script that was posted by Troy Black but how does it work? It ran with no errors but there was no output.

    dim a()
    function parseLine2(theLine)
    ' Troy Black, Web-Ease, Inc., www.web-ease.com
    count = 0
    quoted = false
    quoteCount = 0

    theLine = theLine & ","
    for x = 1 to len(theLine)

    r = mid(theLine,x,1)
    if r = chr(34) then
    quoted = true
    quoteCount = quoteCount + 1
    end if

    if quoteCount = 2 then
    if not mid(theLine, x + 1, 1) = "," then
    quoteCount = 1
    else
    quoted = false
    quoteCount = 0
    end if
    end if

    s = s & r

    if quoted = false and r = "," then
    if inStr(1,s,chr(34)) then
    s = replace(s,chr(34),"")
    end if
    redim preserve a(count)
    a(count) = trim(left(s,len(s) - 1))
    if a(count) = "" then a(count) = " "
    s = ""
    count = count + 1
    end if
    Next
    end function

    Regards,
    Keith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center