2

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.

Improve this question
5
  • Please describe more your environment - operating system, available scripting tools or languages. Task can be solved in many ways and it's not clear what fits to your situation. If I were you, I would use linux shell tools (namely for loop combined with sed used to extract country from file name and another sed to append column to the end of each line in CSV), though I fully understand other guy would prefer Visual Basic Excel macro :-). Commented Dec 20, 2019 at 9:05
  • @TomášZáluský Unfortunately Linux is not an option in this case. Operating system is Windows 10 and we have to operate with CSVs. The CSVs are all comma separated. Commented Dec 20, 2019 at 9:17
  • Then you can either use Powershell or install some Windows port of sed. You can find a lot of inspiration here: stackoverflow.com/questions/127318/… . If you are not familiar with any of these tools and number of your files is under say 20, it will probably be faster for you to edit in Notepad manually one at a time. Commented Dec 20, 2019 at 9:28
  • 1
    Your question requires more focus, if you wish it to receive appropriate attention. Please edit it to include, examples of the CSV content, now and afterwards. Thank you. Commented Dec 20, 2019 at 10:27
  • What have you tried, and where are you stuck? Commented Dec 20, 2019 at 14:42

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.