Computer Hope
Microsoft => Microsoft DOS => Topic started by: carmine on November 26, 2014, 08:59:44 PM
-
I have a text file that has columns delimited using a pipe character ("|"). The csv has hundreds of columns, and I want to remove column 16&17 and rest of the columns to be intact. How do i create a batch script that will create a new output.txt file.
-
May I ask why? :)
Why do you need to do that?
Why use a script in Batch?
Programs the use CSV have ways to read and modify the files.
Microsoft says one can use Excel to do it.
But f batch is better, they would have recommended it.
Reference:
Microsoft Excel Tutorial: How to work with CSV files
(http://spreadsheetyogi.com/microsoft-excel-tutorial-how-to-work-with-csv-files/)
-
I have a text file that has columns delimited using a pipe character ("|"). The csv has hundreds of columns, and I want to remove column 16&17 and rest of the columns to be intact. How do i create a batch script that will create a new output.txt file.
It does depends on the text makeup of the file, unicode, foreign text, the maximum length of a line, the total size of the file.
-
If the file is ASCII, then you can do this very quickly with JREPL.BAT (http://www.dostips.com/forum/viewtopic.php?f=3&t=6044)
Assuming none of the columns contain the quoted pipe characters as part of the value, then:
jrepl "^((?:.*?\|){15}).*?\|.*?\|" "$1" /f "yourFile.csv" /o -
The above will overwrite the original file. If you want to preserve the original and write a new file, then use /o "newFile.csv" instead of /o -
It can also be made to work if column values can contain quoted pipe literals. It even supports quotes within the quoted value (escaped as ""):
jrepl "^((?:\q(?:\q\q|[^\q])*\q\||.*?\|){15})(?:\q(?:\q\q|[^\q])*\q\||.*?\|){2}" "$1" /x /f "yourFile.csv" /o -
Dave Benham
-
Hi,
Many thanks to Dave Benham for helping.
Thanks.