Extracting Data results in ugly output

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.
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.
Post Reply
RAEALI
Posts: 1
Joined: Tue Aug 14, 2018 8:51 pm

Extracting Data results in ugly output

Post by RAEALI » Tue Aug 14, 2018 9:05 pm

I am trying to scrape data from the website: https://reports.myreca.ca/publicsearch.aspx

The table that is generated is an ASP.NET reportviewer table.

I am providing data source in a CSV file, and I plan on setting this to loop for the amount of records we have.

The problem is that the saved file format is less than diserable and near impossible to work with.

My script:

VERSION BUILD=12.0.501.6698
'Uses a Windows script to submit several datasets to a website, e. g. for filling an online database
TAB T=1
TAB CLOSEALLOTHERS
' Specify input file (if !COL variables are used, IIM automatically assume a CSV format of the input file
'CSV = Comma Separated Values in each line of the file
SET !DATASOURCE Address.csv
'Start at line 2 to skip the header in the file
SET !LOOP 2
'Increase the current position in the file with each loop
SET !DATASOURCE_LINE {{!LOOP}}
' Fill web form
URL GOTO=https://reports.myreca.ca/publicsearch.aspx
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:Button1
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TextBox2 CONTENT={{!COL1}}
WAIT SECONDS=3
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:Button3

TAG POS=1 TYPE=TABLE ATTR=ID:ReportViewer1_fixedTable EXTRACT=TXT



SAVEAS TYPE=EXTRACT FOLDER=* FILE=report.csv

--
In excel the entire output is saved on one cell and is really hard to work with. Is there a way that I can set it so the export is in somewhat workable format?



The output:

"













Legend for Sectors: Res. = Residential, Comm. = Commercial, PM. = Property Management, Rur. = Rural





Executed on: 8/14/2018 9:48:55 AM





Real Estate






Status

Licence History


First

Middle


Last


AKA


Brokerage


City

Class

Issue Date

Real Estate Sectors

Authorized

View

Greg

Alan

Steele

EXCELLENCE REAL ESTATE EDMONTON LTD. O/A RE/MAX EXCELLENCE

Edmonton

Associate

10/1/2017



Res. Comm. PM. Rur.

Authorized

View

Gregory

John

Steele

GREG

MOUNTAIN VIEW REAL ESTATE INC. O/A RE/MAX REAL ESTATE (MOUNTAIN VIEW)

Calgary

Associate

10/1/2017



Res. Comm. PM. Rur."
chivracq
Posts: 7722
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting Data results in ugly output

Post by chivracq » Tue Aug 14, 2018 11:43 pm

RAEALI wrote:I am trying to scrape data from the website: https://reports.myreca.ca/publicsearch.aspx

The table that is generated is an ASP.NET reportviewer table.

I am providing data source in a CSV file, and I plan on setting this to loop for the amount of records we have.

The problem is that the saved file format is less than diserable and near impossible to work with.

My script:

Code: Select all

VERSION BUILD=12.0.501.6698
'Uses a Windows script to submit several datasets to a website, e. g. for filling an online database
TAB T=1     
TAB CLOSEALLOTHERS  
' Specify input file (if !COL variables are used, IIM automatically assume a CSV format of the input file
'CSV = Comma Separated Values in each line of the file
SET !DATASOURCE Address.csv
'Start at line 2 to skip the header in the file
SET !LOOP 2
'Increase the current position in the file with each loop 
SET !DATASOURCE_LINE {{!LOOP}}
' Fill web form   
URL GOTO=https://reports.myreca.ca/publicsearch.aspx
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:Button1
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:TextBox2 CONTENT={{!COL1}}
WAIT SECONDS=3
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:Button3

TAG POS=1 TYPE=TABLE ATTR=ID:ReportViewer1_fixedTable EXTRACT=TXT

SAVEAS TYPE=EXTRACT FOLDER=* FILE=report.csv
--
In excel the entire output is saved on one cell and is really hard to work with. Is there a way that I can set it so the export is in somewhat workable format?

The output:

Code: Select all

"



 

 
 






Legend for Sectors: Res. = Residential, Comm. = Commercial, PM. = Property Management, Rur. = Rural





Executed on: 8/14/2018 9:48:55 AM





Real Estate






Status

Licence History


First

Middle 


Last 


AKA


Brokerage


City

Class

Issue Date

Real Estate Sectors

Authorized

View

Greg

Alan

Steele
 
EXCELLENCE REAL ESTATE EDMONTON LTD. O/A RE/MAX EXCELLENCE

Edmonton

Associate

10/1/2017



Res. Comm. PM. Rur.

Authorized

View

Gregory

John

Steele

GREG

MOUNTAIN VIEW REAL ESTATE INC. O/A RE/MAX REAL ESTATE (MOUNTAIN VIEW)

Calgary

Associate

10/1/2017



Res. Comm. PM. Rur."
Hum..., some interesting Thread, finally, ah-ah....! (Sorry, it's Summertime, the Forum is a bit quiet at the moment, mostly Script-Kiddies trying to hack their Friends on the Beach...! :roll: )

But, hum, could you first "clarify' a bit your FCI...? (Read my Sig...)
=> FCIM :mrgreen:
=> v12.0: iMB v12.0 ('Full' or 'Trial') or iMacros for IE v12.0...?
+ OS...? (+ OS Language...? + Regional Settings...? (Used on iMB + IE for DataDelimiter/DataSeparator in '.CSV' Files...))

Hum, URL mentioned, perfect, but I cannot guess what you've got in your DataSource for '{{!COL1}}', if you can mention some valid Search Keyword, or post the direct URL to that Record...?

>>>

Hum, and mini-Badge earnt for being the 34th User on the Forum (ever!) to use the Word "Ugly", since 13 years the Forum exists... 8)
(I'm still the Golden-Badge Owner with 25+ Posts (from 75 Posts in total), ah-ah...!) :wink:
Hum, 75x "ugly" out of 7500 Posts = 1%, hum..., still not my "favorite" Word then I guess, and I'm afraid "CIM" or "FCIM" beat "ugly" unfortunately by at least x50 times..., oops...! :shock:

>>>

And re-hum..., you could maybe edit your OP to use the ']CODE[' Forum Tags like I did in my Quote around your Script but especially around your ugly (oops, sorry => "long") '.CSV' Output that requires a lot of Scrolling, to ease a bit the Readability of the Thread... :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: 7722
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting Data results in ugly output

Post by chivracq » Wed Aug 15, 2018 12:59 am

Hum, react a bit quickly if you expect some quick Answer/Solution, already 03h for me (NL-EUR), I'll soon be going to bed... :idea:
(And I would rather be very surprised if anybody else tried to answer your Thread..., or you'll have to wait until "my" tomorrow, 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: 7722
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Extracting Data results in ugly output

Post by chivracq » Thu Aug 16, 2018 1:53 am

Hum, you don't seem to be in a hurry, looks like..., ah-ah...! Checked the Thread but no Follow-up (yet), hum, OK, fair enough... :o
- (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...
Post Reply