Script to add fields to a csv file

[IrritableTech]

I hope someone can help or point me in the right direction after a frustrating couple of hours trying to find a script to complete this task.

 

I need to run a process probably hourly (hence the script) to complete the following task...

 

Open a csv file in location A

Add two extra fields of data (eg data1,data2,) to the beginning of every single line

Save the resulting csv file in location B

 

I've been looking around at vbs scripts and found the following, but I'm really not very good at vbs to get to the next stage. Thanks in advance.

Sub ConvertFile(strFileName As String)
   Dim InFileID As Integer, OutFileID As Integer
   Dim strData As String
   InFileID = FreeFile
   Open strFileName For Input As #InFileID
   OutFileID = FreeFile
   Open strFileName & ".tmp" For Output As #OutFileID
   Do Until EOF(InFileID)
       Line Input #InFileID, strData
       Print #OutFileID, "data1,data2" & strData
   Loop
   Close #InFileID
   Close #OutFileID
   Kill strFileName
   Name strFileName & ".tmp" As strFileName
End Sub

[cromertech]

Try this site. It has some useful resources on what you are trying to do. VBScript Tutorial. How FSO writes data file OpenTextFile FileSystemObject

[apeo]

Ok just want to clarify, if this is run every hour or so then how is location A and B defined. For example first time you run the script it updates file1.csv and saves it as file2.csv, and the next time it runs does it open file2.csv updates that and saves it as file3.csv? or does it open file1.csv again, updates it and replaces file2.cvs? or does it open file1.csv again, updates it and saves it as file3.cvs?

[IrritableTech]

Thanks cromertech, I will take a look...

 

Thanks also apeo. To clarify, location a and b are fixed... ie. I wish to open file A (which will be regenerated every hour) make the adjustments and overwrite file b in it's fixed location.

[apeo]

Ok i think the following is what you want but i havent tested it..

 

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Const ForReading = 1 
Const ForWriting = 2 


Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
i = 0 
Do Until objFileA.AtEndOfStream 
strNextLine = objFileA.Readline

If strNextLine <> "" Then 
	strNextLine = "data1,data2," & strNextLine
	objFileB.WriteLine strNextLine & VbCrLf
End If
i = i + 1
Loop 

objFileA.Close 
objFileB.Close 

[IrritableTech]

Thanks you very much apeo that is almost perfect! It is creating a empty line after each data line, however it doesn't seem to worry the system to which I am importing. I just need to play around with a bit of formatting with "file A" and I think i'll have it cracked.

 

Where do I send the beer tokens?

[apeo]

Oops i put a break after each line and forgot that writeline writes a new line lol. Heres the updated version:

 

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Const ForReading = 1 
Const ForWriting = 2 


Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
i = 0 
Do Until objFileA.AtEndOfStream 
strNextLine = objFileA.Readline

If strNextLine <> "" Then 
	strNextLine = "data1,data2," & strNextLine
	objFileB.WriteLine strNextLine
End If
i = i + 1
Loop 

objFileA.Close 
objFileB.Close

 

FYI i just removed & VbCrLf.

[IrritableTech]

Superb! Thanks again apeo. Now I just need to work out how to change the order of my columns in my original csv file, and I'll have it cracked!

[LosOjos]

Superb! Thanks again apeo. Now I just need to work out how to change the order of my columns in my original csv file, and I'll have it cracked!

 

You'll be wanting the Split function, use it to split each line the write it back in the order you want, so say you're file has 5 columns/fields (including the two you added) and you want to write them back in reverse order, alter the code above to the following:

 

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Const ForReading = 1 
Const ForWriting = 2 


Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
i = 0 
Do Until objFileA.AtEndOfStream 
strNextLine = objFileA.Readline

If strNextLine <> "" Then 
	strNextLine = "data1,data2," & strNextLine
              [b] strSplit=Split(strNextLine,",")
	objFileB.WriteLine strSplit(4) & strSplit(3) & strSplit(2) & strSplit(1) & strSplit(0)[/b]
End If
i = i + 1
Loop 

objFileA.Close 
objFileB.Close

 

 

BTW, haven't tested that so back up your original script before you try it

[IrritableTech]

Thanks Los0jos thats fantastic. The only issue was that it stripped my commas from the resulting csv file. I've tweaked the script so that it orders the columns for my needs, and added in commas. Is this a really bad way to do it? I'm really no good at this... it does work though!

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Const ForReading = 1 
Const ForWriting = 2 


Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
i = 0 
Do Until objFileA.AtEndOfStream 
strNextLine = objFileA.Readline

If strNextLine <> "" Then 
	strNextLine = "add,student," & strNextLine
               strSplit=Split(strNextLine,",")
	objFileB.WriteLine strSplit(0) & "," & strSplit(1) & "," & strSplit(3) & "," & strSplit(2)
End If
i = i + 1
Loop 

objFileA.Close 
objFileB.Close

 

Can anyone advise me on how to get rid of speech marks (") in the resulting file?

 

Thanks again both.

[LosOjos]

Woops, didn't think of that one! You're code is exactly how I would have done it, can't see a problem with it anyway.

 

Are the speech marks causing a problem? Because they usually tell whichever piece of software that is reading the CSV that anything between those speech marks is the value for that field; including any commas; so theoretically it's better for compatibility. I'd only remove them if they're causing you a problem.

[IrritableTech]

Thanks for your reply. It does seem that the quotation marks are causing an issue. I am 99.999% sure that we will never need to import any data with commas within a field. Is it an easy function to add?

 

Cheers

[LosOjos]

Are the quotation marks in the original file?

 

If so, try altering your code to the following:

 

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Const ForReading = 1 
Const ForWriting = 2 


Set objFileA = objFSO.OpenTextFile ("c:\locationA\fileA.csv", ForReading)
Set objFileB = objFSO.OpenTextFile ("c:\locationB\fileB.csv", ForWriting)
i = 0 
Do Until objFileA.AtEndOfStream 
strNextLine = objFileA.Readline

If strNextLine <> "" Then 
	strNextLine = "add,student," & strNextLine
	[b]strNextLine = Replace(strNextLine, Chr(34), "")[/b]
               strSplit = Split(strNextLine,",")
	objFileB.WriteLine strSplit(0) & "," & strSplit(1) & "," & strSplit(3) & "," & strSplit(2)
End If
i = i + 1
Loop 

objFileA.Close 
objFileB.Close

 

 

Also, I notice you're counting the iterations of the loop with i, but never using it. Won't make a huge difference but you may as well remove those lines (unless it's used elsewhere in the script of course)

[IrritableTech]

Amazing. That has done the trick.

 

Thank you very much, to you both. If I can ever return the favour, please ask.