Batch single CSV column to multiple with comma delimiter

Question

I'm looking to create a batch script that merges and translates the text to columns in excel using a batch script. I've managed to merge the CSV files so that they create one CSV with only one header. The data stored is comma seperated, but I'm unable to use the comma delimiter to split the single column containing the entire string into multiple colums. For instance "a,b,c" should be turned into three columns on the same row "a b c"

I hope someone will be able to help.

My code so far:


ECHO Set working directory
pushd %~dp0

ECHO Deleting existing combined file
del combined.csv

setlocal ENABLEDELAYEDEXPANSION

REM set count to 1
set cnt=1

REM for each file that matches *.csv
for %%i in (*.csv) do (
REM if count is 1 it's the first time running
  if !cnt!==1 (
REM echo ^"sep=,^" >> combined.csv
REM push the entire file complete with header into combined.csv - this will also create combined.csv
    for /f "delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
REM otherwise, make sure we're not working with the combined file and
  ) else if %%i NEQ combined.csv (
REM push the file without the header into combined.csv
    for /f "skip=1 delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
  )
REM increment count by 1
  set /a cnt+=1
)

PAUSE```

Powershell can replace delimiters for you the correct way. You can either do full powershell with something like this, by simply doing it foreach in a loop. doing a single file will be like: powershell "Import-Csv .\test.csv -Delimiter , | Export-Csv -delimiter "t" .\temp.csv -NoTypeInformation" or incorporate it into your batch file in a for loop.. for %%i in (*.csv) do powershell.... — Gerhard

Mʀ Dʜʌŋʑ Mʀ Dʜʌŋʑ · Accepted Answer · 2020-11-05T14:19:44

You just need to use String Replace, for example, the previous delimiter is a space character,

@echo off 
set "str=a b c"
echo %str%     
set "str=%str: =,%"
echo %str%

Output will be :

a,b,c

Why you make this? Excel now already support space char delimited. Just change the file extentions.

Batch single CSV column to multiple with comma delimiter

1 Answers