Jump to content

[CMD] Find and replace characters in a csv file


Recommended Posts

Hi all

Just wondering if anyone knows what the best way of doing the following would be in a CMD / BAT script:

I have a csv list of entries exported from excel and I need to have the "'s around the items i.e.

FIELD1,FIELD2,FIELD3

will need to be:

"FIELD1","FIELD2","FIELD3"

Can anyone suggest a way of doing this via a batch file please? It probably pretty obvious but i'm a clutz with these kind of comands ;)

Thanks,

Nath.

Edited by it_ybd
Link to comment
Share on other sites


@Martin:

Thanks for the reply - thought you were gonna say that ;)

@phkninja:

Well thats theway i have been doing it, but the whole point of making a script is that its a) more full-proof and B) quicker [and i suppose c) idot proof ;)] as it was gonna be a way for other users to be able to "prep" a csv file thats been exported from a list (my sig gives you a clue as to what it is hehe)

About 300 entries and the starting and ending fields arent a fixed word so you couldn't do the last part of what you wrote.

I worked out a fairly quick way of doing it in notepad - wordpad isnt preferable here incase formating info happen to slip into it or the users might save it as something else etc) - but in the end its pointless as I got a template for the chosen program that exports the info list correctly for another program to import :)

Thanks for your suggestion tho.

Regards all

Nath.

Link to comment
Share on other sites

is it always a fixed number of fields per line? if it is and its not too many you could do this:

for /f "tokens=1,2,3* delims=," %%i in (test.csv) do (
 echo "%%i","%%j","%%k","%%l">>done.csv
)

this does four items per row.

If you have a variable number of items per line or more than just a few it would be easier to do with a VB script.

Link to comment
Share on other sites

Where there's a will there's a way, one catch though if you have an enpty field so you end up with consecutive commas you will lose a field in that line, if there is a space between them then the space will end up in quotes.

field1,field2,field3,,field5 returns "field1","field2","field3","field5"

setlocal enabledelayedexpansion
type>done.csv 2>nul
set line=
for /f "delims=" %%i in (test.csv) do (
set line=
call :fullline "%%i"
echo !line!>>done.csv

)

goto EOF

:fullline
for /f "tokens=1* delims=," %%j in (%1) do (
if "!line!"=="" (
 set line="%%j"
) else (
 set line=!line!,"%%j"
)
call :fullline "%%k"
)
goto EOF

:EOF

Link to comment
Share on other sites

2itibd: I love to try&learn, however this specific task (search&replace) is really hard-to-achieve with internal commands (I mean it is not worth trying)...

As you can see on Iceman example, it can be performed, however I thing using external utility for this task is much better and error-prone.

I know this is not related to your problem, however this is the method I am something implementing search&replace operations=

In batches you can implement this syntax:

Set var=%var:searchfor=replace%

Like for example

Set test=1,2,3
Set test=%test:3=1%

The variable %test% will now have values 1,2,1...

Maybe you find it useful sometime. If you are interested in advanced batch scripting, check my blog

Link to comment
Share on other sites

I always forget about the replace function in the SET commmand. Here's a shorter version which takes care of the empty field problem

setlocal enabledelayedexpansion
type>done.csv 2>nul
set line=
for /f "delims=" %%i in (test.csv) do (
set line="%%i"
set repl=!line:,=","!
echo !repl!>>done.csv
)

and it's alot shorter too.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...