Batch Script - Add Column to CSV based on Filename

Question

I have a directory where several CSV are in, and the CSV file names are all like this:

prod_EU_30.csv
prod_US_30.csv
prod_CHN_30.csv
prod_UK_30.csv

etc.

For every CSV file automatically, how can I add a column with the region name to the according CSV file and add the region name as content for each line in this newly created column? For example, let's take the first one: I would like to have a column EU to prod_EU_30.csv and fill this newly created EU column with EU for each line of the CSV.

Answer 1

---

This answer and code considering:

• 1) the bat are and run in same folder where the .csv files are

• 2) you not need use recursively for look and apply edit the .csv files

• 3) you also need add the Region Name in first line off all files .csv

• 4) definitely my English is worse than i thought, and limit to me explain more, sorry!..

If you look for pure bat solution, and if your delimiter is ";", may this can help to do this job for you:

@echo off & setlocal EnableDelayedExpansion && cd /d "%~dp0" && title .\%~nx0

for /f ^tokens^=* %%i in ('%__APPDIR__%where .:*.csv')do (set "_csv=%%~fitmp"
cmd.exe /v/c <nul type nul >"!_csv!" && for /f tokens^=^2^delims^=_ %%z in ('
echo="%%~ni"')do set "_r=Region Name"&& for /f tokens^=*^delims^=^; %%I in ('
type "%%~fi"')do echo=!_r!| find "Region N" >nul && (echo=%%~I;!_r!>>"!_csv!"
set "_r=")||(echo=%%~I;%%~z>>"!_csv!"))&& move/y "!_csv!" "!_csv:~0,-3!" >nul
endlocal & exit /b

---

• Sample File prod_EU_30.csv Test Layout:

  ---

A;B;C;D;E;F;G;H;I;J;K;L;M;N
2;3;3;4;4;5;5;6;6;7;7;8;8;9
.;.;.;.;.;.;.;.;.;.;.;.;.;.
n;n;n;n;n;n;n;n;n;n;n;n;n;n
.;.;.;.;.;.;.;.;.;.;.;.;.;.
6;7;7;8;8;9;9;0;0;1;1;2;2;3

---

• Sample File prod_EU_30.csv Test Layout Results:

  ---

A;B;C;D;E;F;G;H;I;J;K;L;M;N;Region Name
2;3;3;4;4;5;5;6;6;7;7;8;8;9;EU
.;.;.;.;.;.;.;.;.;.;.;.;.;.;EU
n;n;n;n;n;n;n;n;n;n;n;n;n;n;n
.;.;.;.;.;.;.;.;.;.;.;.;.;.;EU
6;7;7;8;8;9;9;0;0;1;1;2;2;3;EU

---

• Obs.: If your delimiter is not ";", replace the correct one

  ---

---

• If you don´t need add "Region Name" in first line, try:

---

@echo off 

setlocal EnableDelayedExpansion & cd/d "%~dp0" && title .\%0

for /f ^tokens^=* %%i in ('%__APPDIR__%where ".:*.csv"')do (
  set "_f=%%~fitmp" && for /f tokens^=^2^delims^=_ %%z in ('
     echo="%%~ni"')do for /f tokens^=^*^delims^=^; %%I in ('
                    type "%%~fi"')do echo=%%~I;%%~z>>"!_f!")
                            move /y "!_f!" "!_f:~0,-3!" >nul
                                         endlocal && exit /b

---

Answer 2

It's not batch, it's a command line utility (for Linux, Max and Win). Using this Miller (https://github.com/johnkerl/miller) command

mlr -I --csv put '$regionName=FILENAME;$regionName=gsub($regionName,"^(.+)_(.+)_(.+)$","\2")' *.csv

You will have this output for every CSV file you have in the active folder.

+-----+-----+------------+
| foo | bar | regionName |
+-----+-----+------------+
| a   | z   | EU         |
| b   | y   | EU         |
+-----+-----+------------+

This script modify your source files, than use it on a copy of them.

Please note, I'm using CSV files that have , as separator.

Answer 3

(untested)

@echo off
setlocal enabledelayedexpansion
for %%f in (prod_*_30.csv) do (
  for /f "tokens=2 delims=_" %%r in ("%%f") do (
    )for /f "delims=" %%a in (%%f) do (
      echo %%a,%%r
    ))>%%~nf.new
    ECHO move /y %%~nf.new %%f
  )
)

Note: I disabled the move command for security reasons. Remove the ECHO after verifying it does what you want.