Extracting column data from another csv file

Discussions and Tech Support related to the iMacros Firefox Add-on, including the built-in Javascript scripting interface for playing .js files.

Moderators: Community Moderators, iMacros Moderators

Forum rules
Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the Google search box (at the top of each forum page) to see if a similar problem or question has already been addressed. This will search the entire contents of the forums as well as the iMacros Wiki.
3. We can respond much faster to your posts if you include the following information:

CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST

Answering your own posts (e.g. attempting to "bump" your topic) drops your topic from the list of unanswered threads, so it may actually receive less views.

Extracting column data from another csv file

by babarali124 on Sun Dec 03, 2017 5:57 pm

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.
babarali124
 
Posts: 17
Joined: Sat Jul 15, 2017 9:24 pm

Re: Extracting column data from another csv file

by chivracq on Sun Dec 03, 2017 7:53 pm

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)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6490
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Extracting column data from another csv file

by babarali124 on Sun Dec 03, 2017 10:41 pm

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.
babarali124
 
Posts: 17
Joined: Sat Jul 15, 2017 9:24 pm

Re: Extracting column data from another csv file

by chivracq on Sun Dec 03, 2017 11:39 pm

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)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6490
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Extracting column data from another csv file

by babarali124 on Tue Dec 05, 2017 7:49 am

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=*
babarali124
 
Posts: 17
Joined: Sat Jul 15, 2017 9:24 pm

Re: Extracting column data from another csv file

by chivracq on Tue Dec 05, 2017 8:44 am

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)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6490
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)


Return to iMacros for Firefox

Who is online

Users browsing this forum: No registered users and 12 guests

-->