comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:
> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
> > Hi,
> > I want to select a comma delimited text file into a table.
> > Each field separated by a comma should be a field in the table.
> > e.g "faa,fee,fii,foo,fuu" should be insert as
> > field1 field2 field3 field4 field5
> > faa fee fii foo fuu
> >
> > My file is not a fixed length.
> >

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.
Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>
|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent t
o
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
>|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Comma Delimited Text File

Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.you can upload a file to the server...there are tons of articles that describe how you can do this.all you need to do is goooogle..now do you want to save the text file as it on the server and maintain a link in a table or do you want to parse the textfile and insert the records into a table ?|||I want to be able to parse the comma delimited textfile and insert the records into a table. Thanks.|||check out my blog i have an article ( under SQL Server 2000 ) tht has some sample code too..

hth|||

Hi,

Could you be able to point me to where is your blog link or the link to where the relate article for this topic. Really appreciate and thanks in advance.

comma delimited results help please

Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.

tblservers
servid servername
1 server1
2 server2
3 server3

tblapplications
appid appname
1 app1
2 app2
3 app3

tblapplink
id appid servid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 1

we want to display this information as below:

appname serverlist
app1 server1, server2, server3
app2 server1, server3
app3 server1

Thank you very much,
mrtwohttp://www.dbforums.com/showthread.php?p=3710823#post3710823|||Thank you very much! It works just as we needed it to. We appreciate you taking time to submit a reply!!

One big frustration down, most likely several more to go!

Thanks again!

mrtwo

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-

Comma delimited list of views

Is it possible to get a comma delimited list of the views in a DB?

What type of DB? Sql Server? Access? MySQL?|||

Add a reference to the Smo namespace, this will give you access to some very powerful classes for manipulating sql server. I know you can obtain a reference to a collection of all the views in a given database. Then simply do:

StringBuilder commaViews = new StringBuilder();

foreach (View vw in myDatabase.Views)
{
commaViews.Append(vw.Name + ", ")
}

|||

Jack,

Bluemistonline has a nice programmatic answer. But your question isn't clear on whether you want this for coding purposes or you just want a straight list from the database. If this is for a DBA query then see below, otheriwse the previous reply is a good answer.

selectTable_Name +','fromInformation_Schema.TableswhereTable_Type ='View'

|||

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

|||

HiJackxxx,

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

What Diamsorn suggested is almost correct. The only thing you need to do is to initialize the @.s string(or it will be recognized as NULL) .

plus, Diamsorn, why do we need to use the COALESCE function? I cannot see the reason why we need to do that.

like this:

Declare @.sAs VarChar(4000)

Set @.s=''Select @.s =@.s + Table_Name+','fromInformation_Schema.TableswhereTable_Type ='View'

Hope my suggestion helps

|||

Bo Chen – MSFT:

HiJackxxx,

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

What Diamsorn suggested is almost correct. The only thing you need to do is to initialize the @.s string(or it will be recognized as NULL) .

plus, Diamsorn, why do we need to use the COALESCE function? I cannot see the reason why we need to do that.

like this:

Declare @.sAs VarChar(4000)

Set @.s=''Select @.s =@.s + Table_Name+','fromInformation_Schema.TableswhereTable_Type ='View'

Hope my suggestion helps

Initializing the variable is always a good idea, but its not necessary in this case. Adding a varchar value to a null still gives you the varchar value. However when using COALESCE if we do initialize the varialbe 1st we will have a ',' for the start because that COALESCE was not able to handle the NULL value of @.s

The reason for using COALESCE is because it will handle null values. In the case of this example you wont have a null value coming from your information_schema. But take an example where someone was wanting a comma delimited list of say zipcodes, or another type of column that does have null values. COALESCE will handle the nulls for us. And we dont have to remove the trailing ','

|||

thanks :)

I tested your code and mine in sql and they both work fine.

Comma delimited list of IDs to a recordset from 2 tables...

Have 2 tables in SQL Server 05 DB:

First one is MyList

user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data

Second one is MyProfile

user_id -> unique value
name
address
email
phone

I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @.user_id for MyList then return all this data.

The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):

d25ef46bp3,s46ji25tn9,p53fy76nc9

The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).

Thank you!Is this assignment posted anywhere that we can read it as the teacher orignally wrote it?

-PatP|||This is for a web site project... Im stuck on this problem and need a solution. Thanks!|||Oh, Pat. I should HOPE this wasn't a homework assignment. I shudder to think that teachers would advise or condone storing data as comma-delimited strings...

L0Y4L1S3R, you can accomplish what you want joining with the LIKE() operator along with suitable wildcard characters on your string, but the result will be both inefficient and buggy. Complex coding is frequently required to make up for inadequecies in design, and ultimately you need to scrap the comma delimited strings and store that data in a subtable.|||Haha... definitely not school assignment... my first attempt at working with complex data in sql server 05.

Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.

Any way... if you can think for a stored proc that would work, please provide. Otherwise please recommend an alternate table structure.

thank you!|||Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.No no no no no no no. The correct "normalized" design is to have a subtable with up to 100 records per user_id.

Look, you can tell there is something fishy about your design because you have two table that each use user_id as a primary key. The proper design should probably be:

Table MyProfile
(user_id primary key,
name,
address,
email,
phone)

Table MyList
(user_id,
list_item,
notes)

In table MyList, user_id and list_time form a composite and unique primary key, and list_item stores one and only one item. This allows you to store as many list_items per user_id as you want, prevents a user_id from having duplicate list_items, and allows fast and easy querying.

Comma delimited list of a given field from records

I have a list that recieves a distinct field from SQL query. I want to
display this list as a comma delimited string of values, i.e. val1, val2,
val3, ...
The query looks something like this "SELECT DISTINCT val FROM TABLE"
However when I create a list associate it with a Dataset that represents the
query and then put that field inside a textbox inside the list region I get
all the values seperated by newlines. Like this,
Val1
Val2
Val3
...
This is clearly not the desired result. Is there an easy way to control the
delimiter between the records?
--
Thank you,
JohnHi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know whether it is
possible to deliver the result with the format of comma delimited in a row.
If I have misunderstood your concern, please feel free to point it out.
You may refer the sample below to build up your queries or stored procedures
use Northwind
GO
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
= 3
set @.str=left(@.str,len(@.str)-1)
print @.str
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey that would be great if I could use TSQL but my solution must be database
agnostic. If I could use TSQL I would right a SP and be done with.
What would be nice is a List aggregate function in Reporting Services that
does just what Sybase's SQL Anywhere does, i.e.
SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
So in Reporting Serices it would look like this.
=List(Fields!valueToAggregate.Value)
Can I extend Reporting Services Aggregate functions somehow so that I can
create my own list function?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know whether it is
> possible to deliver the result with the format of comma delimited in a row.
> If I have misunderstood your concern, please feel free to point it out.
> You may refer the sample below to build up your queries or stored procedures
> use Northwind
> GO
> declare @.str varchar(8000)
> set @.str=''
> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
> = 3
> set @.str=left(@.str,len(@.str)-1)
> print @.str
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Check out using a matrix and see if that will do what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||With 2005 you can:
http://msdn2.microsoft.com/en-us/library/ms165055.aspx
--
William Stacey [MVP]
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||Thanks Bruce but I am really at a loss as to how this will help me after
investigating it can you throw me a clue?
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> Check out using a matrix and see if that will do what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hey thanks, that's way cool. It appears that this modifies the actual SQL
Server instance and that would be great if we weren't using Oracle 85% of the
time. Not my choice BTW. I also found a way to create an aggregate function
for Oracle to use and I suppose I could create all kinds of aggregate
functions on all our server for each Database Vendor we support. However, I
don't think I need to tell you what is wrong with this picture.
What I really need the ability to do is create a new aggregate function that
Reporting Services uses. Or am I missing something here. When I create the
new method for SQL 2005 is it available for use as a Reporting Services
aggregate?
--
Thank you,
John
"William Stacey [MVP]" wrote:
> With 2005 you can:
> http://msdn2.microsoft.com/en-us/library/ms165055.aspx
> --
> William Stacey [MVP]
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hi,
It is not possible to use custom function in the Reporting Services
dataset, but you may try data extension.
For SQL Server 2000 Reporting Services
Custom Dataset Data Extension for Microsoft Reporting Services
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
707-56ef-4864-ac51-d83fc3273fe5
For SQL Server 2005 Reporting Services
Using an External Dataset with Reporting Services
http://msdn2.microsoft.com/en-us/library/ms152917.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Michael, even if this does not help me with this particular problem
and it well may, I it is a great tool to have in my RS bag of tricks.
I went through the sample and am having a problem with the TestDS.rdl report
on my machine. The error I am getting is -
An error has occured during the report processing.
Query execution failed for data set 'DataSet1'.
Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed.
I gave the folder that contains the .XSD file read and execute permissions
for IWAM, IUSR and local\User account but I still get that error.
What can I do to resolve this problem?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> It is not possible to use custom function in the Reporting Services
> dataset, but you may try data extension.
> For SQL Server 2000 Reporting Services
> Custom Dataset Data Extension for Microsoft Reporting Services
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
> 707-56ef-4864-ac51-d83fc3273fe5
> For SQL Server 2005 Reporting Services
> Using an External Dataset with Reporting Services
> http://msdn2.microsoft.com/en-us/library/ms152917.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi John,
I think this KB might help you
How to grant permissions to a custom assembly that is referenced in a
report in Reporting Services
http://support.microsoft.com/kb/842419
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If you can get the data into a temporary table on a SQL2005 server,
then you could use the new PIVOT function to get the list sideways! Or
just show it in a matrix.
You could certainly get the Oracle data into the SQL Server DB as a
Linked Server.
Whilst it uses T-SQL, your data source can still be any common DB
system.
Alternatively, you could do it in code using ADO.Net to read the data
in and then use your CLR language of choice to concatenate the values
together. Then use the result to populate a textbox if that's what
you're after.
Cheers
Chris
John A wrote:
> I have a list that recieves a distinct field from SQL query. I want
> to display this list as a comma delimited string of values, i.e.
> val1, val2, val3, ...
> The query looks something like this "SELECT DISTINCT val FROM TABLE"
> However when I create a list associate it with a Dataset that
> represents the query and then put that field inside a textbox inside
> the list region I get all the values seperated by newlines. Like this,
> Val1
> Val2
> Val3
> ...
> This is clearly not the desired result. Is there an easy way to
> control the delimiter between the records?

comma delimited list

Hello,
I have created a small query that will output a list of email addresses that
are separated by commas. I plan to copy and paste the list into a email
invite field (send to: field for an email app). But what is happening is tha
t
only one address appears in the field when I paste because of the added
spaces between the names, example currently outputs like:
blahblah@.blah.com,
blahblah@.blah.com,
blahblah@.blah.com
What I need is it to output like this:
blahblah@.blah.com,blahblah@.blah.com,blahblah@.blah.com
Here is the small query that I am using to extract the emails.
SELECT RTRIM(LTRIM(EMAIL_ADDRESS)) + ',' AS [Name]
FROM evPRUclinicala
WHERE CREATE_DATE BETWEEN '4/1/06' AND '4/30/06'
AND EMAIL_ADDRESS IS NOT NULL
Any help would be appreciated and thanks in advance.
Message posted via http://www.webservertalk.comIn general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the alterantives for doing it at the server, you can check out the
following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||Anith Sen wrote:
>In general, a recommended approach is to extract the resultset outside the
>server and massage the data to appropriate display format using some client
.
>Regarding the alterantives for doing it at the server, you can check out th
e
>following links:
>( For SQL 2005 only )
>http://groups.google.com/group/micr...r />
a9b9b968a
>( For SQL 2000 & 2005 )
>http://groups.google.com/group/micr...r />
66dd9e73e
>
Thanks for the direction, but I am still have some problems. I elected to us
e
the cursor version of your solution: see below
DECLARE @.temptable TABLE ( list VARCHAR( 8000 ) )
SET NOCOUNT ON
DECLARE @.PRUemail VARCHAR( 1000 ), @.emailNext VARCHAR (100)
DECLARE c CURSOR FOR
SELECT EMAIL_ADDRESS
FROM evPRUclinicala
ORDER BY EMAIL_ADDRESS
OPEN c
FETCH NEXT FROM c INTO @.emailNext
SET @.PRUemail = @.emailNext
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF @.emailNext > @.PRUemail BEGIN
INSERT @.temptable SELECT @.PRUemail
SELECT @.PRUemail = @.emailNext
END ELSE
SET @.PRUemail = COALESCE( @.PRUemail + ',', SPACE( 0 ) ) +
@.emailNext
FETCH NEXT FROM c INTO @.emailNext
END
INSERT @.temptable SELECT @.PRUemail
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM @.temptable
But I am still getting a vertical list with no commas. Can you tell me what
I
am doing wrong? Thanks
Message posted via http://www.webservertalk.com|||>> I elected to use the cursor version of your solution:
Why? It is a bad choice to begin with.
I have no idea how the table evPRUclinicala is structured or what the nature
of the data is. Can you post your table DDLs & a few sample data? For
details, refer to: www.aspfaq.com/5006
Anith|||"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:601bd1f4c21b5@.uwe...
>.
> Can you tell me what I am doing wrong?
>.
Yeah, your not using Rac :)
www.rac4sql.net/onlinehelp.asp?topic=236|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
What would have been a better choice? If I should use a different method tha
n
I would appreciate your advise here. If not I will get you the DDLs.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Cursors are generally performance hogs. In most cases, it is wiser to
utilize a set based solution in SQL where a set of rows are manipulated as a
whole rather than doing it one row at a time.
As a long term solution, consider returning the resultset to the client and
manipulate the data using a client application program -- this allows the
stored procedure to be sufficiently generic, eliminates the need to use
server's processing power for string manipulations & loop-based constructs
and leverages the string functionalities of the client language, which in
most cases are more versatile than t-SQL.
If you are convinced to use the server for such stuff, consider the table
valued UDF approach which might be better performing for medium sized
datasets. For SQL 2005, the XML kludge might be faster, though we do not
have much useful benchmarks with this approach.
Alternatively, for large volume transactions of this nature, consider using
3rd party tool's like RAC mentioned in Steve's post.
Anith|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
Ok I will look at the other solutions rather than the one chosen. Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

Comma delimited file into SQL

Hello,
I have a comma delimited file where data for each field is in " ".
I use Bulk Insert to import the file into SQL table.
How do I get rid of " " ?
thank you.UPDATE MyTable SET MyCol = REPLACE(MyCol, '"', '')
I usually like to get rid of double-quotes or other legacy problems before a
BULK INSERT with a text editor or a script... Generally more efficient that
way.
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:57F89BBC-0268-439D-B55F-350617771BC5@.microsoft.com...
> Hello,
> I have a comma delimited file where data for each field is in " ".
> I use Bulk Insert to import the file into SQL table.
> How do I get rid of " " ?
> thank you.|||You can make a nice little format file to handle the quoted text identifier.
I found a great little thread for you here:
http://groups.google.com/groups?sel...2%40tkmsftngp07
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
Books
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:57F89BBC-0268-439D-B55F-350617771BC5@.microsoft.com...
> Hello,
> I have a comma delimited file where data for each field is in " ".
> I use Bulk Insert to import the file into SQL table.
> How do I get rid of " " ?
> thank you.

comma delimited file

I am working with SQL Server 2000 table called dbo.proofofconcept".
I need to separate values that are all in one column that are space limited.
For example I have a column called tryit and the
value = '1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 '.
I want to write a query so that the results can be split out to 7 output
columns. In other words, I would like the output to be:
Column 1 = 1234,
Column 2 = 456.75,
Column 3 = 01/01/2001,
Column 4 = ABBR,
Column 5 = TRY@.GSU.ORG,
Column 6 = 75%,
Column 7 = $12.75
The output would be separated into different columns for reporting purposes,
setup to create an output file, and possibly to be split into an XML file.
How would I go about writing a query like this? If this should be a stored
procedure, can you show me how I would write the stored procedure and how
would I execute the stored procedure?
Thanks!
Here's one way to do it. Depending on where this data originates,
you might also be able bulk insert or bcp it in, in which case the import
specifications would take care of splitting it up.
create table T (
[value] nvarchar(200)
)
go
insert into T values
('1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 ')
go
select
Column1, Column2, Column3, Column4, Column5, Column6,
ltrim(left([value],charindex(space(1),[value])-1)) as Column7
from (
select
Column1, Column2, Column3, Column4, Column5,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column6,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2, Column3, Column4,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column5,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2, Column3,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column4,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column3,
ltrim(substring([value],charindex(space(1),[value]),200)) as
[value]
from (
select
Column1,
ltrim(left([value],charindex(space(1),[value])-1))+',' as
Column2,
ltrim(substring([value],charindex(space(1),[value]),200)) as
[value]
from (
select
ltrim(left([value],charindex(space(1),[value])-1))+',' as
Column1,
ltrim(substring([value],charindex(space(1),[value]),200))
as [value]
from T
) T1
) T2
) T3
) T4
) T5
) T6
GO
DROP TABLE T
Steve Kass
Drew University
Wendy Elizabeth wrote:

>I am working with SQL Server 2000 table called dbo.proofofconcept".
>I need to separate values that are all in one column that are space limited.
> For example I have a column called tryit and the
> value = '1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 '.
>I want to write a query so that the results can be split out to 7 output
>columns. In other words, I would like the output to be:
> Column 1 = 1234,
> Column 2 = 456.75,
> Column 3 = 01/01/2001,
> Column 4 = ABBR,
> Column 5 = TRY@.GSU.ORG,
> Column 6 = 75%,
> Column 7 = $12.75
>The output would be separated into different columns for reporting purposes,
>setup to create an output file, and possibly to be split into an XML file.
> How would I go about writing a query like this? If this should be a stored
>procedure, can you show me how I would write the stored procedure and how
>would I execute the stored procedure?
> Thanks!
>
>
|||STEVE KASS:
THIS ANSWER IS EXTREMELY HELPFUL!
THANKS,
DIANE STEIN
"Steve Kass" wrote:

> Here's one way to do it. Depending on where this data originates,
> you might also be able bulk insert or bcp it in, in which case the import
> specifications would take care of splitting it up.
> create table T (
> [value] nvarchar(200)
> )
> go
> insert into T values
> ('1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 ')
> go
> select
> Column1, Column2, Column3, Column4, Column5, Column6,
> ltrim(left([value],charindex(space(1),[value])-1)) as Column7
> from (
> select
> Column1, Column2, Column3, Column4, Column5,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column6,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2, Column3, Column4,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column5,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2, Column3,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column4,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column3,
> ltrim(substring([value],charindex(space(1),[value]),200)) as
> [value]
> from (
> select
> Column1,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as
> Column2,
> ltrim(substring([value],charindex(space(1),[value]),200)) as
> [value]
> from (
> select
> ltrim(left([value],charindex(space(1),[value])-1))+',' as
> Column1,
> ltrim(substring([value],charindex(space(1),[value]),200))
> as [value]
> from T
> ) T1
> ) T2
> ) T3
> ) T4
> ) T5
> ) T6
> GO
> DROP TABLE T
> Steve Kass
> Drew University
> Wendy Elizabeth wrote:
>

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes has multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would return a recordset with each errorCode as its own field. If the newly generated field values could have the same name as the errorCode, that would be good as well.
Thanks,
Stew
I suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew
|||Aaron, I understand your suggestion and gave it some consideration. However, I'm unaware of all the possible error codes which may be encountered. Meaning I'd be entering new errorcodes as they are encountered - which I'm not interested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. However if there is a query that would satisfy my original request, I'd still be interested in that.
Thanks,
Stew
|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes ha
s multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would retur
n a recordset with each errorCode as its own field. If the newly generated f
ield values could have the same name as the errorCode, that would be good as
well.
Thanks,
StewI suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew|||Aaron, I understand your suggestion and gave it some consideration. However,
I'm unaware of all the possible error codes which may be encountered. Meani
ng I'd be entering new errorcodes as they are encountered - which I'm not in
terested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. Ho
wever if there is a query that would satisfy my original request, I'd still
be interested in that.
Thanks,
Stew|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

Comma delimited errors

I need to import comma delimited text files into sql tables. in one of the column, there is a comma in the string itself. e.g.

Cust_ID Name Phone Address1 Address2

Date that I have:

001,juia, anderson,4694568855,,Cedar Spring

The data does not have double quote as text qualifiers. but as you see, on the Name column, there is a comma, which is not a delimiter. can anybody give any suggestions on how i can deal with that? i would appreciate it so much.

thanks

Jul wrote:

I need to import comma delimited text files into sql tables. in one of the column, there is a comma in the string itself. e.g.

Cust_ID Name Phone Address1 Address2

Date that I have:

001,juia, anderson,4694568855,,Cedar Spring

The data does not have double quote as text qualifiers. but as you see, on the Name column, there is a comma, which is not a delimiter. can anybody give any suggestions on how i can deal with that? i would appreciate it so much.

thanks

If the data has embedded commas, but does not have a text qualifier, I fail to see how you can clean this up. Sorry.

Either fix the format of the file by using text qualifiers, or chose a different delimiter. Tabs, semicolons, etc...|||

Phil,

Thanks for the reply. That's what I thought too..Was just wondering if anybody has ever dealt and solved this problem.

Thanks.