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. #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

  3. #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

  4. #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

  5. #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)
"Learn4Develop" <Learn4Develop@newsgroup> wrote in message news:C072F055-9B82-4D2D-A4D2-216403F47814@newsgroup I agree with Christoph that either...
VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language)
Learn4Develop schrieb: If you want to implement the appropriate algorithm, you cannot use a replace-mechanism based on literals, you always...
VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language)
Learn4Develop wrote: but squence record = Replace(record, Chr(34)&Chr(34), Chr(34)) 'replace "" with single " -- Todd Vargo (Post questions...
VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language)
"Learn4Develop" <Learn4Develop@newsgroup> wrote in message news:C072F055-9B82-4D2D-A4D2-216403F47814@newsgroup The rubbish line looks like...
VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language)
Strange - my initial reply appears to have disappeared into the never-never. I'll try to post it once more: You could do it like this for every...
VB Script
Re: Remove extra Double codes from CSV Files (vb Script Language)
"Learn4Develop" <Learn4Develop@newsgroup> wrote in message news:C072F055-9B82-4D2D-A4D2-216403F47814@newsgroup You could do it like this for...
VB Script
Script to remove space in all files in all subfolders
I have a folder c:\pictures, that has a lot of subfolders that contain subfolders. I want to go through every .jpg in and below that pictures...
VB Script