Copy Entire Column using DATASOURCE

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.
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
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Copy Entire Column using DATASOURCE

Post by wfm007 » Mon Dec 25, 2017 12:43 pm

Firefox Version: 51.0 (32-bit)
iMacros Version: 9.0.3
OS: Windows 10 64-bit

Hello Everyone,

I need help in order to copy entire column using DATASOURCE or any other command.

Actually, I can't use CONCATENATE(excel function) while saving excel as .csv or .txt due to characters limit.

That's why I need a way around to copy complete column and paste it into webpage text area.

This is the code I am using right now:

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
TAB T=1
URL GOTO=http://....
WAIT SECONDS = 5
SET !DATASOURCE MyWorkbook.txt
SET !DATASOURCE_COLUMNS 1
WAIT SECONDS = 5
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:ace_text-input&&AUTOCORRECT:off&&AUTOCAPITALIZE:off&&SPELLCHECK:false&&WRAP:off&&TXT: CONTENT={{!COL1}}
WAIT SECONDS = 5
Thanks
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Mon Dec 25, 2017 6:29 pm

wfm007 wrote:

Code: Select all

Firefox Version: 51.0 (32-bit)
iMacros Version: 9.0.3
OS: Windows 10 64-bit
Hello Everyone,

I need help in order to copy entire column using DATASOURCE or any other command.

Actually, I can't use CONCATENATE(excel function) while saving excel as .csv or .txt due to characters limit.

That's why I need a way around to copy complete column and paste it into webpage text area.

This is the code I am using right now:

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
TAB T=1
URL GOTO=http://....
WAIT SECONDS = 5
SET !DATASOURCE MyWorkbook.txt
SET !DATASOURCE_COLUMNS 1
WAIT SECONDS = 5
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:ace_text-input&&AUTOCORRECT:off&&AUTOCAPITALIZE:off&&SPELLCHECK:false&&WRAP:off&&TXT: CONTENT={{!COL1}}
WAIT SECONDS = 5
Thanks
You have several Threads on the Forum about looping/checking/searching/copying the entire DataSource, which will be the same in your Case as you only have 1 Column, with several Solutions using a '.js' Script + one Solution in pure '.iim' that actually quickly becomes the fastest one if your DataSource contains more than 50 Rows and that I have explained and demonstrated in the following Thread/Post:
- Re: Get number of lines from CSV and use as variable?
The same Thread contains as well all other Solutions mentioned in it, or at least Links to other such Threads...

A few later Threads on the Forum contain as well some "simpler" Implementations of "my" '.iim' Method... (with Code Examples as well).

One "Detail" about my Method is that your DataSource needs to have a '.TXT' Extension (or any other Extension than '.CSV'), but that's already your case with "MyWorkbook.txt", so that's good already... (I've probably explained why in the Thread I referred you to...)

One more Method that could be used in your Case is to maintain your DataSource "Horizontally" with one single Row, or to first (automatically) convert your "Vertical" DataSource to an "Horizontal" DataSource with 'Paste Special' from Excel where you can convert a Vertical Selection (or a whole Column) to its Horizontal Equivalent into one single Row that you then can retrieve from iMacros with one single '{{!COL1}}' Statement with a Trick by temporarily setting a "Fake" '!DATASOURCE_DELIMITER' from your Script. (Only works on iMacros for FF...)
- (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...
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Re: Copy Entire Column using DATASOURCE

Post by wfm007 » Tue Dec 26, 2017 6:58 am

Thankyou chivracq for your reply.

I tried the horizontal trick and it worked, however, while saving excel file as .txt it's adding double quotes(") to it.

Is there something which can be done to eliminate double quotes.

Thankyou.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Tue Dec 26, 2017 7:24 am

wfm007 wrote:Thankyou chivracq for your reply.

I tried the horizontal trick and it worked, however, while saving excel file as .txt it's adding double quotes(") to it.

Is there something which can be done to eliminate double quotes.

Thankyou.
Hum, started to reply but you edited the Content of your Reply between the moment I was "thinking" and actually hit the 'Reply'/'Quote' Button, ah-ah...!, you had mentioned about 10k Cells and FF or Excel hanging..., has now disappeared...

To answer the current Content of "my" Quote... => Yep, simply add a Global 'replace()' (to nothing = empty String) using 'EVAL()'... :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...
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Re: Copy Entire Column using DATASOURCE

Post by wfm007 » Tue Dec 26, 2017 7:35 am

:roll: Yes, I edited my reply, because double quotes were the reason for FF hang issue.

I tried the horizontal trick and removed double quotes and ta_da it worked. :D

Thank you so much for helping me with this.

I have seen so many replies from you on other post and they are amazing.

I thank you again for taking out time from your busy schedule to help others.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Tue Dec 26, 2017 6:48 pm

wfm007 wrote::roll: Yes, I edited my reply, because double quotes were the reason for FF hang issue.

I tried the horizontal trick and removed double quotes and ta_da it worked. :D

Thank you so much for helping me with this.

I have seen so many replies from you on other post and they are amazing.

I thank you again for taking out time from your busy schedule to help others.
Yeah, glad the "Horizontal Trick" worked in your Case...
(This one is not from me btw, but originally from @Shugar, some (other) "Creative" Advanced User on the SOF Forum answering many/most Threads about iMacros on that Forum... But I use it myself in some "adapted" form in one of my Macros where one Macro creates a (Temp) '.CSV' with a variable Nb of Cols that get reused in a second Macro...)

:D Nice to see that you picked up my "Explanations" pretty quickly..., maybe even a bit "too quickly" for other Users who might be interested if you could post your Final Script with your Implementation of the Horizontal Trick and the Removal for the Double Quotes... :idea:
=> + Adding a '[Solved]' Tag to the Thread Title after you've shared your Solution makes it easier for other Users to find this Thread... :idea:

Hum..., and depending on how you'll have implemented (the Global 'replace()' for) the Double Quotes Removal, I might have some mini-Test for you to run about Speed if you have a 10K Cols Test File to verify some "Allegation" sbd once posted on SOF when comparing the Speed of several JavaScript Commands, ah-ah...! :wink:
- (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...
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Re: Copy Entire Column using DATASOURCE

Post by wfm007 » Tue Jan 02, 2018 6:28 am

I am replacing double quotes with VBA code while saving excel file as a .txt file.

VBA Code:

Code: Select all

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'~~> Change this where and how you want to save the file
Const FlName = "C:\Users\.....\data.txt"

Sub Sample1()
    Dim tmpFile As String
    Dim MyData As String, strData() As String
    Dim entireline As String
    Dim filesize As Integer

        tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"

    ActiveWorkbook.SaveAs Filename:=tmpFile _
    , FileFormat:=xlText, CreateBackup:=False

        Open tmpFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

        filesize = FreeFile()

    
    Open FlName For Output As #filesize

    For i = LBound(strData) To UBound(strData)
        entireline = Replace(strData(i), """", "")
            Print #filesize, entireline
    Next i

    Close #filesize

    


End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Tue Jan 02, 2018 2:08 pm

wfm007 wrote:I am replacing double quotes with VBA code while saving excel file as a .txt file.

VBA Code:

Code: Select all

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'~~> Change this where and how you want to save the file
Const FlName = "C:\Users\.....\data.txt"

Sub Sample1()
    Dim tmpFile As String
    Dim MyData As String, strData() As String
    Dim entireline As String
    Dim filesize As Integer

        tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"

    ActiveWorkbook.SaveAs Filename:=tmpFile _
    , FileFormat:=xlText, CreateBackup:=False

        Open tmpFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

        filesize = FreeFile()

    
    Open FlName For Output As #filesize

    For i = LBound(strData) To UBound(strData)
        entireline = Replace(strData(i), """", "")
            Print #filesize, entireline
    Next i

    Close #filesize

End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function
Hum..., Okaaay..., OK-OK-OK, hum-hum...! (that's me "processing" your Post a bit loud, oops...!), so OK..., you "do" it from Excel "directly", from a 'VBA' Script, placed in a Macro, I suppose...

Yep..., why not..., interesting to know that's it's possible to do it this way, could be interesting as well for Users using the Scripting Interface with '.VBA' Code, even if that's not "exactly" (oops!) what I meant, and I'm afraid your Solution might be a little bit too "High Level" for many Users..., and I find it a little bit "cumbersome", as I think it can be done in 1 Line from iMacros directly with one fairly simple 'EVAL()' Statement with a Global 'replace()' on the Double Quotes... Even if handling Double Quotes from 'EVAL()' can sometimes be a little bit "tricky" as they need to be escaped and getting the Syntax to "work as expected" is not always completely "straightforward"...

The Solution I had in mind would be stg like...:

Code: Select all

VERSION BUILD=8820413 RECORDER=FX
TAB T=1

SET !DATASOURCE_DELIMITER #
SET !DATASOURCE Address.txt
SET !LOOP 2
SET Complete_Row EVAL("var c='{{!COL1}}'; var x,y,z; x=c.split('\"').join(''); z=x.split('#').join(','); z")
SET Complete_Col EVAL("var r='{{Complete_Row}}'; var x,y,z; z=r.split(',').join('<BR>'); z")

PROMPT COL1:<BR>_{{!COL1}}_<BR><BR>Complete_Row:<BR>_{{Complete_Row}}_<BR><BR>Complete_Col:<BR>_{{Complete_Col}}_
And yep...!, works directly, from the first Try...! :D
Content of the 'PROMPT':
COL1:
_Frank","Noris","42 iOpus Drive","San Diego","45001","California","United States","fn@abc.com_

Complete_Row:
_Frank,Noris,42 iOpus Drive,San Diego,45001,California,United States,fn@abc.com_

Complete_Col:
_Frank
Noris
42 iOpus Drive
San Diego
45001
California
United States
fn@abc.com_
For the DataSource, I used the Demo DataSource that I renamed to "Address.txt" and I (manually) added Double Quotes to the first Row of Data (=Row_2 as the File has a Header):
'Address.txt':

Code: Select all

FNAME, LNAME, ADDRESS, CITY, ZIP, STATE ID, COUNTRY ID, EMAIL 
"Frank","Noris","42 iOpus Drive","San Diego","45001","California","United States","fn@abc.com"
(Tested on iMacros for FF v8.8.2, Pale Moon v26.3.3 (=FF47), Win10_x64.)
- (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...
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Re: Copy Entire Column using DATASOURCE

Post by wfm007 » Sun Jan 14, 2018 1:08 pm

Hey chivracq,

Hope you doing good.

Need your help on the same script.

When it copies data from Datasource it doesn't show anything in TEXTAREA until I type anything manually(can be automated with iMacros), however, it ruins my final output.
to
Example: iMacros copy abc from datasource to TEXTAREA but then I enter E to show my data, however, my output becomes aEbc or abcE sometimes.

Please help.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Sun Jan 14, 2018 1:39 pm

wfm007 wrote:Hey chivracq,

Hope you doing good.

Need your help on the same script.

When it copies data from Datasource it doesn't show anything in TEXTAREA until I type anything manually(can be automated with iMacros), however, it ruins my final output.
to
Example: iMacros copy abc from datasource to TEXTAREA but then I enter E to show my data, however, my output becomes aEbc or abcE sometimes.

Please help.
URL not posted, I cannot have a look, but it sounds related to that specific Site/Page/Field to me...

You can try using the 'EVENT' Mode instead of the 'TAG' Mode to enter your Content..., or maybe just a single Click in that 'TEXTAREA' Field with the 'EVENT' Mode before and/or after filling it with the 'TAG' Statement will be enough, or some 'CLICK' Statement + some 'KEYPRESS' Statement inside the Field with some Keyboard Arrow(s)...
- (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...
wfm007
Posts: 6
Joined: Mon Dec 25, 2017 12:29 pm

Re: Copy Entire Column using DATASOURCE

Post by wfm007 » Sun Jan 14, 2018 3:46 pm

It's an internal URL and will not work from a public network.

This is my code:

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
ONDIALOG POS=1 BUTTON=OK CONTENT=
TAB T=1
URL GOTO=http://....
SET !DATASOURCE_DELIMITER @
SET !DATASOURCE data.txt
SET !DATASOURCE_COLUMNS 1
SET !CLIPBOARD {{!COL1}}
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>DIV:nth-of-type(3)>DIV" BUTTON=0
WAIT SECONDS = 5
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:ace_text-input&&AUTOCORRECT:off&&AUTOCAPITALIZE:off&&SPELLCHECK:false&&WRAP:off&&TXT: CONTENT={{!COL1}}
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>DIV:nth-of-type(3)>DIV" BUTTON=0
WAIT SECONDS = 5
EVENT TYPE=KEYPRESS SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>TEXTAREA" CHAR="e"
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>DIV>BUTTON" BUTTON=0
WAIT SECONDS = 600
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(4)>DIV>DIV>DIV>BUTTON" BUTTON=0
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(4)>DIV>DIV>DIV>UL>LI>A" BUTTON=0
WAIT SECONDS = 5
ONDIALOG POS=1 BUTTON=OK CONTENT= 
TAB CLOSE
Please help me correct code.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

Post by chivracq » Sun Jan 14, 2018 4:45 pm

wfm007 wrote:It's an internal URL and will not work from a public network.

This is my code:

Code: Select all

VERSION BUILD=9030808 RECORDER=FX
ONDIALOG POS=1 BUTTON=OK CONTENT=
TAB T=1
URL GOTO=http://....
SET !DATASOURCE_DELIMITER @
SET !DATASOURCE data.txt
SET !DATASOURCE_COLUMNS 1
SET !CLIPBOARD {{!COL1}}
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>DIV:nth-of-type(3)>DIV" BUTTON=0
WAIT SECONDS = 5
TAG POS=1 TYPE=TEXTAREA ATTR=CLASS:ace_text-input&&AUTOCORRECT:off&&AUTOCAPITALIZE:off&&SPELLCHECK:false&&WRAP:off&&TXT: CONTENT={{!COL1}}
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>DIV:nth-of-type(3)>DIV" BUTTON=0
WAIT SECONDS = 5
EVENT TYPE=KEYPRESS SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>P>QUERY-EDITOR>DIV>TEXTAREA" CHAR="e"
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(2)>DIV>DIV>DIV>BUTTON" BUTTON=0
WAIT SECONDS = 600
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(4)>DIV>DIV>DIV>BUTTON" BUTTON=0
WAIT SECONDS = 5
EVENT TYPE=CLICK SELECTOR="HTML>BODY>SECTION>DIV>DIV>DIV:nth-of-type(4)>DIV>DIV>DIV>UL>LI>A" BUTTON=0
WAIT SECONDS = 5
ONDIALOG POS=1 BUTTON=OK CONTENT= 
TAB CLOSE
Please help me correct code.
Yeah, well, not much I can do if I cannot "see" what the Script does and how it "behaves" or what it is supposed to do...
I gave you all the "Tricks" and things to try...

But you can maybe upload an HTML Saveas ("HTML only" should be enough, I think) of that Page to your Thread if you don't come out by yourself... (needs to be zipped, max 256Kb).
- (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