How to split CSV column values into other columns using batch script

Question

I'm new to batch files and this is a tricky question. In stores.csv there is a column called 'Image' which stores vertical-line-delimited image URLs as values. There are also additional columns called 'AltImage2', 'AltImage3', etc. How can I split the vertical-line-delimited string into columns that start with 'AltImage' for each row in the CSV? 'AltImage' columns only go to AltImage5, and there may not be five image URLs in a given row. I would also like to keep the first image URL in the 'Image' column if possible.

Example of headers and single row of data:

Company,Title,Image,AltImage2,AltImage3,AltImage4,AltImage5
Testco,U2X40,image1.png|image2.png|image3.png

Desired result after running batch:

Company,Title,Image,AltImage2,AltImage3,AltImage4,AltImage5
Testco,U2X40,image1.png,image2.png,image3.png

So far I've tried this:

for /f "tokens=3 delims=, " %%a in ("stores.csv") do (
    echo run command here "%%a"
)

But cannot even echo the values in the Image column.

Here is a solution using Bash script (unfortunately I need batch): How do I split a string on a delimiter in Bash?

What have you tried on your own so far? Where are you suck? Please share your efforts... Also please clarify whether there are always three items, and how the column headers are derived... — aschipfl, Aug 05 '16 at 17:01
We would also need to see the entire representation of your input and what your desired output should be. — Squashman, Aug 05 '16 at 17:08
Whoops, updated to show CSV formating. Also set Image string to vertical-line-delimited. Updated example to show all columns as well. — Aaron Turecki, Aug 05 '16 at 18:35
In its simplest form it just looks like you want to change the PIPE delimiter to a comma. — Squashman, Aug 05 '16 at 18:43
When you use `FOR /F` and you are quoting the file name in the set, you need to use the `usebackq` option. — Squashman, Aug 05 '16 at 18:44

score 1 · Answer 1 · answered Aug 05 '16 at 22:50

1

@echo off
setlocal

< stores.csv (
   rem Read and write the header
   set /P "header="
   call echo %%header%%
   rem Process the rest of lines
   for /F "tokens=1-3 delims=|" %%a in ('findstr "^"') do echo %%a,%%b,%%c
)

answered Aug 05 '16 at 22:50

Aacini

65,180
12
72
108

shawnt00 · Answer 2 · 2016-08-05T19:10:50.720

I think this handles your parsing problem. Pay attention to quotes and the usebackq option.

for /f "skip=1 tokens=3 delims=," %%a in (stores.csv) do for /f "tokens=1-5 delims=|" %%b in ("%%a") do echo %%b %%c %%d %%e %%f

Here's a fuller solution to play with. There may be a more elegant way to handle optional commas. And you'll have to handle directing the output to whichever place is appropriate.

@echo off
setlocal enabledelayedexpansion
echo Company,Title,Image,AltImage2,AltImage3,AltImage4,AltImage5
for /f "skip=1 tokens=3 delims=," %%a in (stores.csv) do (
    for /f "tokens=1-5 delims=|" %%b in ("%%a") do (
        set line=%%b
        if not "%%c"=="" set line=!line!,
        set line=!line!%%c
        if not "%%d"=="" set line=!line!,
        set line=!line!%%d
        if not "%%e"=="" set line=!line!,
        set line=!line!%%e
        if not "%%f"=="" set line=!line!,
        set line=!line!%%f
        echo !line!
    )
)

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

read the file line by line and replace | with , (you have to escape the | and use delayed expansion:

@echo off
setlocal enabledelayedexpansion
(
  for /f "delims=" %%a in (old.csv) do (
    set line=%%a
    echo !line:^|=,!
  )
)>new.csv

edited May 23 '17 at 12:01

Community

1
1

answered Aug 05 '16 at 19:25

Stephan

53,940
10
58
91

How to split CSV column values into other columns using batch script

3 Answers3