Help with Bat File

April 28, 2009 at 02:09:59
Specs: Windows Server 2003
Hi,

I have a csv file having close to 59 columns delimited with comma. I might contain some fileds with a comma in it like below:
cat,bat,tom
"bot,inc",tom,bot

the field having a comma would be enclosed in a double quote. while parsing it, i am facing problems. I want to move such lines into a different file and proceed with my process.

So, I want to count my number of commas in a line. If it is 58, I want to retain that line else i want to move that line to a different file. How can i do that in a bat file? Please help me.

Sandie


See More: Help with Bat File

Report •


#1
April 28, 2009 at 02:42:50
@echo off
type "My_File.csv" | find """" > "My_File_A.csv"
type "My_File.csv" | find /V """" > "My_File_B.csv"

Where "My_File_A.csv" holds the lines with double quotes while "My_File_B.csv" all the others.

Report •

#2
April 28, 2009 at 02:49:54
But i do have records which are also as follows:

"bat",tom,cat
"bat,inc",tod,cot

So in turn, every line will have double quotes.
I just want to count the commas in a line.


Report •

#3
April 28, 2009 at 03:07:54
@echo off & setlocal EnableDelayedExpansion
for /F "delims=" %%j in ('type "My_File.csv"') do (
  set line=%%j
  set line=!line:"=!
  set line=!line: =!
  set line=!line:;=!
  set cnt=0
  for %%k in (!line!) do set /A cnt+=1
  if !cnt! gtr 59 (
    echo.%%j>> "My_File_A.csv"
    ) else (
    echo.%%j>> "My_File_B.csv"
  )
)



Report •

Related Solutions

#4
April 28, 2009 at 03:25:11
Hey IVO,

I do not see any command counting the commas or anything. what does it do?

Sandie


Report •

#5
April 28, 2009 at 03:42:07
Ciao (Hi) Sandie,

the counting is performed by the inner for loop, i.e.

   for %%k in (!line!) do set /A cnt+=1

Each line is cleaned removing " ; and spaces then feeds the for loop that counts the items (columns) as that statement acts on "set of strings" delimited by commas. For kind of precision the = is a separator too but I assume there are no one in your csv's row.

Anyway the script is no harmful at all. Try and report the result. As I said that counts the columns so the value of 59.


Report •

#6
April 28, 2009 at 03:50:21
Works like a charm.
Thanks a lot IVO.

I've tried with the following example file and it worked.
"1cat",tom,bat,baf
"2cat,inc",tot,cot,fom
3cal,bal,fil,jil
4cat,for

I've changed the "gtr 59" to "equ 4". I want exactly 59 columns to be checked so the equal to.

Thanks a lot for your help.


Report •

#7
April 28, 2009 at 04:54:00
IVO,

I am having trouble with the following type of records:
"cat",,bat,tot
"cat","bat",,tom

any solutions for this case???


Report •

#8
April 28, 2009 at 04:58:02
Never mind.. i got it..

i added the following line
set line=!line:,,=,EMPTY,!


Report •

#9
May 1, 2009 at 04:45:54
Hi Sandie, nice move.

Hi IVO,

I worked on this a few days ago and was trying to test each char for equ comma. As usual, your solution is better.


=====================================
If at first you don't succeed, you're about average.

M2


Report •


Ask Question