February 18, 2014 at 12:31 am
Hello there,
I am facing a issue in importing CSV file to my MS SQL Table.
Let me explain my problem:
I have 2 tables:- 1# Stock_Symbol 2# Stock_Symbol_Daily_Quotes
In Stock_Symbol there are 2 columns - Stock_ID & Stock_Name
In Stock_Symbol_Daily_Quotes there are ID; Stock_ID; Price; Time; Date; High; Low; etc.
From the following link - http://finance.yahoo.com/d/quotes.csv?s=BBDB.TO+NT.TO+GE+MSFT&f=snl1d1t1ohgdr I am manually can get any stock information as CSV.
But I want to do this automatically and directly to Stock_Symbol_Daily_Quotes table. I am seeking for some T-SQL which will allow me to download the stock price in every 15 minutes.
BBDB.TO+NT.TO+GE+MSFT - this will come directly from Stock_Symbol table.
Is there a way to do that, I tried lot of option, I know if the csv file located locally I can do the import and doing dynamically and directly from web is what I am not able to do that.
Hope I am able to make you understand my query, please help.
February 19, 2014 at 9:26 am
mail2payan (2/18/2014)
Hello there,I am facing a issue in importing CSV file to my MS SQL Table.
Let me explain my problem:
I have 2 tables:- 1# Stock_Symbol 2# Stock_Symbol_Daily_Quotes
In Stock_Symbol there are 2 columns - Stock_ID & Stock_Name
In Stock_Symbol_Daily_Quotes there are ID; Stock_ID; Price; Time; Date; High; Low; etc.
From the following link - http://finance.yahoo.com/d/quotes.csv?s=BBDB.TO+NT.TO+GE+MSFT&f=snl1d1t1ohgdr I am manually can get any stock information as CSV.
But I want to do this automatically and directly to Stock_Symbol_Daily_Quotes table. I am seeking for some T-SQL which will allow me to download the stock price in every 15 minutes.
BBDB.TO+NT.TO+GE+MSFT - this will come directly from Stock_Symbol table.
Is there a way to do that, I tried lot of option, I know if the csv file located locally I can do the import and doing dynamically and directly from web is what I am not able to do that.
Hope I am able to make you understand my query, please help.
This isn't really SQL's domain. Using the right tool for the job is important.
Other than loading your CSV file, you would want to look at a front end programming language like C# to do
what you want.
Do you need to analyse the stock quotes on the fly? Maybe StreamInsight might be an option for you?
CLR, Service Broker, maybe, but not SQL.
February 19, 2014 at 5:39 pm
We use a webservice and connect to it via SSIS to import our quotes. It's really easy. Here is a good walk through..
http://www.bidn.com/blogs/BillBrannen/bidn-blog/619/ssis-web-service-task-step-by-step
February 19, 2014 at 6:16 pm
I have seen a few other people use StreamInsight for this type of thing. They used it to import Ticker data all day long.
http://technet.microsoft.com/en-us/library/ee362541.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 10:57 pm
So, you're just looking at a way to automate the download of the csv from that link every 15 minutes?
StreamInsight sounds a little extreme. SSIS would be my weapon of choice, with a script task to do the download, but you could also just write a simple VBScript file or use a command line download tool like WGet and schedule it before your T-SQL import in a SQL Agent job.
February 19, 2014 at 11:43 pm
Hello all SQL Gurus,
Thank You for your all valuable suggestions.
I am a very basic developer just do know how to create table & views in MS SQL, dont have deep knowledge of using MS SQL (SSIS, etc.), but the place where am working they are something who dont want to hire any expert for this but want to get job by any mean through me only. So I am in deep trouble now.
@CK2; @Code-1029433; @SQLRNNR - From your instructions I went through all the links, but didn't understand how to start. But still looking on the SSIS.
@HowardW - Can I do everything via SQL Agent Job, is there a way to download CSV directly writing some T-SQL in the job itself?
February 20, 2014 at 2:12 am
As I said earlier, tSQL won't be your tool of choice for this, in a sql agent job or otherwise.
If this is all new to you then I would keep it as simple as possible.
The SSIS approach seems a viable one and a link to how you might do it was posted by someone earlier.
HowardH also threw in a couple of other options that may well be simpler.
If you take on any the approaches and don't understand how to do something specific, you could always ask.
February 20, 2014 at 2:25 am
Okay! I will start working on the same and let you know. Thanks again
Just one question to ask, where should I get SSIS is MS SQL 2008?
February 20, 2014 at 2:39 am
mail2payan (2/20/2014)
Okay! I will start working on the same and let you know. Thanks againJust one question to ask, where should I get SSIS is MS SQL 2008?
Yes, SSIS stands for SQL Server Integration Services and comes with SQL Server.
To pull up the dev. environment you design packages in go:
Start
Programs
Microsoft SQL Server 2008/R2
SQL Server Business Intelligence Studio
You develop SSIS Packages within that environment.
Here is a link to a tutorial to help you get started with creating your first package if you haven't before:
http://msdn.microsoft.com/en-us/library/ms167031(v=sql.105)
In future, at least until you become a little more comforatable with the technology, you might be better off posting in the newbie section as it is easy for people to make assumptions of your level of knowledge when you post elsewhere.
February 20, 2014 at 3:04 am
Got it. Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply