• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

T

Tom Lavedas

#1
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
 
L

Learn4Develop

#2
"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.
 
P

Pegasus [MVP]

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

Tom Lavedas

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

Todd Vargo

#5
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)