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
– aschipfl, Commented Aug 5, 2016 at 17:01
We would also need to see the entire representation of your input and what your desired output should be. — Squashman
– Squashman, Commented Aug 5, 2016 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
– Aaron Turecki, Commented Aug 5, 2016 at 18:35
In its simplest form it just looks like you want to change the PIPE delimiter to a comma. — Squashman
– Squashman, Commented Aug 5, 2016 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
– Squashman, Commented Aug 5, 2016 at 18:44

Aacini · Accepted Answer · 2016-08-05 22:50:02Z

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 5, 2016 at 22:50

Aacini

67.7k12 gold badges74 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawnt00 · Accepted Answer · 2016-08-05 19:10:50Z

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!
    )
)

Community · Accepted Answer · 2017-05-23 12:01:00Z

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, 2017 at 12:01

CommunityBot

11 silver badge

answered Aug 5, 2016 at 19:25

Stephan

56.8k11 gold badges66 silver badges97 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related