Copy Entire Column using DATASOURCE

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.

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.

Copy Entire Column using DATASOURCE

by wfm007 on Mon Dec 25, 2017 5:43 am

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
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Mon Dec 25, 2017 11:29 am

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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

by wfm007 on Mon Dec 25, 2017 11:58 pm

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.
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Tue Dec 26, 2017 12: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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

by wfm007 on Tue Dec 26, 2017 12: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.
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Tue Dec 26, 2017 11:48 am

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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

by wfm007 on Mon Jan 01, 2018 11:28 pm

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
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Tue Jan 02, 2018 7:08 am

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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

by wfm007 on Sun Jan 14, 2018 6:08 am

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.
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Sun Jan 14, 2018 6:39 am

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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Copy Entire Column using DATASOURCE

by wfm007 on Sun Jan 14, 2018 8:46 am

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.
wfm007
 
Posts: 6
Joined: Mon Dec 25, 2017 5:29 am

Re: Copy Entire Column using DATASOURCE

by chivracq on Sun Jan 14, 2018 9:45 am

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)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: 6687
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)


Return to Data Extraction and Web Screen Scraping

Who is online

Users browsing this forum: No registered users and 3 guests

-->