Page 1 of 1

Manipulating a CSV file before running iMacros on it

Posted: Fri Mar 23, 2007 2:50 pm
by Hannes, Tech Support
Assume, your initial CSV consistes of numbers that are 10 digits long, like

1234567890
0987654321

The web form has three textfields into which you want to enter
a) the first four digits
b) the next three
c) the remaining three.

If you are not using the scripting interface (which would be the most easy solution), but need to make use of iMacros CSV reading abilities (via. {{!COL1}} to {{!COL3}}), a new CSV is needed that manipulates the numbers to look like this:

1234,567,890
0987,654,321

Here's the script that does this manipulation (which you may find a useful basis for your own CSV file manipulations):

Code: Select all

' Global Variables
Const INPUT_CSV="c:\input.csv" 		'input file path
Const OUTPUT_CSV="c:\output.csv" 		'input file path

' File access constants
Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8

'Open input for reading
Dim objFileSystemInput, inputFile
Set objFileSystemInput = CreateObject("Scripting.fileSystemObject")
Set inputFile = objFileSystemInput.OpenTextFile(INPUT_CSV, FOR_READING, true)

'Open outout for appending 
Dim objFileSystemResult, outputFile
Set objFileSystemResult = CreateObject("Scripting.fileSystemObject")
Set outputFile = objFileSystemResult.OpenTextFile(OUTPUT_CSV, FOR_APPENDING, true)

Dim nextLine, part, tempPart

Do while not inputFile.AtEndOfStream
	
	nextLine = inputFile.ReadLine()
	
	'now here's the splitting
	tempPart = Left(nextLine,7) 'first 7 characters
	
	part = Left(tempPart,4) 'first 4 characters
	outputFile.write(part+",")
	
	part = Right(tempPart,3) 'characters 5,6,7
	outputFile.write(part+",")
	
	part = Right(nextLine,3) 'characters 8,9,10
	outputFile.write(part+vbNewLine)
	
loop

inputFile.Close;
set inputFile = nothing;
outputFile.Close;
set outputFile = nothing;
set objFileSystemInput = nothing
set ohjFileSystemResult = nothing


msgbox "Done."

CSV + Ultraedit

Posted: Tue Apr 10, 2007 2:12 pm
by shiimera
this can be done also with UltraEdit or some other text editor by selecting column mode and putting the cursor on the position where we want to add the "," character and by selecting shif scroll down on your CSV file until the EOF once done you go to the menu column and you select insert fill column and you put the "," on the message box.

Another trick if the file is big you can keep the shift selected and scroll with the scrolling bar, or by going to search and GOTO ... after that you release the button, it make the manipulation of CSV file extremely easy this way.

the iMacro FF plugin has a bug that i want to report with the csv file, it doesn't filter out space, tabs, nor the ""

thank for this great piece of software, i will buy the final version once it come out. :wink:

Posted: Tue Apr 10, 2007 2:25 pm
by Hannes, Tech Support
Thanks for your additions!

Could you describe the FF bug you found in another thread (in the FF forum) in more detail?


Hannes

Re: Manipulating a CSV file before running iMacros on it

Posted: Fri May 08, 2009 5:45 pm
by Puss
This is a nice tip for quick manipulation of csv files. Thanks.

I wish I had known about this some time ago!!

UltraEdit seems to be commercial software.

'Free' alternatives: jEdit and to a lesser extent Notepad++ also have 'column' mode editing.