CSV formatting issue with Firefox imacro data extraction

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.

CSV formatting issue with Firefox imacro data extraction

by pratiksha27.jain on Fri Mar 10, 2017 3:22 am

Browser : Internet explorer 11.0
Os : windows 7 enterprise
I MAcro :11.1.495.5175


Firefox : 49.0.2
plug in : imacros_for_firefox-8.9.7-fx
Os : windows 7 enterprise

---------------------------------------------------------


hi

running the macro from imacro is giving proper output ..but running the script with firefox is giving weired format :(.


Imacro script and output :
[u][/u]



VERSION BUILD=11.1.495.5175
TAB T=1
TAB CLOSEALLOTHERS
'SET !PLAYBACKDELAY 0.2
URL GOTO=http://20.17.236.130:7220/nbALoginModule/jsp/Links.jsp
'change login id as per the requirement
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:uid CONTENT=xxxx
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD ATTR=NAME:password CONTENT=xxxx
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:submitLogin
ONDIALOG POS=1 BUTTON=NO

SET !DATASOURCE C:\Users\pjain74\Desktop\macro\pollers_input.csv
SET !LOOP 1
SET !DATASOURCE_LINE {{!LOOP}}
'TAG POS=1 TYPE=A ATTR=TXT:LnbA<SP>Admin-IST2
TAG POS=1 TYPE=A ATTR=TXT:{{!COL1}}
'New tab opened
TAB T=2
'New tab opened
TAB T=3
TAB T=2
TAB CLOSE
TAB T=2
FRAME NAME=mainContentFrame
TAG POS=1 TYPE=TD ATTR=ID:dm0m0i0tdText
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:menu:coordmenuForm:menuFormButton
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
wait seconds=5

FRAME NAME=file
SET !EXTRACT_TEST_POPUP NO

SET !EXTRACT {{!COL1}}
SET !VAR1 poller_status_{{!NOW:ddmmyy_hh}}.csv

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
'TAG POS=1 TXTTYPE=DIV ATTR=ID:pollersTableData EXTRACT=
TAG POS=2 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=3 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"


imacro_output.JPG
perfect output from imacro script


firefox script and output :


VERSION BUILD=9030808 RECORDER=FX

TAB CLOSEALLOTHERS

TAB T=1
URL GOTO=http://20.17.236.130:7220/nbALoginModule/jsp/login4.jsp
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:test ATTR=ID:user-id CONTENT=xxxx
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:test ATTR=ID:loginPassword CONTENT=xxxx
TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:test ATTR=ID:submitLogin
SET !DATASOURCE C:\Users\pjain74\Desktop\macro\pollers_input.csv
SET !LOOP 1
SET !DATASOURCE_LINE {{!LOOP}}
TAG POS=1 TYPE=A ATTR=TXT:{{!COL1}}
'TAG POS=1 TYPE=A ATTR=TXT:LnbA<SP>Admin<SP>-<SP>IST2
TAB T=2
wait seconds=5

FRAME NAME="mainContentFrame"
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ID:logonForm ATTR=ID:logonForm:submit
TAG POS=1 TYPE=TD ATTR=ID:dm0m0i0tdText
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
wait seconds=5
FRAME NAME="file"
SET !EXTRACT_TEST_POPUP NO
SET !EXTRACT {{!COL1}}
SET !VAR1 poller_status_{{!NOW:ddmmyy_hh}}.csv

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=2 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=3 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
FF_imacro.jpg
undesired format for csv


Please help me correcting this format :(


Thank you.
pratiksha27.jain
 
Posts: 19
Joined: Wed Dec 28, 2016 12:24 am

Re: CSV formatting issue with Firefox imacro data extraction

by chivracq on Fri Mar 10, 2017 4:50 pm

pratiksha27.jain wrote:
Code: Select all
Browser : Internet explorer 11.0
Os : windows 7 enterprise
I MAcro :11.1.495.5175

Code: Select all
Firefox : 49.0.2
plug in : imacros_for_firefox-8.9.7-fx
Os : windows 7 enterprise

---------------------------------------------------------
hi

running the macro from imacro is giving proper output ..but running the script with firefox is giving weired format :(.

Imacro script and output :
[u][/u]

Code: Select all
VERSION BUILD=11.1.495.5175
TAB T=1
TAB CLOSEALLOTHERS
'SET !PLAYBACKDELAY 0.2
URL GOTO=http://20.17.236.130:7220/nbALoginModule/jsp/Links.jsp
'change login id as per the requirement
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:uid CONTENT=xxxx
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD ATTR=NAME:password CONTENT=xxxx
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:submitLogin
ONDIALOG POS=1 BUTTON=NO

SET !DATASOURCE C:\Users\pjain74\Desktop\macro\pollers_input.csv
SET !LOOP 1
SET !DATASOURCE_LINE {{!LOOP}}
'TAG POS=1 TYPE=A ATTR=TXT:LnbA<SP>Admin-IST2
TAG POS=1 TYPE=A ATTR=TXT:{{!COL1}}
'New tab opened
TAB T=2
'New tab opened
TAB T=3
TAB T=2
TAB CLOSE
TAB T=2
FRAME NAME=mainContentFrame
TAG POS=1 TYPE=TD ATTR=ID:dm0m0i0tdText
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:menu:coordmenuForm:menuFormButton
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
wait seconds=5

FRAME NAME=file
SET !EXTRACT_TEST_POPUP NO

SET !EXTRACT {{!COL1}}
SET !VAR1 poller_status_{{!NOW:ddmmyy_hh}}.csv

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
'TAG POS=1 TXTTYPE=DIV ATTR=ID:pollersTableData EXTRACT=
TAG POS=2 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=3 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"


imacro_output.JPG


firefox script and output :
Code: Select all
VERSION BUILD=9030808 RECORDER=FX

TAB CLOSEALLOTHERS

TAB T=1
URL GOTO=http://20.17.236.130:7220/nbALoginModule/jsp/login4.jsp
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:test ATTR=ID:user-id CONTENT=xxxx
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:test ATTR=ID:loginPassword CONTENT=xxxx
TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:test ATTR=ID:submitLogin
SET !DATASOURCE C:\Users\pjain74\Desktop\macro\pollers_input.csv
SET !LOOP 1
SET !DATASOURCE_LINE {{!LOOP}}
TAG POS=1 TYPE=A ATTR=TXT:{{!COL1}}
'TAG POS=1 TYPE=A ATTR=TXT:LnbA<SP>Admin<SP>-<SP>IST2
TAB T=2
wait seconds=5

FRAME NAME="mainContentFrame"
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ID:logonForm ATTR=ID:logonForm:submit
TAG POS=1 TYPE=TD ATTR=ID:dm0m0i0tdText
TAG POS=1 TYPE=TD ATTR=ID:dm0m1i0tdText
wait seconds=5
FRAME NAME="file"
SET !EXTRACT_TEST_POPUP NO
SET !EXTRACT {{!COL1}}
SET !VAR1 poller_status_{{!NOW:ddmmyy_hh}}.csv

SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=2 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"
TAG POS=3 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT
SAVEAS TYPE=EXTRACT FOLDER=C:\Users\pjain74\Desktop\macro FILE="{{!var1}}"


FF_imacro.jpg


Please help me correcting this format :(

Thank you.

OK, funny to recognize your Script with "LnbA<SP>Admin<SP>-<SP>IST2" from a few months ago..., but hum, you still don't really master the ]u[ and ]code[ Meta-Tags for the Forum, ah-ah...! (But you managed to underline the ]u[ Meta-Tag itself, I'm impressed, that's nearly Art, ah-ah...!)

But OK, I had started doing a little bit of Testing about your Topic for this Thread about 2 weeks ago (but I didn't go very far/deep as the User had not mentioned their FCI, ah-ah...!), myself using iMacros for FF v8.8.2 or v8.9.7, and hum..., "Problem" is that iMacros for FF automatically wraps any 'EXTRACT' in 'SAVEAS' with Double Quotes, which can be stripped when opening your '.CSV' 'SAVEAS' Result in Excel/OpenOffice by playing with the '.CSV' Import Options, BUT...!, when doing an 'EXTRACT' on a 'TYPE=TABLE' Object, the '!EXTRACT' Var already contains all Cells delimited with Double Quotes, and the extra automatic ones around the 'SAVEAS' cause a Double-Double Quote at the beginning and end of each Table, but you can only get rid of one with the Excel/OO Import Options, meaning that you get your whole Table in one single Cell in Excel/OO... Playing with Data Delimiter in OO with Single Quotes instead of Double Quotes can sometimes bring some avail, but still, it's not a perfect Solution...

The extra Double Quotes at the 'SAVEAS' Process, you have no Influence on it, at least from iMacros, you could still remove them manually from Notepad or by using some '.BAT' File, but the Double Quotes around each Cell after the 'EXTRACT' on 'TYPE=TABLE', yep they are already included in '!EXTRACT', and yep-yep, it is possible to remove the complete first and last ones of the whole Table, it is a bit cumbersome but it should work...

=> You'll have to do some Data Manipulation on '!EXTRACT' directly after extracting your/each Table to remove them using 'EVAL()'...
The first " can easily be removed using 'replace()', the last one is a bit more complicated, you either need to use 'length()' of the complete String of the Extract, + substring(), or instead of 'length()', you can as well use 'lastIndexOf(")' and 'substring()' as well...

The 'replace()' + 'lastIndexOf()' Method shouldn't have any effect on Data extracted on iMB or IE (because there are no Double Quotes, so those 2 Statements won't do anything unless there are Double Quotes in the Data itself) and such a Script should work I would think on all Browsers, but it remains a Workaround, we miss some Control for the Data Extraction about those automatic Double Quotes with iMacros for FF, I think I have an EnhReq about that in my EnhReq Thread...
- (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: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: CSV formatting issue with Firefox imacro data extraction

by pratiksha27.jain on Wed Apr 12, 2017 6:25 am

my data is extracted in the below format :



""""""",""Description"",""Status"",""Success"",""Failure"",""Error Message""
""""",""XML103 Document Input (N2DOCIN)"",""Waiting"",""0"",""0"",""""
"""",""Other Documents Input (N2DOCIN)"",""Waiting"",""0"",""0"",""""
"""",""Create Case from Portal (N2PORTAL)"",""Waiting"",""0"",""0"",""""
"""",""Find and Merge Informal to Formal App (N2FORMAL)"",""Waiting"",""0"",""0"",""""



what format should it be in so that i can have proper formatting.
when i prompt the data is shows a,b,c,d...etc

but when it writes data to doc,txt or csv its like "a","b","c","d"...etx ( automatically adding double quotes)


what do i do :(
pratiksha27.jain
 
Posts: 19
Joined: Wed Dec 28, 2016 12:24 am

Re: CSV formatting issue with Firefox imacro data extraction

by chivracq on Wed Apr 12, 2017 7:28 am

pratiksha27.jain wrote:my data is extracted in the below format :

Code: Select all
""""""",""Description"",""Status"",""Success"",""Failure"",""Error Message""
""""",""XML103 Document Input (N2DOCIN)"",""Waiting"",""0"",""0"",""""
"""",""Other Documents Input (N2DOCIN)"",""Waiting"",""0"",""0"",""""
"""",""Create Case from Portal (N2PORTAL)"",""Waiting"",""0"",""0"",""""
"""",""Find and Merge Informal to Formal App (N2FORMAL)"",""Waiting"",""0"",""0"",""""


what format should it be in so that i can have proper formatting.
when i prompt the data is shows a,b,c,d...etc

but when it writes data to doc,txt or csv its like "a","b","c","d"...etx ( automatically adding double quotes)

what do i do :(

Yeah but sorry, it's a bit difficult to "work" on your Cases if you follow up on your Thread only 1 month later, this Case is a bit "complex" and requires some very "precise" and time consuming Testing, many other Threads were opened on the Forum since 1 month ago that "required" my Attention, I would need to start all over again from the "beginning" for this Case but I don't feel like digging again into it, try to be a bit more "reactive" next time... :idea:

(And I've asked you to pay a bit attention to the Layout of your Posts and to use the Forum Tags to improve the Readability of your Posts but you didn't improve that part...)
- (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: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: CSV formatting issue with Firefox imacro data extraction

by pratiksha27.jain on Thu Apr 13, 2017 2:54 am

not following up....was just trying ways out to work it out!

Not sure what formatting you need.bu tthanks for your time..


If anybody else got any clue on this...will be quite thankful.


thankyou!!
pratiksha27.jain
 
Posts: 19
Joined: Wed Dec 28, 2016 12:24 am

Re: CSV formatting issue with Firefox imacro data extraction

by chivracq on Fri Apr 14, 2017 12:49 am

pratiksha27.jain wrote:not following up....was just trying ways out to work it out!

Not sure what formatting you need.bu tthanks for your time..

If anybody else got any clue on this...will be quite thankful.

thankyou!!

Yeah but sorry, 1 month is too long to follow up, I "keep" a Case in my head for maybe 1 week or 10 days, a bit depending on how many Cases / new Threads I "work" on, then I have to start again from the beginning...

I don't know what you "tried", you don't post anything about what you tried, only the Result of the saved Extraction with FF, which is perfect, even if that belonged already to your OP actually (is always better than a Screenshot), but that's it, the "raw" Content of the 'EXTRACT' Command would be needed as well, as I cannot access your Site which is behind Login&Password...

But hum, OK, I read the whole Thread again, and (hum..., nearly "impressed" by myself, ah-ah...! :oops: ), I already gave you all Info and "Things" I can think of that you need to do or that you can try in my first Reply in this Thread but you didn't do anything with all those Directions and Suggestions...

>>>

Concerning the Layout of your Posts, notice the Differences between your Original Post in this Thread which is very-very long with all your Scripts posted in it (perfect on that point, I often have to "cry and beg" for Users to post their Script, ah-ah...! :D ) and how I quoted it using the ]CODE[ formatting Tags you can use from the 'phpBB' Forum Software when posting or editing a Reply, like for Bold / Italic / Underlined / etc... This is Basic Formatting, most Forums work in the same way, at least all Forums using the 'phpBB' Forum Framework... :roll:
- (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: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: CSV formatting issue with Firefox imacro data extraction

by pratiksha27.jain on Fri Apr 21, 2017 6:06 am

.....some piece of code to extract data....then below code......

macro += "TAG POS=3 TYPE=TABLE ATTR=TXT:* EXTRACT=TXT" + "\n";

iimPlay(macro);

var str = iimGetLastExtract()

str=str.replace(/""/g,"");
//str=str.replace(/""/g,"'");
//str=str.replace(/"",""/g,"#NEXT#");
//str = str.replace(/","/g, "#NEXT#");
//str = str.replace(/""/g, "#NEXT##NEWLINE#");


*iimSet("var3",str);

macro = "CODE:" + "\n";
macro += " SET !EXTRACT {{!var3}}" + "\n";

macro += " PROMPT \" {{!EXTRACT}}\" " + "\n";

macro += "SAVEAS TYPE=EXTRACT FOLDER=C:\\Users\\pjain74\\Desktop FILE=\"pratiksha.doc\""+ "\n";

iimPlay(macro);


-------------------------------------------------



    1. i have tried to extract data and alter it using #NEXT# also...the way it does in imacro standalone app. it dint work
    2.tried to modify the data replacing double quotes also...it dint work(was same)
    3.tried to save data in doc/xls/csv...in doc format it was showing proper format but when copied back to xls/csv it showed the same thing :( and finally dint work

Capture.JPG
snapshot of data when extracted in doc


please advise if there is any other approach i have missed


thank you
pratiksha27.jain
 
Posts: 19
Joined: Wed Dec 28, 2016 12:24 am


Return to Data Extraction and Web Screen Scraping

Who is online

Users browsing this forum: Majestic-12 [Bot] and 4 guests

-->