-1

I have a csv file with commas used to separate values. I want to replace commas with semicolons via batch, but leave the commas that are inside quotations. So for example:

012,ABC,"DE,FG",345

must become:

012;ABC;"DE,FG";345

How can I do that via Batch?

Ale TheFe
  • 1,540
  • 15
  • 43
  • 1
    You seem to be asking for someone to write some code for you. Stack Overflow is a question and answer site, not a code-writing service. Please [see here](http://stackoverflow.com/help/how-to-ask) to learn how to write effective questions. – Clijsters Dec 05 '17 at 16:00
  • No dude, I'm asking for help, since I don't know how to achieve my target, as I always do here without problems...so could you help me? – Ale TheFe Dec 05 '17 at 16:01
  • 1
    What have you tried, and how has what you've tried failed? Ideally, you should provide a [MCVE] of what you've tried, and include specific information on how it failed, with error messages and/or erroneous output. [SO] is not a code-writing service; the best questions are those which provide useful information so that those who answer can guide you to devising your own correct answer. See [Ask]. – Jeff Zeitlin Dec 05 '17 at 16:05
  • This, incidentally, would be essentially trivial if PowerShell is an option. – Jeff Zeitlin Dec 05 '17 at 16:06

3 Answers3

2

If you happen to have the JREPL.BAT regular expression text processing utility (v7.9 or later), then you can use:

jrepl "," ";" /p "([\c\q]+)|\q.*?\q" /prepl "$1?{$0}:$0" /f "test.csv" /o -

Use call jrepl if you put the command within a batch script.

The original file will be overwritten. You can substitute a new file name for - if you don't want to overwrite the original.

JREPL.BAT is pure script (hybrid JScript/batch) that runs natively on any Windows machine from XP onward - no 3rd party .exe file required.

The JREPL solution works by performing the replacement in two steps.

1) The /P option breaks each line into unquoted strings and quoted strings. The /PREPL option passes unquoted strings on to the normal FIND/REPLACE, and unquoted strings are preserved as is.

2) The main FIND/REPLACE substitutes ; for ,

It is possible to reliably accomplish this with pure batch using a variant of a technique developed by jeb at 'Pretty print' windows %PATH% variable - how to split on ';' in CMD shell. Although any pure batch solution will be significantly slower than hybrid solutions like JREPL.BAT, ParseCSV.bat, or a powershell solution.

Here is a batch script derived from jeb's technique - simply pass the name of the CSV file as the one and only argument. The original file will be overwritten. It should be trivial to modify the script to write the output to a new file instead. See jeb's post for an overview of how this seemingly magical technique works.

@echo off
setlocal disableDelayedExpansion
>"%~1.new" (
  for /f usebackq^ delims^=^ eol^= %%A in ("%~1") do (
    set "ln=%%A"
    call :repl
  )
)
move /y "%~1.new" "%~1" >nul
exit /b

:repl
set "ln=%ln:"=""%"
set "ln=%ln:^=^^%"
set "ln=%ln:&=^&%"
set "ln=%ln:|=^|%"
set "ln=%ln:<=^<%"
set "ln=%ln:>=^>%"

set "ln=%ln:,=^,^,%"
set ln=%ln:""="%
set "ln=%ln:"=""%"

set "ln=%ln:,,=;%"
set "ln=%ln:^,^,=,%"
set "ln=%ln:""="%"
setlocal enableDelayedExpansion
echo(!ln!
exit /b

The script should be able to process almost any valid CSV file input. The only restrictions are:

  • Empty lines are stripped from the output (should not be a problem with CSV)
  • Line lengths are limited to around 8 kb. The exact limit is dependent on how many intermediate substitutions must be performed.
dbenham
  • 127,446
  • 28
  • 251
  • 390
1

Powershell is probably the better solution but you can use a neat hybrid batch file called ParseCSV.bat. It allows you to specify the input and output delimiters. The input delimiter uses a comma by default. So you only need to specify the output delimiter.

ParseCSV.bat /o:; <"file.csv" >"filenew.csv"
Squashman
  • 13,649
  • 5
  • 27
  • 36
  • I forgot about that utility! (or at least I forgot it could be useful for something like this) – dbenham Dec 05 '17 at 18:51
  • @dbenham, let me refresh your [memory](https://stackoverflow.com/a/42324094/1417694) – Squashman Dec 05 '17 at 18:56
  • Well how about that :-) Note that ParseCSV will enclose all columns in quotes. Normally that should not be a problem, but I suppose it could be a problem for some obscure situations. – dbenham Dec 05 '17 at 19:17
  • @dbenham, I was playing around with the different /Q options. I agree it should be safer then using /Q:N. – Squashman Dec 05 '17 at 19:39
  • Actually, you should add `/Q:E` so that escaped quotes remain escaped, but all columns will still be quoted. Unfortunately I never added an option to preserve the original quotes "as is". – dbenham Dec 05 '17 at 19:56
1

This possible alternative appears to work with the single line example you've provided:

@Echo Off
If Not Exist "file.csv" Exit/B
(For /F "Delims=" %%A In ('FindStr "^" "file.csv"') Do (Set "$="
    For %%B In (%%A) Do Call Set "$=%%$%%;%%B"
    Call Echo %%$:~1%%))>"filenew.csv"
Compo
  • 36,585
  • 5
  • 27
  • 39
  • Might want to add the `@ECHO OFF` to the top. – Squashman Dec 05 '17 at 20:02
  • Certainly, but there are many valid inputs that could break the code. For example, consecutive commas (empty columns), semicolons within unquoted values, `*` or `?` within values, carets within values, possibly more. Also, you should use `findstr "^"` instead of `findstr "$"` because `$` will not match unix formatted lines (\n instead of \r\n). But I don't see why you are using FINDSTR at all. You could simply let FOR /F read the file directly. – dbenham Dec 05 '17 at 20:03
  • 1
    Added, `@Echo Off` @Squashman. There are many ways to script a solution, but there is absolutely no need to throw a brick when a pebble will do. The questioner provides the working parameters for an answer to meet; mine meets them. Every string replacement questioner could be directed to download, read the help, and use a robust hybrid script, but if their situation doesn't require it, then not offering it isn't wrong. – Compo Dec 05 '17 at 21:54