Extracting column data from another csv file

Discussions and Tech Support specific to the iMacros Firefox add-on.
Forum rules
iMacros EOL - Attention!

The renewal maintenance has officially ended for Progress iMacros effective November 20, 2023 and all versions of iMacros are now considered EOL (End-of-Life). The iMacros products will no longer be supported by Progress (aside from customer license issues), and these forums will also no longer be moderated from the Progress side.

Thank you again for your business and support.

Sincerely,
The Progress Team

Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the search box (at the top of each forum page) to see if a similar problem or question has already been addressed.
3. Try searching the iMacros Wiki - it contains the complete iMacros reference as well as plenty of samples and tutorials.
4. We can respond much faster to your posts if you include the following information: CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST
Post Reply
babarali124
Posts: 17
Joined: Sun Jul 16, 2017 4:24 am

Extracting column data from another csv file

Post by babarali124 » Mon Dec 04, 2017 12:57 am

Hi,
Firefox version 54
Imacros VERSION BUILD=9030808 RECORDER=FX
Windows 10 64 bit
I just wanted to know if there is a way of extracting data from the columns of 2nd csv file using the first csv file. I have done this with the text file where I give the path of the 2nd file and extract the entire data from it. But I am confused about extracting data from a particular column inside a csv file. Let me give you an example and my code below

2. CSV files
First CSV file contains column 4 = path to the second csv file
2nd CSV file contains 4 columns , I need data from column number 3 from the 2nd csv file to be extracted and used.

Below is the code for extracting data from a text file.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
TAB T=1
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE testing.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAB OPEN
TAB T=2
URL GOTO={{!COL15}}
TAG POS=1 TYPE=PRE ATTR=* EXTRACT=TXT
TAB T=1
TAB CLOSEALLOTHERS
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:keyword-textarea*form-control* CONTENT={{!EXTRACT}}
The Col15 in the above example has the value of :

Code: Select all

file:///C:\Users\Ali\Documents\iMacros\Datasources\keywords.txt
But I want to use a csv file and get a data from particular column.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting column data from another csv file

Post by chivracq » Mon Dec 04, 2017 2:53 am

babarali124 wrote:Hi,
Firefox version 54
Imacros VERSION BUILD=9030808 RECORDER=FX
Windows 10 64 bit
I just wanted to know if there is a way of extracting data from the columns of 2nd csv file using the first csv file. I have done this with the text file where I give the path of the 2nd file and extract the entire data from it. But I am confused about extracting data from a particular column inside a csv file. Let me give you an example and my code below

2. CSV files
First CSV file contains column 4 = path to the second csv file
2nd CSV file contains 4 columns , I need data from column number 3 from the 2nd csv file to be extracted and used.

Below is the code for extracting data from a text file.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
TAB T=1
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE testing.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAB OPEN
TAB T=2
URL GOTO={{!COL15}}
TAG POS=1 TYPE=PRE ATTR=* EXTRACT=TXT
TAB T=1
TAB CLOSEALLOTHERS
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:keyword-textarea*form-control* CONTENT={{!EXTRACT}}
The Col15 in the above example has the value of :

Code: Select all

file:///C:\Users\Ali\Documents\iMacros\Datasources\keywords.txt
But I want to use a csv file and get a data from particular column.
Hum..., Thread has nothing specific to iMacros for FF, the 'General' Sub-Forum would have been more appropriate... (But no need to duplicate now...)

Hum, and the Terminology you use is a bit confusing, try to keep the Term(s) "extract"/"extracting"/"extraction" when "extracting" Data from a Web-Page/Site, you don't really "extract" from a DataSource..., you can rather use "use"/"get"/"pull"/"fetch"/"retrieve"/"query"...
Even if hum..., I see, you use the Trick to open a '.TXT' File in a 2nd Tab and to "re-extract" its full Content with 'EXTRACT', so in a way, you are indeed "extracting" or "re-extracting" its Content..., but you should mention that then..., it's a bit "confusing" otherwise... (And until you needed that Functionality, I guess I was the only one using it...)

But, hum..., that Trick is a Trick or "Special" Technique I use in order to be able to retrieve very quickly the whole Content of a DataSource (as a '.TXT' File, it doesn't work properly and reliably with '.CSV' Files as it depends on a few Settings at the OS and Browser Levels...), if you want to check if that File already contains some specific Content, to avoid Duplicates for example, or if you want to know how many Rows you have in your DataSource or if you want to locate quickly a Specific Row, instead of having to loop through that (2nd) DataSource Row by Row with the "Standard" Loop Mechanism, from an '.iim' or '.js' Script...
Then like I said, it's a Trick, if you don't need that Functionality, don't use it, and treat it like a "Standard" DataSource if you only need one specific Column/Cell... => Use the '!DATASOURCE' Command again for the 2nd DataSource, you already have its Path in your 1st DataSource... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
babarali124
Posts: 17
Joined: Sun Jul 16, 2017 4:24 am

Re: Extracting column data from another csv file

Post by babarali124 » Mon Dec 04, 2017 5:41 am

Yeh , extracting isnt a correct word for it... I will keep this in mind next time.. i did try the suggestion but it turns out to be pasting the url in the csv file. But not opening it and copying the data.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE trackinglink.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
SET !DATASOURCE {{!COL1}}
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL1}}
WAIT SECONDS=300
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
TAG POS=2 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL2}}
WAIT SECONDS=1
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
Here col1 has the value =

Code: Select all

file:///C:\Users\Ali\Documents\iMacros\Datasources\testing2.csv
So it copies that and pastes into the input field.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting column data from another csv file

Post by chivracq » Mon Dec 04, 2017 6:39 am

babarali124 wrote:Yeh , extracting isnt a correct word for it... I will keep this in mind next time.. i did try the suggestion but it turns out to be pasting the url in the csv file. But not opening it and copying the data.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE trackinglink.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
SET !DATASOURCE {{!COL1}}
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL1}}
WAIT SECONDS=300
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
TAG POS=2 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL2}}
WAIT SECONDS=1
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
Here col1 has the value =

Code: Select all

file:///C:\Users\Ali\Documents\iMacros\Datasources\testing2.csv
So it copies that and pastes into the input field.
Yeah, but without the "file:///" part...!, '!DATASOURCE' needs a "real" Path or File Name, "file:///" is only the Syntax needed for opening a Local File with 'URL GOTO'...
And you might need to double the Backslashes in the Path, even if you don't have any Spaces in your Path and don't use any Double Quotes...

But hum..., simply putting "testing2.csv" in your Col_1 in the 1st DataSource should be enough as well, you don't need to specify the whole Path as your File is located in the Default 'DataSources' Folder...
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
babarali124
Posts: 17
Joined: Sun Jul 16, 2017 4:24 am

Re: Extracting column data from another csv file

Post by babarali124 » Tue Dec 05, 2017 2:49 pm

Thanks alot. It worked, Just had to use the filename and reset the datasource for the file. Below is the final code.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE trackinglink.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
SET !DATASOURCE {{!COL1}}
SET !LOOP 3
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL1}}
WAIT SECONDS=3
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
TAG POS=2 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:tsf ATTR=ID:lst-ib CONTENT={{!COL2}}
WAIT SECONDS=144
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL2}}
WAIT SECONDS=1
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting column data from another csv file

Post by chivracq » Tue Dec 05, 2017 3:44 pm

babarali124 wrote:Thanks alot. It worked, Just had to use the filename and reset the datasource for the file. Below is the final code.

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !TIMEOUT_PAGE 15
SET !TIMEOUT_STEP 0
SET !DATASOURCE trackinglink.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
SET !DATASOURCE {{!COL1}}
SET !LOOP 3
SET !DATASOURCE_LINE {{!loop}}
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL1}}
WAIT SECONDS=3
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
TAG POS=2 TYPE=DIV ATTR=CLASS:grid-edit-icon&&TITLE:Edit&&TXT:
WAIT SECONDS=1
TAG POS=1 TYPE=SPAN ATTR=TXT:Ad<SP>URL<SP>options
WAIT SECONDS=1
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:tsf ATTR=ID:lst-ib CONTENT={{!COL2}}
WAIT SECONDS=144
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TrackingTemplate CONTENT={{!COL2}}
WAIT SECONDS=1
TAG POS=6 TYPE=INPUT:BUTTON ATTR=*
OK, good-good..., hum..., except that you are going to get some probably unexpected Behaviour I reckon, because your Script will go directly from Row_2 to Row_4 in your 1st DataSource (= DS_1), skipping Row_3.

The Explanation is that iMacros only has ONE '!LOOP' Mechanism, and if the Looping for your 2 DataSources is not synced like it seems to be the case from your 2 different 'SET !LOOP' Statements, you should rather compute the Looping for DS_2 based on the Looping for DS_1... (Same Principle like for Nested Loops..., but even simpler actually in your case..., a simple "ADD !DATASOURCE_LINE 1" between the 2 DS's would already do the Trick...)
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
Post Reply