Page 1 of 1
changing a 'Column' in a tab-delimited text File
Posted: 17 Aug 2017 02:50
by saltypepper105
I have a very large tab-delimited text file with 4 columns and many rows. I want a batch file to replace the third tab on each row with a colon.
Here is an example of input and desired output:
input(always 4 columns):
username[tab]firstname[space]lastname[tab]email[tab]number
username[tab]firstname[tab]email[tab]number
output:
username[tab]firstname[space]lastname[tab]email[:]number
username[tab]firstname[tab]email[:]number
Re: changing a 'Column' in a tab-delimited text File
Posted: 17 Aug 2017 09:00
by elzooilogico
saltypepper105 wrote:I have a very large tab-delimited text file with 4 columns and many rows. I want a batch file to replace the third tab on each row with a colon.
Here is an example of input and desired output:
input(always 4 columns):
username[tab]firstname[space]lastname[tab]email[tab]number
username[tab]firstname[tab]email[tab]number
output:
username[tab]firstname[space]lastname[tab]email[:]number
username[tab]firstname[tab]email[:]number
You said
input(always 4 columns) but your text samples are 5 and 4 columns. The script below is when there are 4 columns
Code: Select all
@echo off
setlocal
rem grab tab character
set "TAB="
rem First, try the method for Windows XP
for /F "skip=4 delims=pR tokens=2" %%a in ('reg query hkcu\environment /v temp' ) do set "TAB=%%a"
rem Then, the method for newer versions
rem http://www.dostips.com/forum/viewtopic.php?f=3&t=1733&p=6840#p6853
for /F "tokens=2 delims=0" %%a in ('shutdown /? ^| findstr /BC:E') do if not defined TAB set "TAB=%%a"
(
for /f "tokens=1-4 delims=%TAB%" %%a in (input.txt) do (
echo %%a%TAB%%%b%TAB%%%c:%%d
)
) > "output.txt"
endlocal
exit/B
Re: changing a 'Column' in a tab-delimited text File
Posted: 17 Aug 2017 16:16
by saltypepper105
elzooilogico wrote:saltypepper105 wrote:I have a very large tab-delimited text file with 4 columns and many rows. I want a batch file to replace the third tab on each row with a colon.
Here is an example of input and desired output:
input(always 4 columns):
username[tab]firstname[space]lastname[tab]email[tab]number
username[tab]firstname[tab]email[tab]number
output:
username[tab]firstname[space]lastname[tab]email[:]number
username[tab]firstname[tab]email[:]number
You said
input(always 4 columns) but your text samples are 5 and 4 columns. The script below is when there are 4 columns
Code: Select all
@echo off
setlocal
rem grab tab character
set "TAB="
rem First, try the method for Windows XP
for /F "skip=4 delims=pR tokens=2" %%a in ('reg query hkcu\environment /v temp' ) do set "TAB=%%a"
rem Then, the method for newer versions
rem http://www.dostips.com/forum/viewtopic.php?f=3&t=1733&p=6840#p6853
for /F "tokens=2 delims=0" %%a in ('shutdown /? ^| findstr /BC:E') do if not defined TAB set "TAB=%%a"
(
for /f "tokens=1-4 delims=%TAB%" %%a in (input.txt) do (
echo %%a%TAB%%%b%TAB%%%c:%%d
)
) > "output.txt"
endlocal
exit/B
Thank you, I will test the code, it is still 4 columns if it is delimited by [tab], just one of the columns has a space
Re: changing a 'Column' in a tab-delimited text File
Posted: 18 Aug 2017 02:44
by elzooilogico
My fault, I didn't read carefully. I've seen a delimiter and assumed they were all the same. Then, this should do the job, as the only valid delimiter is tab.
Re: changing a 'Column' in a tab-delimited text File
Posted: 18 Aug 2017 05:55
by saltypepper105
elzooilogico wrote:My fault, I didn't read carefully. I've seen a delimiter and assumed they were all the same. Then, this should do the job, as the only valid delimiter is tab.
You are amazing, it works perfectly!
Re: changing a 'Column' in a tab-delimited text File
Posted: 18 Aug 2017 08:42
by Aacini
Simpler:
Code: Select all
@echo off
(for /F "tokens=1-5" %%a in (input.txt) do (
if "%%e" neq "" (
echo %%a %%b %%c %%d:%%e
) else (
echo %%a %%b %%c:%%d
)
)) > output.txt
Just be sure to insert the proper separators in echo command in each case: a TAB after %%a and %%c and a space after %%b in first case; or a TAB after %%a and %%b in second case...
Antonio
Re: changing a 'Column' in a tab-delimited text File
Posted: 18 Aug 2017 16:35
by saltypepper105
Aacini wrote:Simpler:
Code: Select all
@echo off
(for /F "tokens=1-5" %%a in (input.txt) do (
if "%%e" neq "" (
echo %%a %%b %%c %%d:%%e
) else (
echo %%a %%b %%c:%%d
)
)) > output.txt
Just be sure to insert the proper separators in echo command in each case: a TAB after %%a and %%c and a space after %%b in first case; or a TAB after %%a and %%b in second case...
Antonio
While it does work, you have to manually specify every possible possible combination with else statements. Lets say someone had a name with 3 spaces, or 4. The code should only treat [tab] as a delimiter and ignore [space]
Re: changing a 'Column' in a tab-delimited text File
Posted: 18 Aug 2017 21:15
by Aacini
I am afraid I don't understand. This is your example data:
Code: Select all
username[tab]firstname[space]lastname[tab]email[tab]number
username[tab]firstname[tab]email[tab]number
... and in your description, you said:
saltypepper105 wrote:... one of the columns has a space
Are you saying now that "a name may have 3 spaces, or 4"? Well, this
new specification makes the code even simpler:
Code: Select all
@echo off
(for /F "tokens=1-4 delims= " %%a in (input.txt) do echo %%a %%b %%c:%%d) > output.txt
Just be sure that the character after the equal sign in delims= and after %%a and %%b in "echo" be a TAB...
Antonio
Re: changing a 'Column' in a tab-delimited text File
Posted: 20 Aug 2017 21:47
by saltypepper105
Ok I noticed the problem with both of you codes, they don't allow for a empty/null column entry. Is there any way to fix this?
Re: changing a 'Column' in a tab-delimited text File
Posted: 21 Aug 2017 10:00
by elzooilogico
saltypepper105 wrote:Ok I noticed the problem with both of you codes, they don't allow for a empty/null column entry. Is there any way to fix this?
Then, you need a workaround,
Code: Select all
@echo off
SetLocal EnableExtensions EnableDelayedExpansion
rem get sure here is the tab character (my text editor is set to change tabs into spaces)
set "TAB= "
(for /f "tokens=*" %%a in (input.txt) do (
set "line=%%a" & set "line="!line:%TAB%="%TAB%"!""
for /f "tokens=1-4 delims=%TAB%" %%A in ("!line!") do echo %%~A%TAB%%%~B%TAB%%%~C:%%~D
)) > "output.txt"
endlocal
exit/B
or
Code: Select all
@echo off
SetLocal EnableExtensions EnableDelayedExpansion
rem get sure here is the tab character (my text editor is set to change tabs into spaces)
set "TAB= "
(for /f usebackq^ delims^=^ eol^= %%a in ("input.txt") do (
set "line=%%a"
for /f "tokens=1-4 delims=%TAB%" %%A in (""!line:^%TAB%^="%TAB%"!"") do echo %%~A%TAB%%%~B%TAB%%%~C:%%~D
)) > "output.txt"
endlocal
exit/B