Computer Hope
Microsoft => Microsoft DOS => Topic started by: tanvir.khan on September 03, 2014, 05:32:37 AM
-
Hi:
I've a dump file in text format (pipe separator). I need to select specific columns from the file and make a new file. For Example,
Original Dump (I need to select column 3, 4 and 6; and make a new dump). How can I do it in MS DOS using a batch file?
----------------
AAA|BBB|CCC|DDD|EEEE|FFFF|GGGG|HHHH
TTT|MMM|KKK|123|FFFF|VVVV|IIII|BBBB
XXX|SSS|AAA|ZZZ|TTTT|JJJJ|RRRR|YYYY
Output Dump
---------------
CCC|DDD|FFFF
KKK|123|VVVV
AAA|ZZZ|JJJJ
Please help.
Tanvir
-
Assuming you are using CMD.exe from Windows and not actually a version of MS DOS.
@echo off
for /F "tokens=3,4,6 delims=|" %%G in (file.txt) do echo %%G^|%%H^|%%I>>newfile.txt
-
Thanks Squashman. Your post is really helpful. But I need one more help, I need to extract file with any name (*.txt or file*) instead of a static name (file.txt).
@echo off
for /F "tokens=3,4,6 delims=|" %%G in (file.txt) do echo %%G^|%%H^|%%I>>newfile.txt
I've tried with *.txt or file* but it doesn't work. Could you please let me know how to resolve it.
Thanks,Tanvir
-
I've found a problem in the script. It doesn't recognize blank field. Please help me in this issue as well. Picked column 3, 4 and 6. For Example:
INPUT:
--------
VVVVV|BBBB|3212|9M9M|7777|MMMM|899
sssss||4444|AAAA|3333|LLLL|101
RRRR|PPPP|5555|1111|5555|PPPP|770
It returns OUTPUT:
---------------------
3212|9M9M|MMMM
AAAA|3333|101
5555|1111|PPPP
Actual Output should be:
---------------------------
3212|9M9M|MMMM
4444|AAAA|LLLL
5555|1111|PPPP
Thanks, Tanvir
-
I've found a problem in the script. It doesn't recognize blank field. Please help me in this issue as well. Picked column 3, 4 and 6. For Example:
INPUT:
--------
VVVVV|BBBB|3212|9M9M|7777|MMMM|899
sssss||4444|AAAA|3333|LLLL|101
RRRR|PPPP|5555|1111|5555|PPPP|770
It returns OUTPUT:
---------------------
3212|9M9M|MMMM
AAAA|3333|101
5555|1111|PPPP
Actual Output should be:
---------------------------
3212|9M9M|MMMM
4444|AAAA|LLLL
5555|1111|PPPP
Thanks, Tanvir
Not a problem with the script. It is more of a problem with your data. The FOR /F command always sees consecutive delimiters as one. If you would have provided that information upfront, I could have coded for that instance. That is the main problem with providing obfuscated data. It is better to provide real world examples up front.
-
Thanks for your feedback. As I've said the script works fine if all the fields have value like below: (real time example)
SUBCOS|RATE|DATE|ACCSTATE|PREVBALANCE|ACTTIME|USERSTARE|RATEPLAN|EXPIRY|
2|30|20150830|0|0|20160226235959|11|3|20170101000000|
2|44|20140329|0|0|20140923235959|12|4|20140328003330|
2|20|20140722|2|0|20150118235959|11|3|20140518151446|
But if few fields are NULL, i.e. in second row there is no RATE (3, 4 & 6 columns are picked)
SL|RATE|DATE|ACCSTATE|PREVBALANCE|ACTTIME|USERSTARE|RATEPLAN|EXPIRY|
2|30|20150830|55|99|20160226235959|11|3|20170101000000|
2||20140329|33|90|20140923235959|12|4|20140328003330|
2|20|20140722|22|91|20150118235959|11|3|20140518151446|
Output comes:
----------------
DATE|ACCSTATE|ACTTIME|
20150830|55|20160226235959| ----Ok
33|90|12| -----As RATE field is blank, insted of picking DATE column, it picks ACCSTATE as column 3 which actually is 4
20140722|22|20150118235959| -----Ok
Please let me know if you need any information.
Thanks, Tanvir
-
This uses dbenham's ParseCSV.bat to reformat the file and then Squashman's code to output the data.
Save both batch files into the same folder as file.csv and run this one.
@echo off
call ParseCSV.bat "/i:|" "/o:|" <file.csv >file.csv.tmp
for /F "tokens=3,4,6 delims=|" %%G in (file.csv.tmp) do >>newfile.csv echo %%~G^|%%~H^|%%~I
del file.csv.tmp
ParseCSV.bat
@if (@X)==(@Y) @end /* harmless hybrid line that begins a JScrpt comment
::************ Documentation ***********
::parseCSV.bat version 1.0
:::
:::parseCSV [/option]...
:::
::: Parse stdin as CSV and write it to stdout in a way that can be safely
::: parsed by FOR /F. All columns will be enclosed by quotes so that empty
::: columns may be preserved. It also supports delimiters, newlines, and
::: quotes within quoted values. Two consecutive quotes within a quoted value
::: are converted into one quote.
:::
::: Available options:
:::
::: /I:string = Input delimiter. Default is a comma.
:::
::: /O:string = Output delimiter. Default is a comma.
:::
::: /E = Encode output delimiter in value as \D
::: Encode newline in value as \N
::: Encode backslash in value as \S
:::
::: /D = Escape exclamation point and caret for delayed expansion
::: ! becomes ^!
::: ^ becomes ^^
:::
:::parseCSV /?
:::
::: Display this help
:::
:::parseCSV /V
:::
::: Display the version of parseCSV.bat
:::
:::parseCSV.bat was written by Dave Benham. Updates are available at the original
:::posting site: http://www.dostips.com/forum/viewtopic.php?f=3&t=5702
:::
::************ Batch portion ***********
@echo off
if "%~1" equ "/?" (
setlocal disableDelayedExpansion
for /f "delims=: tokens=*" %%A in ('findstr "^:::" "%~f0"') do echo(%%A
exit /b 0
)
if /i "%~1" equ "/V" (
for /f "delims=:" %%A in ('findstr /bc:"::%~nx0 version " "%~f0"') do echo %%A
exit /b 0
)
cscript //E:JScript //nologo "%~f0" %*
exit /b 0
************ JScript portion ***********/
var args = WScript.Arguments.Named,
stdin = WScript.Stdin,
stdout = WScript.Stdout,
escape = args.Exists("E"),
delayed = args.Exists("D"),
inDelim = args.Exists("I") ? args.Item("I") : ",",
outDelim = args.Exists("O") ? args.Item("O") : ",",
quote = false,
ln, c, n;
while (!stdin.AtEndOfStream) {
ln=stdin.ReadLine();
if (!quote) stdout.Write('"');
for (n=0; n<ln.length; n++ ) {
c=ln.charAt(n);
if (c == '"') {
if (quote && ln.charAt(n+1) == '"') {
n++;
} else {
quote=!quote;
continue;
}
}
if (c == inDelim && !quote) c='"'+outDelim+'"';
if (escape) {
if (c == outDelim) c="\\D";
if (c == "\\") c="\\S";
}
if (delayed) {
if (c == "!") c="^!";
if (c == "^") c="^^";
}
stdout.Write(c);
}
stdout.Write( (quote) ? ((escape) ? "\\N" : "\n") : '"\n' );
}
-
I had found another post online about changing the DOUBLE PIPES to |#null#| and then running that output to another FOR /F command to un-delimit the output. But then you have to run that data through another SET command to remove the #null#. Would probably have issues with poison characters as well.
-
Thanks to both of you. It returns the correct output now but each field comes with " like below:
"2"|"147500"|"1906342342"|"2"|"20140830125243"
"2"|"0"|"1903312025"|"197"|"20140327111829"
"2"|"765952"|"1903312029"|"160"|"20140327174850"
I've tried removing " from parser file but the output become wrong again. Just need to eliminate the ". As I'm very beginner with DOS need further help.
Another help I need - How can I call multiple files instead of a single one here
call ParseCSV.bat "/i:|" "/o:|" <file.csv >file.csv.tmp
i.e. call ParseCSV.bat "/i:|" "/o:|" <*.csv >file.csv.tmp ---I tried but it doesn't work
Please help.
Thanks/
Tanvir
-
Is it OK if I ask a question?
If this was a CSV file --
Why not just use a spreadsheet to parse it.
Just asking.
-
Is it OK if I ask a question?
I think the OP's last post answers your question. They need to do it on multiple files. Would you want to open up multiple files and manually process all of them on a daily basis?
-
Thanks to both of you. It returns the correct output now but each field comes with " like below:
"2"|"147500"|"1906342342"|"2"|"20140830125243"
"2"|"0"|"1903312025"|"197"|"20140327111829"
"2"|"765952"|"1903312029"|"160"|"20140327174850"
That is not the correct output. It has more than 3 fields. Nor would it be the correct input because you said you wanted columns 3, 4 & 6 and you only have 5 columns.
I used your last data example
SL|RATE|DATE|ACCSTATE|PREVBALANCE|ACTTIME|USERSTARE|RATEPLAN|EXPIRY|
2|30|20150830|55|99|20160226235959|11|3|20170101000000|
2||20140329|33|90|20140923235959|12|4|20140328003330|
2|20|20140722|22|91|20150118235959|11|3|20140518151446|And this is the output I got.
DATE|ACCSTATE|ACTTIME
20150830|55|20160226235959
20140329|33|20140923235959
20140722|22|20150118235959Program is working for the 2nd time as you described your data.
-
I used the following input which has surround quotes around each field
"2"|""|"147500"|"1906342342"|"2"|"20140830125243"
"2"|""|"0"|"1903312025"|"197"|"20140327111829"
"2"|""|"765952"|"1903312029"|"160"|"20140327174850"And the output does not have any surround quotes. I did not change any of the code that Foxidrive posted.
147500|1906342342|20140830125243
0|1903312025|20140327111829
765952|1903312029|20140327174850
-
Dear Geek-9pm: Actually it's not a CSV, those are text files as I mentioned in my first post.
Dear Squashman: No, files will come in a folder automatically with extension .txt; I've to parse those make a single dump (an auto job will run every 1 hr) and upload into a Oracle database using SQL Loader. So, I need to parse all a files of .txt extension an make a single dump.
My Input files don't have quotes. But when I run the Parse Script the output comes with quote. These quotes appear because of this line in the parse script:
if (c == inDelim && !quote) c='"'+outDelim+'"' ---I removed quotes from here, quotes disappear but returns wrong result
Did you use the same parse script foxidrive provided and also parsed from text file?
Need further help.
Thanks/Tanvir
-
My Input files don't have quotes. But when I run the Parse Script the output comes with quote. These quotes appear because of this line in the parse script:
if (c == inDelim && !quote) c='"'+outDelim+'"' ---I removed quotes from here, quotes disappear but returns wrong result
No. It is not because of that line.
Did you use the same parse script foxidrive provided and also parsed from text file?
I did not change any of the code that Foxidrive posted.
-
Post the main batch file that you used and I bet you have something wrong in it. Pretty sure you didn't copy the main batch file that Foxidrive posted. If you just tried to edit the first one I posted then you are using the wrong code.
-
Here are all the original files attached with this mail:
Input File: 20140902_pps_BaseTab_133.txt --FYI this file extension is .list; but I can't attach file with that extension, so changed to .txt
Output File: SDP_DUMP.txt
Parser File: Parser.bat
Executable File: SDP_DUMP_EXECUTE.bat
Please let me know what went wrong :(
Thanks/Tanvir
[attachment deleted by admin to conserve space]
-
Pretty sure you didn't copy the main batch file that Foxidrive posted.
This. I can see the difference in your code tanvir.khan.
-
SDP_DUMP_EXECUTE.bat is not the same code as Foxidrive posted.
-
Sorry guys!! foxidrive, as you mentioned to use parse script and add an extra line in SDP_DUMP_EXECUTE.bat, then use Squashman code; I didn't change the below line which actually creating the problem: :o
Squashman: for /F "tokens=3,4,6 delims=|" %%G in (file.csv.tmp) do echo %%G^|%%H^|%%I>>newfile.txt
foxidrive: for /F "tokens=3,4,6 delims=|" %%G in (file.csv.tmp) do >>newfile.txt echo %%~G^|%%~H^|%%~I
Now it works absolutely fine. Big thanks to both of you (Squashman, foxidrive) guys for your continuous support.
As you know I've to parse multiple files automatically; is it possible to use *_pps_* or *.list etc. so that I don't to change file name manually by opening the bat file each time.
call Parser.bat "/i:|" "/o:|" <20140902_pps_BaseTab_133.list >file.csv.tmp
Thanks/Tanvir
-
What I do at work is make the batch file accept cmd line arguments. What is nice about this method is that you can use it from the command prompt or you can also use Windows Explorer to select the files you want to process and then drag and drop them onto the main batch file.
The other option is too wrap your existing code inside another FOR cmd that will list the files you want to process. This can used Wild Cards to select the files you want.
-
Hi Squashman:
I think your second option is more convenient for me, at least I understood what you wanted to say; but I couldn't even understand your first point. :( Could you please help me with code or demonstration to implement the second option. :)
Please help.
Thanks/Tanvir
-
Your SIG: By 2015 the number of mobile-devices will exceed the number of people.
This surely includes every mobile device made by man - not the number that are currently in use.
Apply the same count to Automobiles... I think it would be a huge number too. :)
-
This surely includes every mobile device made by man - not the number that are currently in use.
Apply the same count to Automobiles... I think it would be a huge number too. :)
What do we consider a mobile device?
We could go all the way back to the original Walkman, Portable CD players, Boom Boxes, Electronic Games etc, etc... Those were the mobile devices I used as a teenager. When I was growing up I already had more mobile devices then all of my family combined.
-
What do we consider a mobile device?
Hmm, good question. A car is a mobile device too. :D
-
What are you guys talking about ::)
-
What are you guys talking about ::)
Click on the quote link to see the sig from a post in this thread.
-
That isn't relevant with my problem. I need to parse multiple files instead of a single one
call Parser.bat "/i:|" "/o:|" <20140902_pps_BaseTab_133.list >file.csv.tmp
How can I call multiple files? ???
-
Sorry to meander off topic re the SIG, it's just not very satisfying answering questions when the task changes, and you need to redo what has already been tested and completed.
-
Foxidrive
Your frustration is understandable.
This post got my attention because the op seem to be introducing new parameters into the problem, as if this were a chat room or classroom discussion and not a form.
Whenever anyone post a problem in this section, it needs to be clearly stated. often a clear, concise statement of power problem. i'll be solved is the key to providing a quick and accurate solution.
Sometimes it helps if the poster will outline his problem in a pseudo-language that is close to the way we naturally speak.
Something like this. perhaps.
Open up some kind of file lists of a bunch of files that need to be processed.
Begin the outer loop of the file list.
Open up some kind of file from the next item in the list
Open up some kind of file for the output of an input file.
Read in one line from the input file.
Examine the line to find the first item we want and send it to the output file
Do the same for the next item.
And again from the next item.
Bump up to the input line.
If at the end of the file close both files.
Bump up to the next file in the list.
If we get to the end of the list, quit the job.
Otherwise repeat.
Of course, that is not usable code, but it does describe the idea of having a list of files and reading input from each file and doing a process and sending it to an output. the type of file reading that you could do in dos is called stream editing. there is no provision in dos for reading an entire column of text from a file that is organized into columns. so you just have to do it line by line.
This is given only asset suggestion. it is much better to have a verbose description in the first post rather than going on on and adding more information to the problem on-the-fly.
Well, that's what i think. if anybody knows what better way to solve problems, please speak up and say something.
-
I think this should work. Didn't test it though.
@echo off
if exist combined.list del combined.list
for %%A in (*.list) do (
call ParseCSV.bat "/i:|" "/o:|" <"%%~A" >"%%~A.tmp"
for /F "usebackq tokens=3,4,6 delims=|" %%G in ("%%~A.tmp") do >>combined.list echo %%~G^|%%~H^|%%~I
del "%%~A.tmp"
)
-
No reply, no thanks.
It seems to be the norm these days.
-
No reply, no thanks.
It seems to be the norm these days.
The sad truth.
-
Kudos to you guys...
I stated it last week in another Topic....
Unsung heros....but those that are important recognise the efforts put forth.
Once again...Thanx !