April 28, 2010 at 9:05 pm
DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)
SET @str1 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#04/21/2010,5.03,5.04,4.82,4.93,987875700,4.93#04'
SET @str1 = @str1 + '/22/2010,4.85,4.89,4.77,4.87,806903800,4.87#04/23/2010,4.87,4.95,4.83,4.86,760414400,4.86#04/26/2010,4.79,4.8,4.6,4.61,1242088300,4.61#04'
SET @str1 = @str1 + '/27/2010,4.55,4.57,4.33,4.34,1337330600,4.34#04/28/2010,4.42,4.56,4.4,4.45,1103714300,4.45#'
SET @str2 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#'
Via a table function turn this into a table
04/20/2010 5.02 5.06 4.92 4.97 1024893100 4.97
etc
etc
So I can then do a INSERT into a table.
Rules
1) There will always be 6 commas
2) Record will always end with a '#'
3) If data is missing like 04/20/2010,5.02,,,4.97,1024893100,4.97 load a null or something??
4) Varchar(max) used as some data is greater than 8000 characters.
5) ORDER of data in table is the same as it is in @str
6) To work no matter if one '#' is present (ie @Str2) or many '#' are present (ie @Str1)
Please help
Thanks:-)
Editor's Note: reformatted.
April 28, 2010 at 10:09 pm
Is this data actually coming from a text file?
April 28, 2010 at 10:25 pm
The data is not imported or anything like that, it will be passed down from the client via store procedure parameter.
So if you can work with the @str1 and @str2 variables that would be great.
So data pulled by client (asp.net project)
to
Store proc
to
table function
to
insert into table for storage
Hope this makes sense...:-)
April 28, 2010 at 10:29 pm
Two strings or one? Your initial post makes it sond like a single variable.
April 28, 2010 at 10:39 pm
Well its one table function that can handle one string at a time, no matter the number of records in the string
NOTE: This is one record..and it has one #
04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#
I have concatenated the source data with a # at the end to signify the end of a unique record, so that there are no control breaks or line breaks at the end of the record.
This makes it easier to pass down via stored proc parameter.
Hope this helps
April 28, 2010 at 10:47 pm
Not sure if you have the delimited split function, I have posted it several times here on SSC and I'm sure there are others, but using that iTVF, this is the code I ame up with:
DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)
SET @str1 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#04/21/2010,5.03,5.04,4.82,4.93,987875700,4.93#04/22/2010,4.85,4.89,4.77,4.87,806903800,4.87#04/23/2010,4.87,4.95,4.83,4.86,760414400,4.86#04/26/2010,4.79,4.8,4.6,4.61,1242088300,4.61#04/27/2010,4.55,4.57,4.33,4.34,1337330600,4.34#04/28/2010,4.42,4.56,4.4,4.45,1103714300,4.45#';
SET @str2 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#';
with InitSplit as (
select
ItemID,
Item
from
dbo.DelimitedSplit (@str1, '#')
)
select
ins.ItemID,
max(case when ds.ItemID = 1 then ds.Item else null end) as col1,
max(case when ds.ItemID = 2 then ds.Item else null end) as col2,
max(case when ds.ItemID = 3 then ds.Item else null end) as col3,
max(case when ds.ItemID = 4 then ds.Item else null end) as col4,
max(case when ds.ItemID = 5 then ds.Item else null end) as col5,
max(case when ds.ItemID = 6 then ds.Item else null end) as col6
from
InitSplit ins
cross apply dbo.DelimitedSplit(ins.Item, ',') ds
group by
ins.ItemID
;
SET @str1 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#';
with InitSplit as (
select
ItemID,
Item
from
dbo.DelimitedSplit (@str1, '#')
)
select
ins.ItemID,
max(case when ds.ItemID = 1 then ds.Item else null end) as col1,
max(case when ds.ItemID = 2 then ds.Item else null end) as col2,
max(case when ds.ItemID = 3 then ds.Item else null end) as col3,
max(case when ds.ItemID = 4 then ds.Item else null end) as col4,
max(case when ds.ItemID = 5 then ds.Item else null end) as col5,
max(case when ds.ItemID = 6 then ds.Item else null end) as col6
from
InitSplit ins
cross apply dbo.DelimitedSplit(ins.Item, ',') ds
group by
ins.ItemID
;
Let me know if you need the function dbo.DelimitedSplit.
April 28, 2010 at 11:07 pm
WOW thanks, I have the function split.
HOW can I get this result.
DECLARE @Symbol VARCHAR(20)
DECLARE @CreatedON DATETIME
SET @Symbol ='MSFT'
SET @CreatedON = getutcdate()
So I get a final result set like this see attached image
April 28, 2010 at 11:12 pm
This:
DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)
DECLARE @Symbol VARCHAR(20)
DECLARE @CreatedON DATETIME
SET @Symbol ='MSFT'
SET @CreatedON = getutcdate()
SET @str1 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#04/21/2010,5.03,5.04,4.82,4.93,987875700,4.93#04/22/2010,4.85,4.89,4.77,4.87,806903800,4.87#04/23/2010,4.87,4.95,4.83,4.86,760414400,4.86#04/26/2010,4.79,4.8,4.6,4.61,1242088300,4.61#04/27/2010,4.55,4.57,4.33,4.34,1337330600,4.34#04/28/2010,4.42,4.56,4.4,4.45,1103714300,4.45#';
SET @str2 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#';
with InitSplit as (
select
ItemID,
Item
from
dbo.DelimitedSplit (@str1, '#')
)
select
@Symbol as Symbol,
max(case when ds.ItemID = 1 then ds.Item else null end) as col1,
max(case when ds.ItemID = 2 then ds.Item else null end) as col2,
max(case when ds.ItemID = 3 then ds.Item else null end) as col3,
max(case when ds.ItemID = 4 then ds.Item else null end) as col4,
max(case when ds.ItemID = 5 then ds.Item else null end) as col5,
max(case when ds.ItemID = 6 then ds.Item else null end) as col6,
@CreatedON as CreatedOn
from
InitSplit ins
cross apply dbo.DelimitedSplit(ins.Item, ',') ds
group by
ins.ItemID
;
SET @str1 = '04/20/2010,5.02,5.06,4.92,4.97,1024893100,4.97#';
with InitSplit as (
select
ItemID,
Item
from
dbo.DelimitedSplit (@str1, '#')
)
select
@Symbol as Symbol,
max(case when ds.ItemID = 1 then ds.Item else null end) as col1,
max(case when ds.ItemID = 2 then ds.Item else null end) as col2,
max(case when ds.ItemID = 3 then ds.Item else null end) as col3,
max(case when ds.ItemID = 4 then ds.Item else null end) as col4,
max(case when ds.ItemID = 5 then ds.Item else null end) as col5,
max(case when ds.ItemID = 6 then ds.Item else null end) as col6,
@CreatedON as CreatedOn
from
InitSplit ins
cross apply dbo.DelimitedSplit(ins.Item, ',') ds
group by
ins.ItemID
;
April 28, 2010 at 11:14 pm
Thanks Lynn, I should be ok now. Will work on it tomorrow.:-):-):-)
April 29, 2010 at 12:52 am
Lynn,
Can you do this in TSQL
Copy this URL into browser address bar:
The result is a CSV file with: Date, O,H,L,C,V,AdjC records.
I read this into a vb.net asp.net application like this:
Dim History As String = String.Empty
Dim wc As New WebClient()
History = wc.DownloadString(URL)
History = History.Replace(vbCr, "")
Dim rows() As String = History.Split(ControlChars.Lf)
Questions:
1) Can TSQL do the same thing .NET webClient can ?
2) If so can TSQL get the data in a @Str1 VARCHAR(max)field for splitting into a table as you have done above?
I read this on the web.."If you use SQL Server 2005 or newer you can easily write a .NET function that can be called from T-SQL"...
So hows that done, sound just what I need !!!
Use .net and webclient to get the http csv , then use TSQL to put in a table...please comment on how to do these CALLING a .Net functions (vb.net)??
April 29, 2010 at 2:54 pm
Lynn,
I have found what I need here:
April 29, 2010 at 3:32 pm
It's a "simple" 2 dimensional split. Please see the following article for some different methods to handle such a thing.
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2010 at 12:49 am
REMOVED
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply