Solved manipulate csv through batch file

February 21, 2016 at 03:01:55
Specs: Windows 7
Hi,

I have a csv file (created from a .dbf) with 161 columns, and a number of lines.
I want to delete a lot of them. So when I run this batch file, I expect to get only 4 columns back, instead all the columns from line 1 are exported, what do I wrong ?

I need all lines and only 4 columns

@echo off>output1.csv
cls
setlocal enabledelayedexpansion

:: Deletes The Selected Column From Existing .csv File.

for /f "tokens=1-161* delims=;" %%1 in (teil.csv) do (
echo %%A,%%B,%%C,%%H>output1.csv
)
type output1.csv


See More: manipulate csv through batch file

Report •


✔ Best Answer
February 29, 2016 at 17:30:28
After working with the actual csv data, (one of the fields contained a space within its content), this was the resulting batch attempt:

@echo off>output.csv & setlocal enabledelayedexpansion
set a=1,2,3,8,14,15,19,24,25,26,45,54,68,73,78,83,159,160,161
set dlm=;
for /f "eol= tokens=*" %%a in (file.csv) do (
set d=
set out=
set z=%%a
:: disable the following line if you want all the bloated space included
set z=!z: =!
::put quotes around all the fields so internal space won't throw off the index
set z=!z:;=";"!
:: this is just to give a sample of the data after pre-formatting
>>zz echo "!z!"

for %%b in (%a%) do (
set /a t=%%b-1
call :bild "!z!"
)
>>output.csv echo !out!
)
goto :eof


:bild
for /l %%z in (1 1 %t%) do shift
set out=%out%%d%
set out=%out%%~1
:: enable this if you want that trailing space in each field
rem (set out=%out% )
set d=;
goto :eof

message edited by nbrane



#1
February 21, 2016 at 04:04:11
@echo off > output1.csv
cls

:: Deletes The Selected Columns From Existing .csv File

for /F "tokens=1-3,8 delims=;" %%A in (teil.csv) do (
  echo.%%A,%%B,%%C,%%D
) >> output1.csv
type output1.csv

message edited by IVO


Report •

#2
February 21, 2016 at 05:57:01
Thanks, it works ... but
when I want to add more columns, it stops putting the rows in.
When I do this:

@echo off > output1.csv
cls

:: Deletes The Selected Columns From Existing .csv File

for /F "tokens=1-3,8,14,15,19,24-26,45,54 delims=;" %%A in (teil.csv) do (
  echo.%%A,%%B,%%C,%%D,%%E,%%F,%%G,%%H,%%I,%%J,%%K,%%L
)>> output1.csv
type output1.csv

The last two rows are filled with ,%K,%L and I have to add a few more columns.

Is there a limitation in the number of rows I can choose from ?


Report •

#3
February 21, 2016 at 07:45:03
The number of columns is limited by the number of tokens accessible using alphabetic variables, i.e. A to Z.

About your issue I need to examine your problematic rows since the trouble seems to interest the last two only.


Report •

Related Solutions

#4
February 21, 2016 at 11:05:59
Ah ok so that is the problem A till Z = 26, I have 161 columns (A -> FE).

In my previous post the last 2 added columns gave that result, if I add more the error is equal to the extra added columns.

I think I better find a way to split up the csv into multiple ones with 26 columns max, then extract the columns I need and then paste those back into 1 csv file.

Or extract the columns I need in range 1 -> 26, then delete the first 26 rows, then again extract in the new range 1 -> 26, again delete first 26 columns, and so on untill I reacht the last column.

That will be a challenge


Report •

#5
February 21, 2016 at 12:44:53
Before you go on a challenging lane I want to point out you can access a maximum of 26 columns whatever they are located, e.g. tokens=3,159 with variables A and B i.e. 2 tokens corresponding to the first two consecutive variables. I hope I clarified my previous post. Anyway there are methods to process extra variables without splitting the row using nested For loops.

If you can wait and post the position of requested columns I may help, but not immediately.


Report •

#6
February 22, 2016 at 00:16:33
ah ok, I misunderstood. My knowledge of batch is minimal. Learning more with this issue.

Some more info of the file. It is a database file, converted to a csv file.
Some columns are filled with spaces, but when I remove these spaces the result stays the same.


Report •

#7
February 22, 2016 at 23:48:38
The problem is with batch: It ignores null delimited fields completely. For example, this:
for /f "tokens=1-7 delims=;" %a in (";;one;;;two;three") do @echo .%a
does not give what you expect: two blanks, "one", two more blanks then "two" etc. It skips the null fields.

Whatever possessed them to do that is beyond me, but MSoft decided that was the thing to do. (on second thought, batch var.s can't have null as their value, so...)
There are probably better ways, but this was my "work-around" to get past the issue:
::---------- begin batchscript code
@echo off & setlocal enabledelayedexpansion
:: set your targeted fields here:
set a=1,4,6
:: and your delimiters here:
set dlm=;
for /f "tokens=*" %%z in (x.csv) do (
set t=%%z
:: added this to accommodate a null field one
set test=!t:~0,1!
if !test! equ %dlm% set t=%dlm%!t!
:: this has to be done twice due to some obscure thing having to do with unmatched pairs of delimiters
for /L %%a in (1 1 2) do set t=!t:%dlm%%dlm%=%dlm%_%dlm%!
:: any insignificant char. can be used for "_", but not space or tab. _ just fills in the null fields
for /f "tokens=%a% delims=%dlm%" %%a in ("!t!") do set out=%%a%dlm%%%b%dlm%%%c
echo !out!
)

message edited by nbrane


Report •

#8
February 23, 2016 at 08:48:36
Thanks for you reply

When I use your code, first 10 columns are ok, last 9 are :

%k;%l;%m;%n;%o;%p;%q;%r;%s

Full script I used:

::---------- begin batchscript code
@echo off & setlocal enabledelayedexpansion
:: set your targeted fields here:
set a=1,2,3,8,14,15,19,24,25,26,45,54,68,73,78,83,159,160,161
:: and your delimiters here:
set dlm=;
for /f "tokens=*" %%z in (file.csv) do (
set t=%%z
:: this has to be done twice due to some obscure thing having to do with unmatched pairs of delimiters
for /L %%a in (1 1 2) do set t=!t:%dlm%%dlm%=%dlm%_%dlm%!
:: any insignificant char. can be used for "_", but not space or tab. _ just fills in the null fields
for /f "tokens=%a% delims=%dlm%" %%a in ("!t!") do set out=%%a%dlm%%%b%dlm%%%c%dlm%%%d%dlm%%%e%dlm%%%f%dlm%%%g%dlm%%%h%dlm%%%i%dlm%%%j%dlm%%%k%dlm%%%l%dlm%%%m%dlm%%%n%dlm%%%o%dlm%%%p%dlm%%%q%dlm%%%r%dlm%%%s
echo !out!
)>> output1.csv
type output1.csv


Report •

#9
February 23, 2016 at 09:32:43
If you could post or pmail me with a sample of the csv, preferably the exact line you are using for testing, or at least a close approximation. If the content of the cells is sensitive, replace it with random. There may be something in the content that is throwing things askew, such as if any of the items has the delimiter in its content etc. Meantime, you might try hardcoding the delimiter into the body instead of using the %dlm%:
for /f "tokens=%a% delims=;" %%a in ("!t!") do set out=%%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%%i;%%j;%%k;%%l;%%m;%%n;%%o;%%p;%%q;%%r;%%s

Report •

#10
February 24, 2016 at 23:03:47
I did not know that batch had a restriction on number of tokens, but apparently it won't serve more than 31 "fields", even if they're not used. Here is the next attempt, using "shift" instead of "tokens", more complicated, but it seemed to work ok with your test data:

:: ----- begin batchscript FIELDS.BAT
@echo off & setlocal enabledelayedexpansion
:: these are the target fields
set a=1,2,3,8,14,15,19,24,25,26,45,54,68,73,78,83,159,160,161
set dlm=;
::---- loop through the CSV file
for /f "tokens=*" %%a in (file.csv) do (
set d=
set out=
set z=%%a
set test=!z:~0,1!
if !test! equ %dlm% set z=%dlm% !z!
set z=!z:; ;=;_;!
set z=!z:; ;=;_;!
::--------- loop once for each target field on current line based on %a% fields set into var. t
for %%b in (%a%) do (
set /a t=%%b-1
call :bild !z!
)
>>output1.csv echo !out!
)
goto :eof


:bild
:: shift until current field# is encountered, then append and exit
for /l %%z in (1 1 %t%) do shift
set out=%out%%d%%1
set d=;
goto :eof


Report •

#11
February 25, 2016 at 21:00:52
At which point do you just give up and pick a different language? Like PowerShell?
Get-Content file.csv |
ForEach-Object { 
 ($_ -split ';')[0,1,2,7,13,14,18,23,24,25,44,53,67,72,77,82,158,159,160] -join ';' 
} | Out-File output1.csv -Force

How To Ask Questions The Smart Way


Report •

#12
February 26, 2016 at 00:46:51
Ha! but that's too easy! Never bring a gun to a knife fight. Never bring a chainsaw to a wood-choppers ball. I thought of VBscript solution (since I STILL haven't worked enough with powershell), but elected to go ahead with the batch "challenge" as an exercise in frustration and convolution. It will only work till the rubber-bands snap, the bailing-wire rusts, or the hock-um putty melts (or the platform becomes 64-bit).

Report •

#13
February 26, 2016 at 02:20:13
@nbrane

I tested your latest version, still a no go

@Razor2.3
Problem with powershell is that you do not know wich version is installed on a different pc
so the powershell script should be compatible with all versions ?

message edited by dreeke_


Report •

#14
February 26, 2016 at 05:34:38
@Razor2.3

This powershell script works too, with header selection:

Import-Csv -Delimiter ';' C:\Users\...\file.csv |
Select-Object HEADER1,HEADER2,HEADER3,... | (<- select headers)
Export-Csv -Delimiter ';' C:\Users\...\file_new.csv –NoTypeInformation

But I would like to crack this one with batch, if possible


Report •

#15
February 26, 2016 at 06:15:18
My PowerShell version should be compatible with PS version 1, which is Vista/2008 and up. Import-Csv wasn't added until 2.0 (Win7/2008R2), I believe. The bigger question is if PS will allow scripts to run.

If you're looking for Win98 compatibility (which a batch solution will not offer), then go VBScript. I didn't throw that up as an example because there's a lot more bookkeeping involved, so I couldn't have an example in 30 seconds and 3 lines.

How To Ask Questions The Smart Way


Report •

#16
February 26, 2016 at 06:20:37
I have not had the time to test your PS script solution. The option -bypass should run to bypass execution policy I think.
XP, W7, W8 and W10 should be the expected OS.
I would prefer to stay away from VBScript

Report •

#17
February 26, 2016 at 07:22:00
PSv1 was available for XP as an optional download, but v2 was not. Similarly, Vista came with v1, but could be upgraded to v2. If XP is still required, you're looking into VBScript, which will be faster in both coding and execution time.

How To Ask Questions The Smart Way


Report •

#18
February 29, 2016 at 17:30:28
✔ Best Answer
After working with the actual csv data, (one of the fields contained a space within its content), this was the resulting batch attempt:

@echo off>output.csv & setlocal enabledelayedexpansion
set a=1,2,3,8,14,15,19,24,25,26,45,54,68,73,78,83,159,160,161
set dlm=;
for /f "eol= tokens=*" %%a in (file.csv) do (
set d=
set out=
set z=%%a
:: disable the following line if you want all the bloated space included
set z=!z: =!
::put quotes around all the fields so internal space won't throw off the index
set z=!z:;=";"!
:: this is just to give a sample of the data after pre-formatting
>>zz echo "!z!"

for %%b in (%a%) do (
set /a t=%%b-1
call :bild "!z!"
)
>>output.csv echo !out!
)
goto :eof


:bild
for /l %%z in (1 1 %t%) do shift
set out=%out%%d%
set out=%out%%~1
:: enable this if you want that trailing space in each field
rem (set out=%out% )
set d=;
goto :eof

message edited by nbrane


Report •

#19
March 1, 2016 at 15:39:58
Million thanks to Mr. nbrane.

He made the batch script work like a charm !!!


Report •


Ask Question