Manipulating a CSV file before running iMacros on it

Information related to the use of iMacros for form filling and data upload.

Moderator: iMacros Moderators

Manipulating a CSV file before running iMacros on it

by Hannes, Tech Support on Fri Mar 23, 2007 7:50 am

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."
Hannes, iOpus Support
Hannes, Tech Support
 
Posts: 2120
Joined: Thu Sep 21, 2006 6:27 am

CSV + Ultraedit

by shiimera on Tue Apr 10, 2007 7:12 am

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:
shiimera
 
Posts: 1
Joined: Mon Apr 09, 2007 10:18 am

by Hannes, Tech Support on Tue Apr 10, 2007 7:25 am

Thanks for your additions!

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


Hannes
Hannes, iOpus Support
Hannes, Tech Support
 
Posts: 2120
Joined: Thu Sep 21, 2006 6:27 am

Re: Manipulating a CSV file before running iMacros on it

by Puss on Fri May 08, 2009 10:45 am

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.
Puss
 
Posts: 18
Joined: Fri May 08, 2009 7:32 am


Return to How-To's and Examples for Form Filling

Who is online

Users browsing this forum: No registered users and 1 guest

Website Monitoring