Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Web site data to excel columns

28 Jun 2013   #1
thanku

Windows 7 Ultimate x64
 
 
Web site data to excel columns

Is it possible to fetch some lines of data from a group of web pages and paste in to Excel?Pls view the image for required excel columns.

urls are,
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price jaiprakash associates ltd | Get Quote jaipra | BSE
------------
-----------
up to 100 urls in a text file.




Attached Thumbnails
-capture.jpg  
My System SpecsSystem Spec
.
28 Jun 2013   #2
UsernameIssues

W7 Pro SP1 64bit
 
 

When you right click on the webpage and select "View source" from the context menu - do you see the info of interest; I don't. If the info were there, you could use InetGet. They know the value of the info being presented and they probably want to discourage automated data mining.

See if there is a mobile version of the webpage.
My System SpecsSystem Spec
29 Jun 2013   #3
thanku

Windows 7 Ultimate x64
 
 

the mobile version of the site is BSE - Get Quote
only changing the last six numbers give data of different symbols.This site is more useful than the desktop version.
Ill explain my idea for autoit script.
1.a note pad contain the symbol codes(500087,500047,500030....)in different lines
2.the script use a default url- BSE - Get Quote
3.fill the space after "=" with the six no. from the note pad.thus the script can make up to 100 urls.
4.copy the required data and paste to excel columns-I dont know how to do that.
i make some change in excel columns -the "id" from html source
1-spanCname
2-strongDate
3-spanchangVal
4-strongCvalue
5,6-tdDHL ( in the site two values seperated by"/"-in excel i want two values in two columns)
7-tdWAPName
8-tdTTQW
pls reffer attached new image .


Attached Thumbnails
-new-img.jpg  
My System SpecsSystem Spec
.

29 Jun 2013   #4
UsernameIssues

W7 Pro SP1 64bit
 
 

edit: I see that you posted while I was creating my post below:

There is a mobile version of the website :-)


The basic flow of your script would be:

open/read the text file full of URLs into a variable
split that variable into an array
loop thru the array

for each URL, get the numbers on the end
http://www.bseindia.com/stock-share-price/dlf-ltd/dlf/532868/

use _INetGetSource to download the info into a variable
http://m.bseindia.com/StockReach.aspx?scripcd=532868
split that variable into an array
loop thru the array

use the string functions to find the info of interest
write the info to a comma delimited file

You can work on code to do the steps mentioned above. Use the other scripts that you have as a guide. Let me know what parts you cannot figure out.
My System SpecsSystem Spec
29 Jun 2013   #5
UsernameIssues

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by thanku View Post
~~~
4.copy the required data and paste to excel columns-I dont know how to do that.
~~~
You will not need to copy/paste anything. AutoIt can load the webpage into RAM. Then you can read thru each line of the webpage electronically while it is in RAM. There will be not browser window showing for this.

When you come upon a line that has some info of interest, you can write that info to a text file, just put a comma after the data and that comma will start a new column within Excel when you open the text file.
My System SpecsSystem Spec
29 Jun 2013   #6
thanku

Windows 7 Ultimate x64
 
 

Code:
ConsoleWrite(_INetGetSource('http://www.autoitscript.com'))
then where i can find the data for next step? I spend a lot of time googling and try help cant solve my doubts.

I made a notepad manually with all the numbers line by line.I think thats the only thing i can do.
My System SpecsSystem Spec
29 Jun 2013   #7
UsernameIssues

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by UsernameIssues View Post
~~~
The basic flow of your script would be:

open/read the text file full of URLs into a variable
split that variable into an array
loop thru the array

for each URL, get the numbers on the end
http://www.bseindia.com/stock-share-price/dlf-ltd/dlf/532868/

use _INetGetSource to download the info into a variable
BSE - Get Quote532868
split that variable into an array
loop thru the array

use the string functions to find the info of interest
write the info to a comma delimited file

You can work on code to do the steps mentioned above. Use the other scripts that you have as a guide. Let me know what parts you cannot figure out.
Try and follow the outline quoted above.

The first step is to open/read the text file full of URLs into a variable

Look back at this post:
BAT or VB file help
Quote:
$whole_file = FileRead($file)
The contents of the file we are currently working with is read and put into the variable named $whole_file.
So, you can use a line like:
$whole_file = FileRead("path to file with original URLs")
where path to file with original URLs is a real path to the original file that contains the URLs

Also in that tutorial is this:
$sentence_array = StringSplit($whole_file, @CRLF, 1)
So, you can use a line like:
$URL_array = StringSplit($whole_file, @CRLF, 1)


Using a combination of the AutoIt help file and the tutorial that explained a bit about the other script, you should be able to put together some code. Just take it one step at a time. Try and follow the outline quoted above. Post the code that you are having trouble with and I'll see if I can help you out.
My System SpecsSystem Spec
29 Jun 2013   #8
UsernameIssues

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by thanku View Post
~~~~
I made a notepad manually with all the numbers line by line.I think thats the only thing i can do.
Sorry, but you don't need that and you don't want to be doing stuff manually.

It will take us some time to work thru the outline that I posted :-(

It would be quicker if I just wrote the script for you... :-)
...but you would not learn as much if I did that :-(
My System SpecsSystem Spec
29 Jun 2013   #9
thanku

Windows 7 Ultimate x64
 
 

Thank you very much for the advice,
##for each URL, get the numbers on the end
Stock Share Price dlf ltd | Get Quote dlf | BSE ##
we can ignore this line,My text file contain only numbers.Pls check attached file.

so we need to call each line and attach with http : // m.bseindia.com / StockReach.aspx?scripcd=
what function i use for it?


Attached Files
File Type: txt symbol.TXT (152 Bytes, 4 views)
My System SpecsSystem Spec
29 Jun 2013   #10
UsernameIssues

W7 Pro SP1 64bit
 
 

Sure - if you want to start with that text file with the numbers - that is good.


Put this near the top of the script...
#include<inet.au3>

...then you can use this inside your loop:
$page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=532532", 1)

I'll show you how to add the red part after I see your code that:
...reads the file full of numbers
...splits those lines into an array
...and loops thru each line.
My System SpecsSystem Spec
Reply

 Web site data to excel columns




Thread Tools




Similar help and support threads
Thread Forum
how to divide row into 2 columns in excel
I need to create this table in Excel, How do I create table like I attached? How do I divide 2 row into 2 columns. Im using mac 2013 office. please help thx
Microsoft Office
Number data in 10 columns and 25 rows pasted from other source is text
..... and I want to convert the lot to 2 decimal place numbers. Excel has several methods, but none seem to work for conversion in bulk. Working on futher cells from a suitably formatted one does not work. Is the only way to solve my problem to manually enter the numbers elsewhere, clear the...
Microsoft Office
Can I set explorer columns for data directory within music library
As an example, The Itunes directory is in My Music. My Music is in a Music library, so the file details in explorer include album, #, etc. Within Itunes is the Apps directory and other data where "album name" doesn't make sense. I want it to display as a non-music directory. There doesn't...
General Discussion
Need to swap some data in Excel
Hi, I need to rearrange some data that are mistyped in an Excel sheet. Imagine two columns and many rows. There are some data that need to swap their places so it gets under the appropriate column. The data in this sheet are quite huge and I am thinking of a method like when clicking on those...
Microsoft Office
PDF File data into Excel Spreadsheet ?
I have WIN 7 with Excel 2003. Is it possible to transfer data contained in a .pdf file onto an Ecel spreadsheet ? If it is possible what is the procedure ?
Microsoft Office
Getting data from excel to firefox??
So... i have an excel doc with about 300 records all in a cell each, i want to get them all in to a specific field on a website in their own tab in firefox. any one know if this can be done? Thanks. D
General Discussion


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 08:46.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App