Batch file to convert list to csv

Hewlett-packard / Iq505
January 22, 2009 at 20:34:03
Specs: Windows Vista , Dual Core/4GB
Hello again batch friends. I have lots of text files in a folder that are lists of ingredients that are always in the following format...

2 Layer Banana Cake


1/2 Cup oil
2 3/4 Cup whole wheat pastry flour
1 Cup honey
2 eggs, separated
2½ Tsp cellu or homemade baking powder
1 large mashed banana (3/4 cup)
½ Tsp salt
½ Cup milk, soy

I need a batch file that can insert and change a few lines of text and then insert a comma after the number portion and then a second comma after the unit of measure so that the file will look like this...

2 Layer Banana Cake

Estimated Yield:

Actual Yield:

Serving Size:

1/2, Cup, oil
2 3/4, Cup, whole wheat pastry flour
1, Cup, honey
2, eggs, separated
2½, Tsp, cellu or homemade baking powder
1, large mashed banana (3/4 cup)
½, Tsp salt
½, Cup, milk, soy

And to add icing on the cake, (I know it was a lame joke but I couldn't help it ;) I also would like to insert a comma after an entry is found based on another plain text file called Ingredients.txt that has a csv list of all ingredients that are known to my kitchen. I hope this is fun enough for some of you to help me with. :)

See More: Batch file to convert list to csv

Report •

January 23, 2009 at 05:19:06
oh... one more thing, I am not sure whether or not a comma should be at the end of each line when a text file is converted to a csv file so please advise. Thanks friends. :)

Report •

January 24, 2009 at 05:49:03
hmmm.. I am starting to wonder if this is even possible in a batch file since no one is responding. It sure is getting lonely in this thread....

Report •

January 25, 2009 at 00:48:34
It shouldn't be too hard to do in batch. I'd probably do it in VBScript, but that's my script language of choice. At first glance, the logic seems doable for Command Scripts, if not messy.

Report •

Related Solutions

January 25, 2009 at 07:30:44
Hi Razor. Thanks for the reply. Since I am a newborn in batch scripting, VB script almost sounds like swearing to me in another language... Now if you or someone else can or is willing to walk me step by step through the process to make a vb script that will do what I have described in this thread, I will be more than happy to experiment with the foreign language of Visual Basic Scripting. :) Thanks again.

Report •

January 25, 2009 at 17:06:50
hmmmm... just when I thought I might be able to solve this riddle with my new batch powers, I realize the error of my ways... I can see all kinds of reasons why these files would refuse to accept any operations that I wish to perform on them... For example... I just learned from IVO and Judago that the FOR command can extract what I call "token chunks" of data. The problem that I see in this file so far is that the token chunks are not always the same size... hmmm. The ingredients are sometimes 1 cup and sometimes 1 1/2 cups... so for the first of these that would be 2 "token chunks" and the second one would be 3 "token chunks." Now what? If I use a space as the delimiter, then the token chunks won't be the same for all lines will they?

Report •

January 26, 2009 at 03:45:29
At the risk of nit-picking:

2, eggs, separated

Here, "eggs" is not a unit of measure. It should be part of the ingredients (so that would be the third token.) So the above should be:

2, , "eggs, separated"

(The quotes are there to make what's inside them be read as a single token.)

P.S. I would have used two bananas. But then it depends on how large your banana is...

Report •

January 26, 2009 at 05:45:53
Hi klint! Welcome to my lonely thread where it seems I have created an impossible batch challenge. lol Anyway... the item you pointed out is just another reason why it seems this might not be possible in a batch operation since these recipes often have oddball things like the comma after the eggs and the repetition of the 3/4 cup measurement in the banana descpription. :( Well I think the more bananas the better so I'll certainly try stuffing more in the recipe. :=)

Report •

January 27, 2009 at 13:05:18
Nitpicking is what I live for! (Not really.)

klint: Here, "eggs" is not a unit of measure. It should be part of the ingredients (so that would be the third token.) So the above should be:
This brings up an interesting point. I'd consider an egg is a perfectly valid unit of measurement. It's also a valid ingredient. Should, "'egg', 'separated'," "'egg, separated'," or "'egg', 'egg, separated'?" Such are the design decisions that must be made.

klint: P.S. I would have used two bananas. But then it depends on how large your banana is...
I have a dirty, dirty mind.

I did say I'd do it in VBScript, and so I did. The output probably won't be perfect, but I work with what I got. (Mostly apathy.)

Also: the best language for this would be Perl.

Usage: Save this as a .VBS. Then call it from a Command Script like so:
cscript //nologo <inFile> <outFile.csv>

Option Explicit 'Very important. Never start a VBScript without this line.

Dim inFileName, outFileName, out, fso, errorLevel
inFileName  = WScript.Arguments(0)
outFileName = WScript.Arguments(1)
Set out = WScript.StdOut
Set fso = CreateObject("Scripting.FileSystemObject")

out.WriteLine "In:  " & inFileName
out.WriteLine "Out: " & outFileName

errorLevel = Main()

out.WriteLine "Done"
WScript.Quit errorLevel

'Returns the next line, ignoring blank lines.
'Returns an empty string if EoF is hit
Function GetNextLine(file) 'As String
	Dim ret
	ret	= ""
	Do Until file.AtEndOfStream Or ret <> ""
		ret = Trim(file.ReadLine)
	GetNextLine = ret
End Function

'Gets the start of our recipe information.
'Assumes the first line has the recipe's name, and the following line
'is the serving size.
'lastLine will return the last line read if it didn't find the servings.
'This will probably need work; your example seems incomplete.
Function GetHeader(file, ByRef lastLine) 'As String
	Dim ret, line
	'Find the name of the recipe
	ret = GetNextLine(file) & vbNewLine
	ret = ret & vbNewLine & "Estimated Yield: " & vbNewLine & _
	            vbNewLine & "Actual Yield: " & vbNewLine & _
	            vbNewLine & "Serving Size: "
	'Find the # of servings
	line = GetNextLine(file)
	If CBool(InStr(1, line, "serving", 1)) Then
		Dim token, servings
		servings = 1
		For Each token In Split(line)
			If IsNumeric(token) Then
				servings = CInt(token)
				Exit For
			End If
		Next 'token
		ret = ret & servings
	Else 'If we're here, then something went wrong
		ret = ret & "1"
		lastLine = line
	End If
	GetHeader = ret & vbNewLine
End Function

'Is it a measurement (Y/N)?
Function IsMeasurement(str) 'As Boolean
	Dim ret, s
	ret = False
	s = LCase(str)
	'Everything in Measurements must be lowercase
	Dim m, Measurements
	Measurements = Array("cup", "eggs", "egg", "tsp", "tbsp", "dash", "mashed")
	For Each m In Measurements
		If s = m Then
			ret = True
			Exit For
		End If
	Next 'm
	IsMeasurement = ret
End Function

'Parses the line and adds the commas. Most of our time will be spent here.
Function Parse(line) 'As String
	Dim ret(2), i, tokens
	tokens = Split(line)
	'Part 1: The amount
	For i = 0 To UBound(tokens)
		tokens(i) = LTrim(Replace(tokens(i), "¼", " 1/4"))
		tokens(i) = LTrim(Replace(tokens(i), "½", " 1/2"))
		tokens(i) = LTrim(Replace(tokens(i), "¾", " 3/4"))
		If IsNumeric(Right(tokens(i), 1)) And IsNumeric(Left(tokens(i), 1)) Then 
			'This token is a number
			ret(0) = LTrim(ret(0) & " " & tokens(i))
			'We're past the amount
			Exit For
		End If
	Next 'i
	'Part 2: The measurement
	For i = i To UBound(tokens)
		tokens(i) = Replace(tokens(i), ",", "")
		ret(1) = ret(1) & " " & tokens(i)
		If IsMeasurement(tokens(i)) Then
			i = i + 1
			Exit For
		End If
	Next 'i
	ret(1) = LTrim(ret(1))
	'Part 3: The rest
	For i = i To UBound(tokens)
		ret(2) = ret(2) & " " & tokens(i)
	Next 'i
	ret(2) = """" & LTrim(ret(2)) & """"
	Parse = Join(ret, ",")
End Function

'The main body of the script; its return value will be the script's ErrorLevel.
Function Main() 'As Integer
	Dim ret
	Dim inFile, outFile
	Dim line
	ret = 1
	Set  inFile = fso.OpenTextFile(inFileName, 1)
	Set outFile = fso.OpenTextFile(outFileName, 2, True)
	line = ""
	outFile.WriteLine GetHeader(inFile, line)
	If line = "" Then _
		line = GetNextLine(inFile)
	Do Until inFile.AtEndOfStream
		ret = 0
		line = GetNextLine(inFile)
		outFile.WriteLine Parse(line)
	Main = ret
End Function

Report •

January 27, 2009 at 16:50:53
oh wow!!! Razor, I thought I had a lot to learn with batch scripting!!

I just don't know if I have the courage to try this new language... I just don't know.... :( But I guess I have no choice since very few guys are commenting in this thread so I guess I can accept their silence as some indication that this cannot be done with batch scripting. Well I think it will take my feeble brain a few days or maybe even weeks to absorb the huge chunk of script that is above so I will do my best to post reports on how its going. Thanks a million for the help so far Razor. I will test it for sure since you took the time to post it. I'm just trembling, that's all... lol.

Report •

January 27, 2009 at 19:28:53
Just a warning, but CMD has all but abandoned by MS as a scripting platform, as has VBScript. These days, MS is all about PowerShell. (PS may even be included w/ Win7.)

If you want to learn something you can take forward, try PS or something non-MS, like Python or Perl.

Report •

Ask Question