Script help

cotton31 · 10 Sep 2010 at 10:03

I need to write a script which can take a CSV file, read it in as text and replace any commas with " " (that's double quote - space - double quote), it also needs to add a double quote to the beginning and end of each line.

I know what it needs to do but my scripting isn't really up to scratch, I think it will be something to do with regex?

If it could rename the file too that would be great.

Can anyone help?

Tilluss · 10 Sep 2010 at 12:00

If you have a CSV file example i'll quickly whip up a c# prog for you.

cotton31 · 10 Sep 2010 at 16:32

Thanks for the offer Tilluss, that would be great! However, I can't really give you a real CSV example as it's company data.

It's just a CSV file - I've replicated what I need to do by doing a find and replace in notepad++ for the below:

commas for " "
start of line for "
end of line for "

This gives me the exact file I need.

If you definitely need an example, I could probably give you one with dummy data.

Thanks again for your offer!

cotton31 · 23 Sep 2010 at 14:59

Can anyone help with this? I've managed to write the below which runs fine, but the output file has some stange character (like a bullet point) and a double quote, rather than " " that I need.

Code:

'declare variables

Const ForReading = 1
Const ForWriting = 2

strOldFileName = Wscript.Arguments(0) 					'input file to be replaced
strFileName = "C:\Scripts\new.csv" 					'set output file
strOldText = "," 							'string to replace
strNewText = Chr(34) & " " & Chr(34) 					'string to be insert

'copy the file

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenFile(strOldFileName, ForReading)		'open initial file

strText = objFile1.ReadAll						'read in file
objFile1.Close
strSecondText = strText							'copy text to variable for new file

Set objFile2 = objFSO.CreateTextFile(strFileName, ForWriting)		'create new file
objFile2.WriteLine strSecondText					'write copied text to new file
objFile2.Close

'replace string in new file

Set objFile3 = objFSO.OpenTextFile(strFileName, ForReading)		'open the new text file

strReplaceText = objFile3.ReadAll
objFile3.Close
strLastText = Replace(strReplaceText, strOldText, strNewText)

Set objFile4 = objFSO.OpenTextFile(strFileName, ForWriting)
objFile4.WriteLine strLastText
objFile4.Close

Bizarrely, if I copy the contents of my initial file to a new CSV, the script runs as it should do (without the strange characters). Any idea why?

Cheers,