[tarquel]

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.

This post has been edited by it_ybd: 15 September 2005 - 05:30 AM

[Martin Zugec]

Its not easy to perform this using internal commands, it is much easier to download some external utility to do this...

[phkninja]

Open wordpad or another txt editor with replace capabilities
Do search and replace, replace , with ",".
Replace the last field {filed N) with FieldN"
Replace Field1 with "Field1

This should do it for you.

[tarquel]

@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 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.

[IcemanND]

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.

[IcemanND]

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

[Martin Zugec]

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

[IcemanND]

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.

[tarquel]

hehe Thanks for the reply's guys

All handy to know guys [always forget how to do the 'for...do...' command hehe]

Cheers
N.