Windows Vista Forums

Re: Remove extra Double codes from CSV Files (vb Script Language)

  1. #1


    Tom Lavedas Guest

    Re: Remove extra Double codes from CSV Files (vb Script Language)

    On Nov 16, 6:20*am, Learn4Develop
    <Learn4Deve...@newsgroup> wrote:

    > On daily bases I have received files with double quotes comma separated, but
    > in some records I found 2 times double quotes and some time different squence
    > of double quotes placements.
    >
    > I just want to remove all unnecessary (extra) double quotes (see the
    > following values where i want to remove extra double quotes.
    >
    > "0123x",""Company D-Val"","Class D, sector N","DD5894"
    > "4894D",""Recycle" Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > "AB8679",""AB Ltd"",""Need" Clean Drive Way, stores","GF0347"
    >
    > I am looking the output like that.
    >
    > "0123x","Company D-Val","Class D, sector N","DD5894"
    > "4894D","Recycle Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > "AB8679","AB Ltd","Need Clean Drive Way, stores","GF0347"
    >
    {snip}

    It was tricky getting the indexing right, but this seemed to do what
    you want on the test sample you provided ...

    Do Until inFile.AtEndOfStream
    record = InFile.ReadLine
    nPos = Instr(record, Chr(34) & Chr(34))
    do while nPos > 0
    record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    nPos = Instr(nPos + 1, record, Chr(34))
    record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    nPos = Instr(nPos + 1, record, Chr(34) & Chr(34))
    loop
    outFile.WriteLine record
    Loop

    The approach removes the first of a pair of double quotes and the very
    next double quote that follows; thereby removing the starting and
    ending quotes that enclose text, when there are too many.
    _____________________
    Tom Lavedas

      My System SpecsSystem Spec

  2.   


  3. #2


    Learn4Develop Guest

    Re: Remove extra Double codes from CSV Files (vb Script Language)



    "Tom Lavedas" wrote:

    > On Nov 16, 6:20 am, Learn4Develop
    > <Learn4Deve...@newsgroup> wrote:

    > > On daily bases I have received files with double quotes comma separated, but
    > > in some records I found 2 times double quotes and some time different squence
    > > of double quotes placements.
    > >
    > > I just want to remove all unnecessary (extra) double quotes (see the
    > > following values where i want to remove extra double quotes.
    > >
    > > "0123x",""Company D-Val"","Class D, sector N","DD5894"
    > > "4894D",""Recycle" Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > > "AB8679",""AB Ltd"",""Need" Clean Drive Way, stores","GF0347"
    > >
    > > I am looking the output like that.
    > >
    > > "0123x","Company D-Val","Class D, sector N","DD5894"
    > > "4894D","Recycle Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > > "AB8679","AB Ltd","Need Clean Drive Way, stores","GF0347"
    > >
    > {snip}
    >
    > It was tricky getting the indexing right, but this seemed to do what
    > you want on the test sample you provided ...
    >
    > Do Until inFile.AtEndOfStream
    > record = InFile.ReadLine
    > nPos = Instr(record, Chr(34) & Chr(34))
    > do while nPos > 0
    > record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    > nPos = Instr(nPos + 1, record, Chr(34))
    > record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    > nPos = Instr(nPos + 1, record, Chr(34) & Chr(34))
    > loop
    > outFile.WriteLine record
    > Loop
    >
    > The approach removes the first of a pair of double quotes and the very
    > next double quote that follows; thereby removing the starting and
    > ending quotes that enclose text, when there are too many.
    > _____________________
    > Tom Lavedas
    > .
    >
    Hi Tom
    Thanks for the code, its working fine with the above criteria, but gives
    me error if there is Null value between double quotes ("") i.e.

    ("AB8679","""",""Need" Clean Drive Way, stores","")

    As i have some columns in CSV file where the value is Null "". How i can
    resolve it.

    Thanks again for your great help.


      My System SpecsSystem Spec

  4. #3


    Pegasus [MVP] Guest

    Re: Remove extra Double codes from CSV Files (vb Script Language)


    "Learn4Develop" <Learn4Develop@newsgroup> wrote in message
    news:70A7DE23-C1A4-417B-9B34-E0BA09CDF49C@newsgroup

    >
    >
    > "Tom Lavedas" wrote:
    >

    >> On Nov 16, 6:20 am, Learn4Develop
    >> <Learn4Deve...@newsgroup> wrote:

    >> > On daily bases I have received files with double quotes comma
    >> > separated, but
    >> > in some records I found 2 times double quotes and some time different
    >> > squence
    >> > of double quotes placements.
    >> >
    >> > I just want to remove all unnecessary (extra) double quotes (see the
    >> > following values where i want to remove extra double quotes.
    >> >
    >> > "0123x",""Company D-Val"","Class D, sector N","DD5894"
    >> > "4894D",""Recycle" Rubbish, C class","Class D, Sector F, Block
    >> > N","D870GH"
    >> > "AB8679",""AB Ltd"",""Need" Clean Drive Way, stores","GF0347"
    >> >
    >> > I am looking the output like that.
    >> >
    >> > "0123x","Company D-Val","Class D, sector N","DD5894"
    >> > "4894D","Recycle Rubbish, C class","Class D, Sector F, Block
    >> > N","D870GH"
    >> > "AB8679","AB Ltd","Need Clean Drive Way, stores","GF0347"
    >> >
    >> {snip}
    >>
    >> It was tricky getting the indexing right, but this seemed to do what
    >> you want on the test sample you provided ...
    >>
    >> Do Until inFile.AtEndOfStream
    >> record = InFile.ReadLine
    >> nPos = Instr(record, Chr(34) & Chr(34))
    >> do while nPos > 0
    >> record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    >> nPos = Instr(nPos + 1, record, Chr(34))
    >> record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    >> nPos = Instr(nPos + 1, record, Chr(34) & Chr(34))
    >> loop
    >> outFile.WriteLine record
    >> Loop
    >>
    >> The approach removes the first of a pair of double quotes and the very
    >> next double quote that follows; thereby removing the starting and
    >> ending quotes that enclose text, when there are too many.
    >> _____________________
    >> Tom Lavedas
    >> .
    >>
    >
    > Hi Tom
    > Thanks for the code, its working fine with the above criteria, but gives
    > me error if there is Null value between double quotes ("") i.e.
    >
    > ("AB8679","""",""Need" Clean Drive Way, stores","")
    >
    > As i have some columns in CSV file where the value is Null "". How i can
    > resolve it.
    >
    > Thanks again for your great help.
    >
    Try my suggestion. It should beable to cope with empty fields.



      My System SpecsSystem Spec

  5. #4


    Tom Lavedas Guest

    Re: Remove extra Double codes from CSV Files (vb Script Language)

    On Nov 17, 11:46*am, Learn4Develop
    <Learn4Deve...@newsgroup> wrote:

    > "Tom Lavedas" wrote:

    > > On Nov 16, 6:20 am, Learn4Develop
    > > <Learn4Deve...@newsgroup> wrote:

    > > > On daily bases I have received files with double quotes comma separated, but
    > > > in some records I found 2 times double quotes and some time differentsquence
    > > > of double quotes placements.
    >

    > > > I just want to remove all unnecessary (extra) double quotes (see the
    > > > following values where i want to remove extra double quotes.
    >

    > > > "0123x",""Company D-Val"","Class D, sector N","DD5894"
    > > > "4894D",""Recycle" Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > > > "AB8679",""AB Ltd"",""Need" Clean Drive Way, stores","GF0347"
    >

    > > > I am looking the output like that.
    >

    > > > "0123x","Company D-Val","Class D, sector N","DD5894"
    > > > "4894D","Recycle Rubbish, C class","Class D, Sector F, Block N","D870GH"
    > > > "AB8679","AB Ltd","Need Clean Drive Way, stores","GF0347"
    >
    {snip}

    >
    > Hi Tom
    > * Thanks for the code, its working fine with the above criteria, but gives
    > me error if there is Null value between double quotes ("") i.e.
    >
    > ("AB8679","""",""Need" Clean Drive Way, stores","")
    >
    > As i have some columns in CSV file where the value is Null "". How i can
    > resolve it.
    >
    > Thanks again for your great help.
    Try this (though Pegusus' approach is also workable) ...

    Do Until inFile.AtEndOfStream
    record = InFile.ReadLine
    nPos = Instr(record, Chr(34) & Chr(34))
    do while nPos > 0
    record = Left(record, nPos) & Mid(record, nPos + 2)
    nPos = Instr(nPos + 1, record, Chr(34))
    if nPos > 0 then
    record = Left(record, nPos - 1) & Mid(record, nPos + 1)
    nPos = Instr(nPos + 1, record, Chr(34) & Chr(34))
    else
    record = record & Chr(34)
    end if
    loop
    outFile.WriteLine record
    Loop
    _____________________
    Tom Lavedas

      My System SpecsSystem Spec

  6. #5


    Todd Vargo Guest

    Re: Remove extra Double codes from CSV Files (vb Script Language)

    Learn4Develop wrote:

    > Hi Tom
    > Thanks for the code, its working fine with the above criteria, but gives
    > me error if there is Null value between double quotes ("") i.e.
    >
    > ("AB8679","""",""Need" Clean Drive Way, stores","")
    >
    > As i have some columns in CSV file where the value is Null "". How i can
    > resolve it.
    >
    > Thanks again for your great help.
    ISTM, only strings containing a comma should need quoted. But since you say
    it gives you an error, it begs the questions. What is giving the error? And
    what is creating these incorrect csv files?

    --
    Todd Vargo
    (Post questions to group only. Remove "z" to email personal messages)


      My System SpecsSystem Spec


Re: Remove extra Double codes from CSV Files (vb Script Language)
Similar Threads
Thread Forum
Re: Remove extra Double codes from CSV Files (vb Script Language) VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language) VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language) VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language) VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language) VB Script