DosTips.com

Posted: **24 Oct 2011 15:26**

This script does what I want unless the text file it processes contains exclamation marks.

@ECHO OFF
setlocal enabledelayedexpansion

SET FILE=TESTx.CSV

IF EXIST %FILE% DEL %FILE%
IF EXIST _%FILE% DEL _%FILE%

SET N=200
for /f "tokens=1* delims=," %%d in ('type ~%FILE%') do (
  ECHO %%d,%%e>> _%FILE%
  SET /A N+=1
  ECHO !N!,%%e>> %FILE%
)
FC ~%FILE% _%FILE%
PAUSE
GOTO :EOF

This is a tiny portion of the input file ~TESTx.CSV

Code: Select all

16,"sysexp-x64.zip","http://www.nirsoft.net/utils/sysexp-x64.zip","file:///E:/Downloads/sysexp-x64.zip","",1315585017060000,1315585020958000,1,,"",52836,52836,"application/zip","",0,0
17,"!Default_v200.zip","http://www.blackviper.com/!Default_v200.zip","file:///E:/Downloads/!Default_v200.zip","",1315655804198000,1315655821601000,1,,"",1263,1263,"application/zip","",0,0
18,"!Safe_v200.zip","http://www.blackviper.com/!Safe_v200.zip","file:///E:/Downloads/!Safe_v200.zip","",1315655839792000,1315655852243000,1,,"",1295,1295,"application/zip","",0,0
19,"PDF.zip","http://c1236872.com/PDF.zip","file:///E:/Downloads/PDFVManual.zip","",1315684437750000,1315684455821000,1,"http://www.tracker/pdf","",7002554,7002554,"application/x-zip","",0,0
20,"Port.zip","http://c1236872.com/Port.zip","file:///E:/Downloads/Port.zip","",1315684645421000,1315684664289000,1,"http://www.tracker/pdf-view","",7304310,7304310,"application/x-zip","",0,0

The desired output TEST.CSV should be exactly the same excepting that the index number at the start of each line should be in sequence starting at 201
(Firefox downloads history is broken and I wish to join the pieces.)

As a sanity test the file _TEST.CSV does the same operations on the file, but prepends the text with the original index count,
and this shows how much is removed from lines 17 and 18

Code: Select all

16,"sysexp-x64.zip","http://www.nirsoft.net/utils/sysexp-x64.zip","file:///E:/Downloads/sysexp-x64.zip","",1315585017060000,1315585020958000,1,,"",52836,52836,"application/zip","",0,0
17,"//www.blackviper.com////E:/Downloads/Default_v200.zip","",1315655804198000,1315655821601000,1,,"",1263,1263,"application/zip","",0,0
18,"//www.blackviper.com////E:/Downloads/Safe_v200.zip","",1315655839792000,1315655852243000,1,,"",1295,1295,"application/zip","",0,0
19,"PDF.zip","http://c1236872.com/PDF.zip","file:///E:/Downloads/PDFVManual.zip","",1315684437750000,1315684455821000,1,"http://www.tracker/pdf","",7002554,7002554,"application/x-zip","",0,0
20,"Port.zip","http://c1236872.com/Port.zip","file:///E:/Downloads/Port.zip","",1315684645421000,1315684664289000,1,"http://www.tracker/pdf-view","",7304310,7304310,"application/x-zip","",0,0

A crude solution is to avoid delayed expansion and replace

SET /A N+=1
ECHO !N!,%%e>> %FILE%

with

CALL :New_Index %%e

and create a function

Code: Select all

:New_Index
  SET /A N+=1
  ECHO %N%,%1>> %FILE%
  GOTO :EOF

But I am hoping for something more elegant,
I still remember DOS making the floppy disc read the entire script to get back to where it was at ! !

Regards
Alan

Posted: **24 Oct 2011 17:00**

The simplest solution with your current code base is to transfer %%e to a variable before enabling and disabling delayed expansion within the loop:

Code: Select all

@ECHO OFF
setlocal disableDelayedExpansion

SET FILE=TESTx.CSV

IF EXIST %FILE% DEL %FILE%
IF EXIST _%FILE% DEL _%FILE%

SET N=200
for /f "tokens=1* delims=," %%d in ('type ~%FILE%') do (
  ECHO %%d,%%e>> _%FILE%
  SET /A N+=1
  set "ln=%%e"
  setlocal enableDelayedExpansion
  ECHO !N!,!ln!>> %FILE%
  endlocal
)
FC ~%FILE% _%FILE%
PAUSE
GOTO :EOF

Very minor point - I believe it is more efficient to use a file set instead of the results of the TYPE command, probably with USEBACKQ option in case you want to quote the file name.

If you are sure your text file uses Windows style <CR><LF> and not Unix style <LF>, then you can avoid the delayed expansion problem entirely by using methods found in the New technic: set /p can read multiple lines from a file thread.

Dave Benham

Posted: **25 Oct 2011 00:38**

IMHO simplest solution is CALL

Code: Select all

@ECHO OFF
setlocal &rem enabledelayedexpansion

SET FILE=TESTx.CSV

IF EXIST %FILE% DEL %FILE%
IF EXIST _%FILE% DEL _%FILE%

SET N=200
for /f "tokens=1* delims=," %%d in ('type ~%FILE%') do call :process "%%d" "%%e"
FC ~%FILE% _%FILE%
PAUSE
GOTO :EOF

:process
  >> _%FILE% ECHO %~1,%~2
  SET /A N+=1
  >> %FILE% ECHO %N%,%~2
GOTO :EOF

Posted: **25 Oct 2011 02:04**

Hi !k,

IMHO call is a bad idea, as it is slow and it change the content of %%d and %%e (like ^^"^^"^^) while calling the function.
And it can fail with some contents like &"&.
You got also problems in the :process function with expanding the %1 and %2

Therefore I would prefere the solution of dbenham. :wink:

jeb

Posted: **25 Oct 2011 02:18**

Many thanks Dave, that works perfectly for me.

Question about the second line "setlocal disableDelayedExpansion"
When is it needed ?
I have never been able to use !var! delay expansion unless I specifically enable it.
Is that line to cover situations where I believe a registry hack activates all the capabilities that Command.com never had ?

A few years ago I had difficulty using USEBACKQ, but I might reconsider that.

I have used the Firefox "Addon" SqliteManager to export downloads.sqlite as both cvs and sql so I may append one portion that has been broken from a previous portion.
The first thing that stabbed me in the eyes was horrendous Unix style <LF> instead of <CR><LF>.
Notepad was no help at all.
Fortunately I also have portable MetaPad would allowed me to see and understand.

Until I tried it I did not know whether the remainder in %%e after breaking off %%d was going to stop at unix <LF>,
or whether it would continue through to the end of the file.
Then my concern became that parsing Unix <LF> text with Windows CMD.EXE might result in an output with <CR><LF> instead of <LF>,
and I did not know if Firefox would object to extraneous <CR> along with each <LF>.

Those are the reasons why I initially decided to create a "sanity check" file that had the same "parse and join" processing of the desired end final file.
I now find that as I feared, what I wanted to be identical did in fact have a <CR><LF> at every end of line,
but Firefox seems to survive regardless.

Regards
Alan

Posted: **25 Oct 2011 02:33**

Hi !k

I still hear in my mind a floppy disc reading an entire BAT script to the end and around again from the start when I think of a CALL :lol:

Posted: **25 Oct 2011 07:08**

alan_b wrote:Question about the second line "setlocal disableDelayedExpansion"
When is it needed ?
I have never been able to use !var! delay expansion unless I specifically enable it.
Is that line to cover situations where I believe a registry hack activates all the capabilities that Command.com never had ?

Certainly you need to enable delayed expansion to use !var!, but as you discovered, you don't want it enabled when you expand a FOR variable. The second line is probably not "needed" in your case since in most environments delayed expansion is disabled until you explicitly enable it. But it is possible that the batch file could be invoked after delayed expansion has been enabled (via registry, or after call to cmd.exe with appropriate option, etc.)

alan_b wrote:A few years ago I had difficulty using USEBACKQ, but I might reconsider that.

USEBACKQ works great in Vista and beyond. It works great with XP too as long as you are using a quoted file set. But there is a nasty XP bug that can cause intermittent problems if you use USEBACKQ with a 'string' containing unquoted/un-escaped token delimiters like <space>, <semicolon>, <comma>, <equal> or <LF>.

alan_b wrote:The first thing that stabbed me in the eyes was horrendous Unix style <LF> instead of <CR><LF>.

Too bad - that rules out the SET /P replacement for FOR /F. You should still check out the link in my initial response - it is very cool

Dave Benham

Posted: **25 Oct 2011 10:21**

Thanks Dave

I will play safe and keep that redundant "setlocal disableDelayedExpansion" in case I ever call this script from an "enabled" environment.

I am now happily using Windows 7 Ultimate.
My problems with USEBACKQ were under XP Home,
and I remember trying to cut and paste suitable backward slanted single quotes from the Windows / Accessories / Character Map.

Regards
Alan

Posted: **26 Oct 2011 13:14**

@ 1k
WHOOPS - A PROBLEM with the CALL solution.

I found that although the actual Firefox downloads.sqlite exports as a few hundred lines of CSV text,
the delayed expansion approach was much faster than I expected so I tested your simpler CALL version.

It worked perfectly on the example CSV I posted.
BIG WHOOPS on the real life code.

I simply changed 1 line in your code to process TESTy.CSV instead of TESTx.CSV
and this is my new ~TESTy.CSV

Code: Select all

18,"!Safe_v200.zip","http://www.blackviper.com/!Safe_v200.zip","file:///E:/Downloads/!Safe_v200.zip","",1315655839792000,1315655852243000,1,,"",1295,1295,"application/zip","",0,0
19,"PDF.zip","http://c1236872.com/PDF.zip","file:///E:/Downloads/PDFVManual.zip","",1315684437750000,1315684455821000,1,"http://www.tracker/pdf","",7002554,7002554,"application/x-zip","",0,0
20,"Port.zip","http://c1236872.com/Port.zip","file:///E:/Downloads/Port.zip","",1315684645421000,1315684664289000,1,"http://www.tracker/pdf-view","",7304310,7304310,"application/x-zip","",0,0
21,"DTLite4413-0173.exe","http://mirror07.soft24.com/getfile.php?p=http://disc-tools.com/1bbd44ed68712d32173b0eb376f5d449/DTLite4413-0173.exe","file:///E:/Downloads/DTLite4413-0173.exe","",1314462169570000,1314462196855000,1,"http://www.disc-tools.com/download/daemon","",11527296,11527296,"application/octet-stream","",0,0
22,"Acer-Aspire-4810TZ-Laptop-User-Manual.pdf","http://fs536.uploading.com/get_file/%3DYWPQOE%7CPHZbAWt8ZnISppP7GF0WlomKijVVpCSnLLyLOAH-b1F03gFafDg68YdMU6tXsgWqJx4ejsxZ%7CqFbD4vEGuwrjZDZ-Ao7u-MCi%7CPXKwDsyDIO5eGaUYL%7CdU9cLyjmnvbQ78oQ6CbCyc7DcjGRM%7CfLFrttCa9GkuJx5trXdqyXwhomQYsgPFjHt3pIdxiTZDe7crdOrDpNjfjXUEUT5YbNfIVfLiXxzrFZn3C4HBJcqnBQCuNbruPSQ3FHTJvD5zBjgvA-yRJ8IwH8bTfDfazKThES4Z2rAKwIi9pPHtnaM-JHXU0fRfuLDAMUE6nwmu2Rmzuo6deCJuRLpS5turLgFJdQnJzgGyk2Ufu2Au5zfcD4GUyHv7%7CWBKIe%7CuGO1Gd4hy-z","file:///E:/Downloads/Acer-Aspire-4810TZ-Laptop-User-Manual.pdf","",1314544738695000,1314544762353000,1,"http://uploading.com/files/get/78b1m9c4/","",1953536,1953536,"application/force-download","",0,0

And the result in _TESTy.CSV is

Code: Select all

18,"!Safe_v200.zip","http://www.blackviper.com/!Safe_v200.zip","file:///E:/Downloads/!Safe_v200.zip","",1315655839792000,1315655852243000,1,,"",1295,1295,"application/zip","",0,0
19,"PDF.zip","http://c1236872.com/PDF.zip","file:///E:/Downloads/PDFVManual.zip","",1315684437750000,1315684455821000,1,"http://www.tracker/pdf","",7002554,7002554,"application/x-zip","",0,0
20,"Port.zip","http://c1236872.com/Port.zip","file:///E:/Downloads/Port.zip","",1315684645421000,1315684664289000,1,"http://www.tracker/pdf-view","",7304310,7304310,"application/x-zip","",0,0
21,"DTLite4413-0173.exe","http://mirror07.soft24.com/getfile.php?p
22,"Acer-Aspire-4810TZ-Laptop-User-Manual.pdf","http://fs536.uploading.com/get_file/DYWPQOECPHZbAWt8ZnISppP7GF0WlomKijVVpCSnLLyLOAH-b1F03gFafDg68YdMU6tXsgWqJx4ejsxZCqFbD4vEGuwrjZDZ-Ao7u-MCiCPXKwDsyDIO5eGaUYLCdU9cLyjmnvbQ78oQ6CbCyc7DcjGRMCfLFrttCa9GkuJx5trXdqyXwhomQYsgPFjHt3pIdxiTZDe7crdOrDpNjfjXUEUT5YbNfIVfLiXxzrFZn3C4HBJcqnBQCuNbruPSQ3FHTJvD5zBjgvA-yRJ8IwH8bTfDfazKThES4Z2rAKwIi9pPHtnaM-JHXU0fRfuLDAMUE6nwmu2Rmzuo6deCJuRLpS5turLgFJdQnJzgGyk2Ufu2Au5zfcD4GUyHv7CWBKIeCuGO1Gd4hy-z","file:///E:/Downloads/Acer-Aspire-4810TZ-Laptop-User-Manual.pdf","",1314544738695000,1314544762353000,1,"http://uploading.com/files/get/78b1m9c4/","",1953536,1953536,"application/force-download","",0,0

As the above shows
The line index no. 21 is abruptly aborted when it reaches '='
and line index no. 22 omits all instances of '%3' and '%7'

I now believe that CMD.EXE has surprises for us humans when proper human text is processed by
for /f "tokens=1* delims=," ...
but even CMD.EXE does not know what it is doing when given inhuman computer/internet speak to process.
HERE BE DRAGONS :evil:

Posted: **26 Oct 2011 14:58**

I believe my original posted solution should still work with TESTy.CSV , and it should be faster.

Yes/No :?:

Dave Benham

Posted: **26 Oct 2011 15:29**

That is a definite YES - it works to perfection - so far :roll:

When I first started on this little SQLITE MERGE project I was concerned that I would make a mistake in my code,
therefore I used my code to not only produce an output file with new index numbers,
but to use the same process to split and then merge each line with the original index number as an "equivalent" file
and then compare the original with the "equivalent" to detect any deviations.
So far the only deviation is that the equivalent replaces each <LF> with <CR><LF>
and Firefox does not seem to object.

This recent WHOOPS has shaken what little confidence I had in what CMD.EXE would do for me.
I think it prudent that whilst using and trusting your code,
I will take the extra 2 seconds to create and test an "equivalent" file,
just in case the SQLITE data base surprises me with any more "special" ANSI codes.

Dare I ask about the prospects of the internet/SQLITE giving me text in UTF-8 or Unicode Big Endian or Unicode Little Endian. :wink:

Posted: **27 Oct 2011 10:23**

I have just tested code with 160 line lines of typical CSV
Code using this took 13170 mSec to produce the two output files

Code: Select all

for /f "tokens=1* delims=," %%d in ('type 0-%FILE%') do (

Code using this took 8730 mSec

Code: Select all

for /f "usebackq tokens=1* delims=," %%d in (0-%FILE%) do (

USEBACKQ is a clear winner on speed.

Question :-
Since I have no control over the internet chatter in Downloads.Sqlite,
and encountered DOS interpretation problems when using CALL,
is there any difference between the vulnerabilities of the two parse techniques due to CMD.EXE handling of special/unexpected/unicode text ?

Regards
Alan

Posted: **27 Oct 2011 13:20**

You probably should enclose your USEBACKQ fileset within quotes - just in case %FILE% contains spaces or any nasty characters like ^ & < etc. (That is the whole point of the USEBACKQ option):

Code: Select all

for /f "usebackq tokens=1* delims=," %%d in ("0-%FILE%") do (

------------------------------------------------

alan_b wrote:Question :-
Since I have no control over the internet chatter in Downloads.Sqlite,
and encountered DOS interpretation problems when using CALL,
is there any difference between the vulnerabilities of the two parse techniques due to CMD.EXE handling of special/unexpected/unicode text ?

I'm not very experienced with unicode.

But things I do know about the solution I provided:

The FOR loop will skip blank lines (I don't think this a problem for you, can be fixed if it is)
The FOR loop will skip lines beginning with ; because of default EOL option (again I think not a problem, but can be fixed)
Provided you are dealing with a single byte character set (some flavor of extended ASCII), all characters will be handled correctly except for 0x00. Not even jeb has figured out a way to load the nul character into a variable in batch.
As you have discovered, unix style <LF> line terminators will be converted to Windows style <CR><LF>. I'm not sure about other new line variants that may be out there. I believe that a file using the old Apple II/Macintosh <CR> line terminator will fail.
The solution will fail if any line exceeds the ~8k variable size limit

Dave Benham

Posted: **27 Oct 2011 13:29**

Thanks Dave

Regards
Alan

Posted: **28 Oct 2011 14:45**

VALIDITY TEST ERROR FROM FC.EXE

FC.EXE Can fail when a 255 character <LF> line is compared with the same 255 character <CR><LF> line

Code: Select all

FC 0-%FILE% 1-%FILE%

The code above fails
The code below works - SO FAR

Code: Select all

FC /W 0-%FILE% 1-%FILE%

QUESTION :-
Is it safe to assume the /W option to compress white space applies to not only the cited
(tabs and spaces)
but also to <LF> versus <CR><LF> discrepancies ?
OR should I fear the /W option has merely shifted the magic boundary to something other than 255 ?

This is a 4 line snippet from 0-moz_down.csv

Code: Select all

24,"FSL_Launcher.zip","http://www.webalice.it/guido.vinaio/releases/FSL_Launcher.zip","file:///D:/Downloads/FSL_Launcher.zip","",1299527613267000,1299527620298000,1,"http://fsl.sytes.net/download.html#launcher","",2486486,2486486,"application/zip","",0,0
25,"SuperFinder.chm","http://www.webalice.it/guido.vinaio/releases/SuperFinder.chm","file:///D:/Downloads/SuperFinder.chm","",1299598804179000,1299598810045000,1,,"",251764,251764,"application/vnd.ms-htmlhelp","",0,0
26,"SuperFinderXT.chm","http://www.webalice.it/guido.vinaio/releases/SuperFinderXT.chm","file:///D:/Downloads/SuperFinderXT.chm","",1299598814850000,1299598818485000,1,,"",467709,467709,"application/vnd.ms-htmlhelp","",0,0
27,"SuperFinderXT.zip","http://www.webalice.it/guido.vinaio/releases/SuperFinderXT.zip","file:///D:/Downloads/SuperFinderXT.zip","",1299599542553000,1299599553740000,1,"http://fsl.sytes.net/download.html#ssearch","",3795450,3795450,"application/zip","",0,0

OOPS sorry - my paste buffer held only <LF> text but it looks like the forum has changed it to <CR><LF>

This is my final final final code - I hope :-

Code: Select all

@ECHO OFF
setlocal disableDelayedExpansion

SET FILE=moz_down.csv
IF EXIST %FILE% DEL %FILE%
IF EXIST 1-%FILE% DEL 1-%FILE%

SET N=200
for /f "usebackq tokens=1* delims=," %%d in ("0-%FILE%") do (
  ECHO %%d,%%e>> 1-%FILE%
  SET /A N+=1
  set "ln=%%e"
  setlocal enableDelayedExpansion
  ECHO !N!,!ln!>> %FILE%
  endlocal
)
FC /W 0-%FILE% 1-%FILE% && SET RESULT=SUCCESS || (SET RESULT=ERROR & ECHO DISASTER & PAUSE)
ECHO %RESULT%TRANSLATING 0-%FILE% to %FILE% & ECHO(
PAUSE

The usage is to export from SqliteManager a CSV that is saved as 0-moz_down.csv
and to produce a desired output with index numbers starting at 201 and named moz_down.csv ready importing to SqliteManager
and to use identical manipulations but retaining the original index numbers and named 1-moz_down.csv
So long as 0-moz_down.csv and 1-moz_down.csv are identical (apart from <CR>) then nothing went wrong with tricky code manipulations,
and I can then be confident that moz_down.csv is exactly as required with index numbers starting at 201.

Am I safe using "FC /W ..."
or should I first of all convert the original export in <LF> format to <CR><LF> format, and how ?

Regards
Alan

DosTips.com

How to stop delayedexpansion from changing "free text"

How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"

Re: How to stop delayedexpansion from changing "free text"