More than 31 columns

October 25, 2009 at 03:36:26
Specs: Windows 7
Hi

Can anyone help me get round the problem of using more than 26 tokens in a batch file.
I need to extract data from a csv file which has approx 50 columns. I can extract data upto column 31 but cannot go any further. The output justs displays %%k.

This is an example of the lines I'm using:
This line displays ok: for /f "tokens=1,2,31 delims=," %%i in (headings.txt) do (echo %%i,%%j,14:30,%%k >> newlst.txt)
This line doesn't display: for /f "tokens=1,2,32 delims=," %%i in (headings.txt) do (echo %%i,%%j,15:00,%%k >> newlst1.txt)


See More: More than 31 columns

Report •


#1
October 25, 2009 at 07:18:33
This is hard wired to get col 49 but you can tailor to suit.


===========================


@echo off > newfile & setLocal enableDELAYedexpansion

for /f "tokens=* delims=," %%a in (column.csv) do (
  call :sub1 %%a
)

goto :eof

:sub1
  set N=
  :loop
  set /a N+=1
    if !N! equ 49 (
    >> newfile echo %1
    )
  shift
  if "%1" neq "" goto :loop
goto :eof


=====================================
Helping others achieve escape felicity

M2


Report •

#2
October 26, 2009 at 00:23:35
use a better tool to parse csv files... there are many tools you can use, gawk, Perl,Python etc... or vbscript if you want native solution. see here for example of parsing csv using vbscript.

save the script as test.vbs and on command line

c:\test> cscript /nologo test.vbs

GNU win32 packages | Gawk


Report •

#3
October 26, 2009 at 13:14:01
Guys

Thanks a million (new to scripting), tried both and modifying the vbscript gives me a better result. Also found how to output the data to a text file. The only thing left is to find a way to change one of the columns which holds a date. The format of the date is 20090901 and I need to change to 01/09/2009. Any suggestions.
Once again thanks to both of you.


Report •

Related Solutions

#4
October 26, 2009 at 18:05:33
>> The format of the date is 20090901 and I need to change to 01/09/2009

you can use the Mid() function. eg to get "2009". try

strYr = Mid( array[1] , 1, 4)

note, array[1] is where the date string is stored. Its just an example. I leave it to you to get the rest of the substrings. check the vbscript manual for usage of Mid.
after getting all of the substrings, concatenate them using & and "/". this will give you a start on what to do. good luck

GNU win32 packages | Gawk


Report •

#5
October 26, 2009 at 21:08:12
This will rearrange the date in col 48.

=============================

@echo off > newfile & setLocal enableDELAYedexpansion

for /f "tokens=* delims=," %%a in (column.csv) do (
  call :sub1 %%a
)
goto :eof

:sub1
  set row=
  set N=
  set c=

:loop
  set c=%1
  set /a N+=1
  if !N! equ 48 call :sub2
    for %%i in (47 48 49) do (
      if !N! equ %%i (
        set row=!row!,!c!
      )
    )
  shift
  if "%1" neq "" goto :loop
  >> newfile echo !row:~1!
goto :eof

:sub2
  set Y=!c:~0,4!
  set M=!c:~4,2!
  set D=!c:~6,2!
  set c=!D!/!M!/!Y!
goto :eof


=====================================
Helping others achieve escape felicity

M2


Report •

#6
October 27, 2009 at 11:47:52
Once again guys thanks for the solutions, looking at the Mid function and applying this to the vbscript from earlier has done the trick. I really appreciate both of you replying as it is getting me to play about with both vbscript and batch scripts.

Thanks again


Report •


Ask Question