changing a 'Column' in a tab-delimited text File

[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

[elzooilogico]

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

[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

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

[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.

[saltypepper105]

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!

[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

[saltypepper105]

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]

[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:

... 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

[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?

[elzooilogico]

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