Manipulating a CSV file before running iMacros on it

Information related to the use of iMacros for form filling and data upload.
Post Reply
Hannes, Tech Support

Manipulating a CSV file before running iMacros on it

Post by Hannes, Tech Support » Fri Mar 23, 2007 2:50 pm

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."
shiimera
Posts: 1
Joined: Mon Apr 09, 2007 5:18 pm

CSV + Ultraedit

Post by shiimera » Tue Apr 10, 2007 2:12 pm

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:
Hannes, Tech Support

Post by Hannes, Tech Support » Tue Apr 10, 2007 2:25 pm

Thanks for your additions!

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


Hannes
Puss
Posts: 18
Joined: Fri May 08, 2009 2:32 pm

Re: Manipulating a CSV file before running iMacros on it

Post by Puss » Fri May 08, 2009 5:45 pm

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.
Post Reply