Solving s CSV dilemma
Timo Salmi
to Excel. Consider the following, simple CSV file myfile.csv
a11,a12,a13
a21,,a23
The desired output of s script under consideration would be
a11
a12
a13
a21
..
a23
However, the script
for /f "tokens=1-3 delims=," %%a in ('type "myfile.csv"') do (
set s1_=%%a
set s2_=%%b
set s3_=%%c
if defined s1_ (echo !s1_!) else (echo ..)
if defined s2_ (echo !s2_!) else (echo ..)
if defined s3_ (echo !s3_!) else (echo ..)
)
will produce
a11
a12
a13
a21
a23
..
Is there a way out? One option is inserting a "-" within the empty ,,
before going through the file
for /f "delims=" %%a in ('type "myfile.csv"') do (
set s_=%%a
echo !s_:,,=,-,!
)>>myfile.tmp
for /f "tokens=1-3 delims=," %%a in ('type "myfile.tmp"') do (
set s1_=%%a
set s2_=%%b
set s3_=%%c
if not [!s1_!]==[-] (echo !s1_!) else (echo ..)
if not [!s2_!]==[-] (echo !s2_!) else (echo ..)
if not [!s3_!]==[-] (echo !s3_!) else (echo ..)
)
Now the oputput is the desired
a11
a12
a13
a21
..
a23
All the best, Timo
--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance ; University of Vaasa
mailto:t...@uwasa.fi <http://www.uwasa.fi/~ts/> ; FIN-65101, Finland
Useful script files and tricks ftp://garbo.uwasa.fi/pc/link/tscmd.zip
foxidrive
I basically did the same thing, without expanded variable support.
@echo off
for /f "delims=" %%a in ('type "myfile.csv"') do (
call :next "%%a"
)
goto :EOF
:next
set var=%~1
set var=%var:,,=,..,%
for /f "tokens=1-3 delims=," %%a in ("%var%") do (
echo %%a
echo %%b
echo %%c
)
Timo Salmi
> On Sat, 24 Sep 2005 05:05:42 +0000 (UTC), Timo Salmi wrote:
> > to Excel. Consider the following, simple CSV file myfile.csv
> > a11,a12,a13
> > a21,,a23
> > The desired output of s script under consideration would be
> > a11
> > a12
> > a13
> > a21
> > ..
> > a23
> I basically did the same thing, without expanded variable support.
Or with the expanded support but now avoiding the temporary file
used in my original posting
@echo off & setlocal enableextensions enabledelayedexpansion
for /f "delims=" %%a in ('type "myfile.csv"') do (
set s_=%%a
set s_=!s_:,,=,-,!
for /f "tokens=1-3 delims=," %%a in ("!s_!") do (
set s1_=%%a
set s2_=%%b
set s3_=%%c
if not [!s1_!]==[-] (echo !s1_!) else (echo ..)
if not [!s2_!]==[-] (echo !s2_!) else (echo ..)
if not [!s3_!]==[-] (echo !s3_!) else (echo ..)
)
echo.
)
endlocal & goto :EOF
BTW, the item will be called
118) How to handle irregular empty fields of a CSV file?
Todd Vargo
"Timo Salmi" <t...@uwasa.fi> wrote in message
news:dh2mr6$cer$1...@haavi.uwasa.fi...
> The CSV file format is useful for many tasks because of its relation
> to Excel. Consider the following, simple CSV file myfile.csv
>
> a11,a12,a13
> a21,,a23
>
> The desired output of s script under consideration would be
>
> a11
> a12
> a13
> a21
> ..
> a23
@echo off
echo> "%temp%.\tmp.vbs" s = WScript.StdIn.ReadAll
echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
echo>>"%temp%.\tmp.vbs" s = Replace(s, ",", vbCRLF)
echo>>"%temp%.\tmp.vbs" Wscript.Echo s
type myfile.csv | cscript /nologo "%temp%.\tmp.vbs"
del "%temp%.\tmp.vbs"
--
Todd Vargo (double "L" to reply by email)
Timo Salmi
> > On Sat, 24 Sep 2005 05:05:42 +0000 (UTC), Timo Salmi wrote:
> > > to Excel. Consider the following, simple CSV file myfile.csv
> > > a11,a12,a13
> > > a21,,a23
The earlier solutions are deficient, since there is a subtle catch
with the odd/even number of ,,
Consider
a11,a12,a13,a14,a25,a26
a21,,,,,a26
The substitution needs to be done twice to cover both the
eventualities:
@echo off & setlocal enableextensions enabledelayedexpansion
for /f "delims=" %%a in ('type "myfile.csv"') do (
set s_=%%a
set s_=!s_:,,=,-,!
set s_=!s_:,,=,-,!
for /f "tokens=1-6 delims=," %%a in ("!s_!") do (
set s1_=%%a
set s2_=%%b
set s3_=%%c
set s4_=%%d
set s5_=%%e
set s6_=%%f
if not [!s1_!]==[-] (echo !s1_!) else (echo ..)
if not [!s2_!]==[-] (echo !s2_!) else (echo ..)
if not [!s3_!]==[-] (echo !s3_!) else (echo ..)
if not [!s4_!]==[-] (echo !s4_!) else (echo ..)
if not [!s5_!]==[-] (echo !s5_!) else (echo ..)
if not [!s6_!]==[-] (echo !s6_!) else (echo ..)
)
echo.
)
endlocal & goto :EOF
All the best, Timo
Timo Salmi
> > a21,,a23
> @echo off
> echo> "%temp%.\tmp.vbs" s = WScript.StdIn.ReadAll
> echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
> echo>>"%temp%.\tmp.vbs" s = Replace(s, ",", vbCRLF)
> echo>>"%temp%.\tmp.vbs" Wscript.Echo s
> type myfile.csv | cscript /nologo "%temp%.\tmp.vbs"
> del "%temp%.\tmp.vbs"
Nice alternative, but it has the same odd/even problem as the
earlier solutions, including mine. You'll see it of you try it on
a11,a12,a13,a14,a25,a26,a27,a28
a21,,a23,a24,a25,,,a28
a31,,a33,a34,,,,a38
It is an insidious and an unexpected catch.
The remedy is to have the
echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
line in duplicate.
I even tested the same substitution situation with sed:
sed -e "s/,,/,..,/g" myfile.csv
Also this has to be duplicated to guarantee that all ,, pairs become
substituted. Strange, indeed.
Timo Salmi
> Todd Vargo <todd...@alvantage.com> wrote:
(Snip the VBS solution)
> Nice alternative, but it has the same odd/even problem as the
> earlier solutions, including mine. You'll see it of you try it on
> a11,a12,a13,a14,a25,a26,a27,a28
> a21,,a23,a24,a25,,,a28
> a31,,a33,a34,,,,a38
Let me clarify simplifying the task with an another VBS aided script
demonstration:
@echo off & setlocal enableextensions enabledelayedexpansion
::
:: Build a test file
echo a11,a12,a13,a14,a25,a26,a27,a28>mytest.csv
echo a21,,a23,a24,a25,,,a28>>mytest.csv
echo a31,,a33,a34,,,,a38>>mytest.csv
::
:: Build a Visual Basic Script
set skip=
set vbs_=%temp%\tmp$$$.vbs
findstr "'%skip%VBS" "%~f0" > %vbs_%
::
:: Run the script with Microsoft Windows Script Host Version 5.6
cscript //nologo %vbs_% < mytest.csv
::
:: Clean up
for %%f in (%vbs_% mytest.csv) do del %%f
endlocal & goto :EOF
'
'.............................................
'The Visual Basic Script
'
Do While Not WScript.StdIn.AtEndOfStream 'VBS
str = WScript.StdIn.ReadLine 'VBS
str = Replace (str, ",,",",-,") 'VBS
WScript.StdOut.WriteLine str 'VBS
Loop 'VBS
The output will be
a11,a12,a13,a14,a25,a26,a27,a28
a21,-,a23,a24,a25,-,,a28
a31,-,a33,a34,-,,-,a38
You'll notice the missing substitution.
If one duplicates
str = Replace (str, ",,",",-,") 'VBS
then we'll have
a11,a12,a13,a14,a25,a26,a27,a28
a21,-,a23,a24,a25,-,-,a28
a31,-,a33,a34,-,-,-,a38
It works, but it is baffling.
Timo Salmi
> > a21,,a23
> @echo off
> echo> "%temp%.\tmp.vbs" s = WScript.StdIn.ReadAll
> echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
> echo>>"%temp%.\tmp.vbs" s = Replace(s, ",", vbCRLF)
> echo>>"%temp%.\tmp.vbs" Wscript.Echo s
> type myfile.csv | cscript /nologo "%temp%.\tmp.vbs"
> del "%temp%.\tmp.vbs"
That is a nicely concise solution for the task I posed. To proceed
further to gain full control of the indivudual fields (which often
is what csv parsing is about in practice) I have a more cumbersome
VBS alternative. (It also takes care of the odd/even ,, pair problem
discussed previously. Even if the current example is too small in
fields to evoke that problem.)
@echo off & setlocal enableextensions
::
:: Make a demonstration test file
echo a11,a12,a13>mytest.csv
echo a21,,a23>>mytest.csv
::
:: Build a Visual Basic Script
set skip=
set vbs_=%temp%\tmp$$$.vbs
findstr "'%skip%VBS" "%~f0" > %vbs_%
::
:: Run the script with Microsoft Windows Script Host Version 5.6
cscript //nologo %vbs_% < mytest.csv
::
:: Clean up
for %%f in (%vbs_% mytest.csv) do del %%f
endlocal & goto :EOF
'
'.............................................
'The Visual Basic Script
'
Do While Not WScript.StdIn.AtEndOfStream 'VBS
str = WScript.StdIn.ReadLine 'VBS
str = str + "," 'VBS
str = Replace (str, ",,",",-,") 'VBS
str = Replace (str, ",,",",-,") 'VBS
'
p1 = Instr (1, str, ",", 1) 'VBS
str1 = Mid (str, 1, p1-1) 'VBS
If str1 <> "-" Then 'VBS
WScript.StdOut.WriteLine str1 'VBS
Else 'VBS
WScript.StdOut.WriteLine ".." 'VBS
End If 'VBS
'
p2 = Instr (p1+1, str, ",", 1) 'VBS
str2 = Mid (str, p1+1, p2-p1-1) 'VBS
If str2 <> "-" Then 'VBS
WScript.StdOut.WriteLine str2 'VBS
Else 'VBS
WScript.StdOut.WriteLine ".." 'VBS
End If 'VBS
'
p3 = Instr (p2+1, str, ",", 1) 'VBS
str3 = Mid (str, p2+1, p3-p2-1) 'VBS
If str3 <> "-" Then 'VBS
WScript.StdOut.WriteLine str3 'VBS
Else 'VBS
WScript.StdOut.WriteLine ".." 'VBS
End If 'VBS
'
WScript.StdOut.WriteLine 'VBS
Loop 'VBS
The output is
a11
a12
a13
a21
..
a23
All the best, Timo
Markko Meriniit
"Timo Salmi" <t...@uwasa.fi> kirjutas sõnumis news:
dh5cai$cfk$1...@haavi.uwasa.fi...
> The output will be
> a11,a12,a13,a14,a25,a26,a27,a28
> a21,-,a23,a24,a25,-,,a28
> a31,-,a33,a34,-,,-,a38
> You'll notice the missing substitution.
>
> If one duplicates
> str = Replace (str, ",,",",-,") 'VBS
> then we'll have
> a11,a12,a13,a14,a25,a26,a27,a28
> a21,-,a23,a24,a25,-,-,a28
> a31,-,a33,a34,-,-,-,a38
>
> It works, but it is baffling.
I guess its not baffling if string replacement works this way with four
commas(,,,,) . Takes first comma pair ,, and replaces it with ,-, result is
,-,,, To program the first pair is done for an it doesn't start new search
from start but continues from next position,that is from third comma an from
there the 3rd and 4th commas are next pair to replace an end result will be
,-,,-,
Its not only cmd specific, you can get some text editor an try to do same
search&replace in there and see what results you get :-)
Markko
Timo Salmi
> > str = Replace (str, ",,",",-,") 'VBS
> > then we'll have
> > It works, but it is baffling.
> from start but continues from next position,that is from third comma an from
> there the 3rd and 4th commas are next pair to replace an end result will be
> ,-,,-,
> Its not only cmd specific, you can get some text editor an try to do same
> search&replace in there and see what results you get :-)
Tested with a cople of editors, including Notepad. Indeed. It takes
two passes even with a text editor. One always learns new things!
Todd Vargo
> Todd Vargo <todd...@alvantage.com> wrote:
> > "Timo Salmi" <t...@uwasa.fi> wrote in message
> > > a11,a12,a13
> > > a21,,a23
>
> > @echo off
> > echo> "%temp%.\tmp.vbs" s = WScript.StdIn.ReadAll
> > echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
> > echo>>"%temp%.\tmp.vbs" s = Replace(s, ",", vbCRLF)
> > echo>>"%temp%.\tmp.vbs" Wscript.Echo s
> > type myfile.csv | cscript /nologo "%temp%.\tmp.vbs"
> > del "%temp%.\tmp.vbs"
>
> Nice alternative, but it has the same odd/even problem as the
> earlier solutions, including mine. You'll see it of you try it on
> a11,a12,a13,a14,a25,a26,a27,a28
> a21,,a23,a24,a25,,,a28
> a31,,a33,a34,,,,a38
> It is an insidious and an unexpected catch.
Quite. I only used the original example and had not considered testing with
multiple consecutive commas.
>
> The remedy is to have the
> echo>>"%temp%.\tmp.vbs" s = Replace(s, ",,", ",..,")
> line in duplicate.
Yes. For this problem (repeating character replacement) we need to run this
command twice.
>
> I even tested the same substitution situation with sed:
> sed -e "s/,,/,..,/g" myfile.csv
> Also this has to be duplicated to guarantee that all ,, pairs become
> substituted. Strange, indeed.
Not really strange at all. Like VBS, once a character is considered, the
program moves to the next character to begin it's next search/replacement. I
suspect all replacement routines function the same. Try it various word
processors as well.
datam...@gmail.com
stream editor) works too. The way around it is to use a regexp that
does not consume the trailing comma, using an EasyPattern this would
be:
,[ mustendwith( ',' ) ]
or with a perl pattern:
,(?=,)
Bruce Uttley
Markko Meriniit <sas...@gmail.com> wrote:
>
> I guess its not baffling if string replacement works this way with four
>commas(,,,,) . Takes first comma pair ,, and replaces it with ,-, result is
>,-,,, To program the first pair is done for an it doesn't start new search
>from start but continues from next position,that is from third comma an from
>there the 3rd and 4th commas are next pair to replace an end result will be
>,-,,-,
> Its not only cmd specific, you can get some text editor an try to do same
>search&replace in there and see what results you get :-)
>
>Markko
>
A trailing comma implies a missing argument as well:
a1,a2 <-- two csv fields
a1,a2, <== three csv fields with the third null
Timo Salmi
Bruce Uttley <b...@ist.uwaterloo.ca> wrote:
> A trailing comma implies a missing argument as well:
> a1,a2 <-- two csv fields
> a1,a2, <== three csv fields with the third null
Yes, I included that eventuality into the VBS solution version.
Note, however, that the number of fields one has in the CSV file
usually is known when writing such scripts as tackled.
Dr John Stockton
Sep 2005 12:48:52, seen in news:alt.msdos.batch.nt, Markko Meriniit
<sas...@gmail.com> posted :
>
> I guess its not baffling if string replacement works this way with four
>commas(,,,,) . Takes first comma pair ,, and replaces it with ,-, result is
>,-,,, To program the first pair is done for an it doesn't start new search
>from start but continues from next position,that is from third comma an from
>there the 3rd and 4th commas are next pair to replace an end result will be
>,-,,-,
> Its not only cmd specific, you can get some text editor an try to do same
>search&replace in there and see what results you get :-)
If, after a search & replace, the next search started anywhere before
the final new character, there would often be the possibility of
infinite repetition.
The quite reasonable-looking
s/Meriniit/Markko Meriniit/
could then generate an infinite number of "Markko"s, which is probably
more than is needed or even wise.
--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk DOS 3.3, 6.20; Win98. ©
Web <URL:http://www.merlyn.demon.co.uk/> - FAQqish topics, acronyms & links.
PAS EXE TXT ZIP via <URL:http://www.merlyn.demon.co.uk/programs/00index.htm>
My DOS <URL:http://www.merlyn.demon.co.uk/batfiles.htm> - also batprogs.htm.