Showing posts with label csv. Show all posts
Showing posts with label csv. Show all posts

Sunday, February 19, 2012

CSV, Render method and encoding

Hi there,
I'm creating my report using the Render method and the format is set to
"CSV". I found that when my report is opened using Excel, each row is treated
as one long string and Excel does not seem to recognise the commas as
demiliters.
After googling, it seemed that the default encoding of the report as Unicode
was the cause of this, so I passed in "ASCII" as the encoding type for the
Render method, however, Excel is still not recognising the commas as
delimiters. Am I missing something?
CheersOk, found out why this wasn't working...I was passing "ASCII" as the Encoding
argument (the 8th srgument) to the Render method, which I really should have
realised is actually a ByRef argument.
What I should have been doing is passing
"<DeviceInfo><Encoding>ASCII</Encoding></DeviceInfo>" to the DeviceInfo
argument (the 4th argument) of the Render method.
Once I did that, the report formatted as a CSV opened up fine in Excel.
Doh! Dune
"Dune" wrote:
> Hi there,
> I'm creating my report using the Render method and the format is set to
> "CSV". I found that when my report is opened using Excel, each row is treated
> as one long string and Excel does not seem to recognise the commas as
> demiliters.
> After googling, it seemed that the default encoding of the report as Unicode
> was the cause of this, so I passed in "ASCII" as the encoding type for the
> Render method, however, Excel is still not recognising the commas as
> delimiters. Am I missing something?
> Cheers|||Dune,
I'm new to RS, so any help would be appreciated. Where did you put that
line of code in at? I've got users asking for ASCII format as well.
Thanks,
Chris
"Dune" wrote:
> Ok, found out why this wasn't working...I was passing "ASCII" as the Encoding
> argument (the 8th srgument) to the Render method, which I really should have
> realised is actually a ByRef argument.
> What I should have been doing is passing
> "<DeviceInfo><Encoding>ASCII</Encoding></DeviceInfo>" to the DeviceInfo
> argument (the 4th argument) of the Render method.
> Once I did that, the report formatted as a CSV opened up fine in Excel.
> Doh! Dune
> "Dune" wrote:
> > Hi there,
> >
> > I'm creating my report using the Render method and the format is set to
> > "CSV". I found that when my report is opened using Excel, each row is treated
> > as one long string and Excel does not seem to recognise the commas as
> > demiliters.
> >
> > After googling, it seemed that the default encoding of the report as Unicode
> > was the cause of this, so I passed in "ASCII" as the encoding type for the
> > Render method, however, Excel is still not recognising the commas as
> > delimiters. Am I missing something?
> >
> > Cheers

CSV to SQL Server import

Iam an intermediate stage programmer to the world of .Net

Can anyone show me how to import a CSV file to a table in the SQL Server database with ASP.Net and VB.Net or even C#.Net ??

Please post any code snippets that you have.

Thank You.dn,
Do a search on SQL DTS

KJ

CSV to SQL help

Using Visual Web Developer 2005 and VB, I have created a web form page with a datagrid that shows the data which is stored in an SQL database Table. Everything shows perfectly but it requires the data to already be in the database. Right now, I am exporting the data as a csv file, clearing the data out of the table in the database, and manually importing the csv file. What I would like is to have an Upload procedure that will automatically clear the table and input the new data into that same table without any extra measures by the user.

I created a FileUpload procedure (FileUpload1) which allows the user to browse to the file they want. I have set up a string that holds the location of that file (fi as string). I also have a Import button (btnImport) which is where I need to write the code which will take the csv file in the above location (fi) and save it to the database table I have set up. I basically want to be able to skip having to go to the SQL Server Manager and import the csv into the table whenever they need it. I also want there to be no interaction for this part by the user other than choosing the file to import.

Amy help would be greatly appreciated.

You should be able to connect directly to the csv file with and OleDbConnection and use a OleDbDataAdapter.Fill() to fill a DataSet. Once you have the dataset you can bind directly to the datagrid. Checkwww.connectionstrings.com for the correct connection string. You will need to dynamically insert the path to the file into the connection string.

-David

CSV string as a SQL In Parameter

Hi Guys,

I am having SQL query whith "IN"

SELECT * FROM Table1 Where ID in ( 1, 2,3)

how can i pass '1,2,3' as a SP parameter.

Thanks

There is a simple way to resolve this
exec ('select *from tblwhere id in (' + @.csv + ')' )
 
where @.csv is your parameter with comma sparated value.
might this helps you.
 
Thanks
 
|||
Hi, 
There is a simple way to resolve this
exec ('select * from tbl where id in ( ' + @.csv + ')' )
 
where @.csv is your parameter with comma sparated value.
might this helps you.
 
Thanks

CSV String

Hi,

I have a variable like @.states=NY,VA,AL,CA.

Now i want to use the above string in the IN clause.How to separate those string and use.

Select * from xxx where @.State IN('NY','VA','AL','CA')

Thanks in advance

Here are a couple of pretty good references:

http://www.sommarskog.se/arrays-in-sql.html
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

CSV Scheduled Report

I need to run a report from my DB to run every night and save the results as
a CSV file. This file will need to be saved to a specified location to be
imported to another vendor DB. I created the query inside of a Stored
Procedure and scheduled the SP to execute using the Service Agent (Jobs) and
save the results to a .CSV file. The file is created but on the top of the
file there's always the time date and step of the job and also the CSV format
is not correct the information needs to be in their own column so that the
vendor DB can import the data to their DB. How can I schedule this job so
that the time, date and Job step does not show on the results file and also
how can i make sure that the format of the CSV file is correct. When I tun
the same query in analyzer it creates all my columns with the headings
correclty.
Thanks in advance for any help.
Carlos
Look at jobs in the BOL
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--Run
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Ppass'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> I need to run a report from my DB to run every night and save the results
as
> a CSV file. This file will need to be saved to a specified location to be
> imported to another vendor DB. I created the query inside of a Stored
> Procedure and scheduled the SP to execute using the Service Agent (Jobs)
and
> save the results to a .CSV file. The file is created but on the top of
the
> file there's always the time date and step of the job and also the CSV
format
> is not correct the information needs to be in their own column so that
the
> vendor DB can import the data to their DB. How can I schedule this job so
> that the time, date and Job step does not show on the results file and
also
> how can i make sure that the format of the CSV file is correct. When I
tun
> the same query in analyzer it creates all my columns with the headings
> correclty.
> Thanks in advance for any help.
|||I don't want to create another table. I already have the query that I need
to run against my DB and the results need to be saved to a .csv file. My
problem is that the .CSV data is all over the columns and also the top on the
file always shows the time date and the step that took to run the job.
The vendor appliaction is always monitoring a specif directory for the
formated .csv file to import to their app. So i need to find a way so that
when my stored procedure when scheduked to run needs to output the results to
a .csv file with the data in the right columns and without the time, date and
step number of the job.
Thanks
"Uri Dimant" wrote:

> Carlos
> Look at jobs in the BOL
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --Run
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
>
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Ppass'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> as
> and
> the
> format
> the
> also
> tun
>
>
|||Carlos
You might want to look at xp_execresultset SP. If I remember well OJ has a
great article about such kind of queries.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> I don't want to create another table. I already have the query that I
need
> to run against my DB and the results need to be saved to a .csv file. My
> problem is that the .CSV data is all over the columns and also the top on
the
> file always shows the time date and the step that took to run the job.
> The vendor appliaction is always monitoring a specif directory for the
> formated .csv file to import to their app. So i need to find a way so
that
> when my stored procedure when scheduked to run needs to output the results
to
> a .csv file with the data in the right columns and without the time, date
and[vbcol=seagreen]
> step number of the job.
> Thanks
> "Uri Dimant" wrote:
message[vbcol=seagreen]
results[vbcol=seagreen]
to be[vbcol=seagreen]
(Jobs)[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]
job so[vbcol=seagreen]
I[vbcol=seagreen]
|||Who's OJ? and where can i look for such article on these kind of queries. I
could not get your sample to work.
Thanks again for your help.
"Uri Dimant" wrote:

> Carlos
> You might want to look at xp_execresultset SP. If I remember well OJ has a
> great article about such kind of queries.
>
> EXEC master..xp_execresultset N'SELECT
> ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
> Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> "C:\Temp\''+CAST(Employeeid AS
> VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> ',N'Northwind'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> need
> the
> that
> to
> and
> message
> results
> to be
> (Jobs)
> of
> that
> job so
> I
>
>
|||Carlos
Sorry , try search on internet OJ+sql Server' ,i don't have a time right
now.
I posted yesterday one example about how it could be used.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:5C6A6F57-DE0F-4213-92FF-88C0DC3A61CB@.microsoft.com...
> Who's OJ? and where can i look for such article on these kind of queries.
I[vbcol=seagreen]
> could not get your sample to work.
> Thanks again for your help.
> "Uri Dimant" wrote:
has a[vbcol=seagreen]
WHERE[vbcol=seagreen]
message[vbcol=seagreen]
My[vbcol=seagreen]
on[vbcol=seagreen]
results[vbcol=seagreen]
date[vbcol=seagreen]
location[vbcol=seagreen]
Stored[vbcol=seagreen]
top[vbcol=seagreen]
CSV[vbcol=seagreen]
this[vbcol=seagreen]
and[vbcol=seagreen]
When[vbcol=seagreen]
headings[vbcol=seagreen]

Friday, February 17, 2012

CSV Scheduled Report

I need to run a report from my DB to run every night and save the results as
a CSV file. This file will need to be saved to a specified location to be
imported to another vendor DB. I created the query inside of a Stored
Procedure and scheduled the SP to execute using the Service Agent (Jobs) and
save the results to a .CSV file. The file is created but on the top of the
file there's always the time date and step of the job and also the CSV format
is not correct the information needs to be in their own column so that the
vendor DB can import the data to their DB. How can I schedule this job so
that the time, date and Job step does not show on the results file and also
how can i make sure that the format of the CSV file is correct. When I tun
the same query in analyzer it creates all my columns with the headings
correclty.
Thanks in advance for any help.Carlos
Look at jobs in the BOL
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--Run
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Ppass'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> I need to run a report from my DB to run every night and save the results
as
> a CSV file. This file will need to be saved to a specified location to be
> imported to another vendor DB. I created the query inside of a Stored
> Procedure and scheduled the SP to execute using the Service Agent (Jobs)
and
> save the results to a .CSV file. The file is created but on the top of
the
> file there's always the time date and step of the job and also the CSV
format
> is not correct the information needs to be in their own column so that
the
> vendor DB can import the data to their DB. How can I schedule this job so
> that the time, date and Job step does not show on the results file and
also
> how can i make sure that the format of the CSV file is correct. When I
tun
> the same query in analyzer it creates all my columns with the headings
> correclty.
> Thanks in advance for any help.|||I don't want to create another table. I already have the query that I need
to run against my DB and the results need to be saved to a .csv file. My
problem is that the .CSV data is all over the columns and also the top on the
file always shows the time date and the step that took to run the job.
The vendor appliaction is always monitoring a specif directory for the
formated .csv file to import to their app. So i need to find a way so that
when my stored procedure when scheduked to run needs to output the results to
a .csv file with the data in the right columns and without the time, date and
step number of the job.
Thanks
"Uri Dimant" wrote:
> Carlos
> Look at jobs in the BOL
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --Run
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
>
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Ppass'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > I need to run a report from my DB to run every night and save the results
> as
> > a CSV file. This file will need to be saved to a specified location to be
> > imported to another vendor DB. I created the query inside of a Stored
> > Procedure and scheduled the SP to execute using the Service Agent (Jobs)
> and
> > save the results to a .CSV file. The file is created but on the top of
> the
> > file there's always the time date and step of the job and also the CSV
> format
> > is not correct the information needs to be in their own column so that
> the
> > vendor DB can import the data to their DB. How can I schedule this job so
> > that the time, date and Job step does not show on the results file and
> also
> > how can i make sure that the format of the CSV file is correct. When I
> tun
> > the same query in analyzer it creates all my columns with the headings
> > correclty.
> >
> > Thanks in advance for any help.
>
>|||Carlos
You might want to look at xp_execresultset SP. If I remember well OJ has a
great article about such kind of queries.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> I don't want to create another table. I already have the query that I
need
> to run against my DB and the results need to be saved to a .csv file. My
> problem is that the .CSV data is all over the columns and also the top on
the
> file always shows the time date and the step that took to run the job.
> The vendor appliaction is always monitoring a specif directory for the
> formated .csv file to import to their app. So i need to find a way so
that
> when my stored procedure when scheduked to run needs to output the results
to
> a .csv file with the data in the right columns and without the time, date
and
> step number of the job.
> Thanks
> "Uri Dimant" wrote:
> > Carlos
> > Look at jobs in the BOL
> > create table ww
> > (
> > col1 int,
> > col2 varchar(50),
> > col3 varchar (50)
> > )
> >
> > insert into ww values (47,'ReadyShip','(503)888-999')
> > insert into ww values (48,'MyShipper','(503)1212-454')
> > insert into ww values (49,'ReadyShip','(45)888-999')
> > insert into ww values (50,'MyShipper','(545)1212-454')
> > --Run
> > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> >
> >
> > exec master..xp_cmdshell 'BCP northwind..ww IN
> > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> >
> >
> >
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
message
> > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > I need to run a report from my DB to run every night and save the
results
> > as
> > > a CSV file. This file will need to be saved to a specified location
to be
> > > imported to another vendor DB. I created the query inside of a Stored
> > > Procedure and scheduled the SP to execute using the Service Agent
(Jobs)
> > and
> > > save the results to a .CSV file. The file is created but on the top
of
> > the
> > > file there's always the time date and step of the job and also the CSV
> > format
> > > is not correct the information needs to be in their own column so
that
> > the
> > > vendor DB can import the data to their DB. How can I schedule this
job so
> > > that the time, date and Job step does not show on the results file and
> > also
> > > how can i make sure that the format of the CSV file is correct. When
I
> > tun
> > > the same query in analyzer it creates all my columns with the headings
> > > correclty.
> > >
> > > Thanks in advance for any help.
> >
> >
> >|||Who's OJ? and where can i look for such article on these kind of queries. I
could not get your sample to work.
Thanks again for your help.
"Uri Dimant" wrote:
> Carlos
> You might want to look at xp_execresultset SP. If I remember well OJ has a
> great article about such kind of queries.
>
> EXEC master..xp_execresultset N'SELECT
> ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
> Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> "C:\Temp\''+CAST(Employeeid AS
> VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> ',N'Northwind'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> > I don't want to create another table. I already have the query that I
> need
> > to run against my DB and the results need to be saved to a .csv file. My
> > problem is that the .CSV data is all over the columns and also the top on
> the
> > file always shows the time date and the step that took to run the job.
> >
> > The vendor appliaction is always monitoring a specif directory for the
> > formated .csv file to import to their app. So i need to find a way so
> that
> > when my stored procedure when scheduked to run needs to output the results
> to
> > a .csv file with the data in the right columns and without the time, date
> and
> > step number of the job.
> >
> > Thanks
> >
> > "Uri Dimant" wrote:
> >
> > > Carlos
> > > Look at jobs in the BOL
> > > create table ww
> > > (
> > > col1 int,
> > > col2 varchar(50),
> > > col3 varchar (50)
> > > )
> > >
> > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > --Run
> > > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> > >
> > >
> > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> > >
> > >
> > >
> > >
> > > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
> message
> > > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > > I need to run a report from my DB to run every night and save the
> results
> > > as
> > > > a CSV file. This file will need to be saved to a specified location
> to be
> > > > imported to another vendor DB. I created the query inside of a Stored
> > > > Procedure and scheduled the SP to execute using the Service Agent
> (Jobs)
> > > and
> > > > save the results to a .CSV file. The file is created but on the top
> of
> > > the
> > > > file there's always the time date and step of the job and also the CSV
> > > format
> > > > is not correct the information needs to be in their own column so
> that
> > > the
> > > > vendor DB can import the data to their DB. How can I schedule this
> job so
> > > > that the time, date and Job step does not show on the results file and
> > > also
> > > > how can i make sure that the format of the CSV file is correct. When
> I
> > > tun
> > > > the same query in analyzer it creates all my columns with the headings
> > > > correclty.
> > > >
> > > > Thanks in advance for any help.
> > >
> > >
> > >
>
>|||Carlos
Sorry , try search on internet OJ+sql Server' ,i don't have a time right
now.
I posted yesterday one example about how it could be used.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:5C6A6F57-DE0F-4213-92FF-88C0DC3A61CB@.microsoft.com...
> Who's OJ? and where can i look for such article on these kind of queries.
I
> could not get your sample to work.
> Thanks again for your help.
> "Uri Dimant" wrote:
> > Carlos
> > You might want to look at xp_execresultset SP. If I remember well OJ
has a
> > great article about such kind of queries.
> >
> >
> >
> > EXEC master..xp_execresultset N'SELECT
> > ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders
WHERE
> > Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> > "C:\Temp\''+CAST(Employeeid AS
> > VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> > FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> > ',N'Northwind'
> >
> >
> >
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
message
> > news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> > > I don't want to create another table. I already have the query that I
> > need
> > > to run against my DB and the results need to be saved to a .csv file.
My
> > > problem is that the .CSV data is all over the columns and also the top
on
> > the
> > > file always shows the time date and the step that took to run the job.
> > >
> > > The vendor appliaction is always monitoring a specif directory for the
> > > formated .csv file to import to their app. So i need to find a way so
> > that
> > > when my stored procedure when scheduked to run needs to output the
results
> > to
> > > a .csv file with the data in the right columns and without the time,
date
> > and
> > > step number of the job.
> > >
> > > Thanks
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Carlos
> > > > Look at jobs in the BOL
> > > > create table ww
> > > > (
> > > > col1 int,
> > > > col2 varchar(50),
> > > > col3 varchar (50)
> > > > )
> > > >
> > > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > > --Run
> > > > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> > > >
> > > >
> > > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> > > >
> > > >
> > > >
> > > >
> > > > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
> > message
> > > > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > > > I need to run a report from my DB to run every night and save the
> > results
> > > > as
> > > > > a CSV file. This file will need to be saved to a specified
location
> > to be
> > > > > imported to another vendor DB. I created the query inside of a
Stored
> > > > > Procedure and scheduled the SP to execute using the Service Agent
> > (Jobs)
> > > > and
> > > > > save the results to a .CSV file. The file is created but on the
top
> > of
> > > > the
> > > > > file there's always the time date and step of the job and also the
CSV
> > > > format
> > > > > is not correct the information needs to be in their own column so
> > that
> > > > the
> > > > > vendor DB can import the data to their DB. How can I schedule
this
> > job so
> > > > > that the time, date and Job step does not show on the results file
and
> > > > also
> > > > > how can i make sure that the format of the CSV file is correct.
When
> > I
> > > > tun
> > > > > the same query in analyzer it creates all my columns with the
headings
> > > > > correclty.
> > > > >
> > > > > Thanks in advance for any help.
> > > >
> > > >
> > > >
> >
> >
> >

CSV rendering is missing first detail row

For one report we've designed, an HTML rendering will produce 3 detail
records. When this same report with the same parameters is rendered in CSV,
the first detail row is missing from the output. All other aspects of the
export seem to work as expected.
This occurs with several reports we have but not all of them. This happens
when previewing in VS.NET, RS Manager, and on our live Web site. Other
formats do not drop the first detail record.
Is there any resolution to this problem?FYI: I experienced the same problem you described. Through trial and error I
determined that what was causing the problem was that I was grouping the
Details section of the Table. To work around this I added grouping to the SQL
code and removed grouping from the report detail.
Brad
"John Demar" wrote:
> For one report we've designed, an HTML rendering will produce 3 detail
> records. When this same report with the same parameters is rendered in CSV,
> the first detail row is missing from the output. All other aspects of the
> export seem to work as expected.
> This occurs with several reports we have but not all of them. This happens
> when previewing in VS.NET, RS Manager, and on our live Web site. Other
> formats do not drop the first detail record.
> Is there any resolution to this problem?

CSV rendering is missing first detail row

For one report we've designed, an HTML rendering will produce 3 detail
records. When this same report with the same parameters is rendered in CSV,
the first detail row is missing from the output. All other aspects of the
export seem to work as expected.
This occurs with several reports we have but not all of them. This happens
when previewing in VS.NET, RS Manager, and on our live Web site. Other
formats do not drop the first detail record.
Any ideas? I can't seem to find anyone else that has run across this
problem in the groups.I continue to have this problem and cannot find any answers. Is there a
problem with the CSV rendering extension?
"John Demar" wrote:
> For one report we've designed, an HTML rendering will produce 3 detail
> records. When this same report with the same parameters is rendered in CSV,
> the first detail row is missing from the output. All other aspects of the
> export seem to work as expected.
> This occurs with several reports we have but not all of them. This happens
> when previewing in VS.NET, RS Manager, and on our live Web site. Other
> formats do not drop the first detail record.
> Any ideas? I can't seem to find anyone else that has run across this
> problem in the groups.
>|||John,
I am also seeing the same results with the missing first line. Does anyone
know if this is a known issue or just a setting that can be set?
"John Demar" wrote:
> I continue to have this problem and cannot find any answers. Is there a
> problem with the CSV rendering extension?
> "John Demar" wrote:
> > For one report we've designed, an HTML rendering will produce 3 detail
> > records. When this same report with the same parameters is rendered in CSV,
> > the first detail row is missing from the output. All other aspects of the
> > export seem to work as expected.
> >
> > This occurs with several reports we have but not all of them. This happens
> > when previewing in VS.NET, RS Manager, and on our live Web site. Other
> > formats do not drop the first detail record.
> >
> > Any ideas? I can't seem to find anyone else that has run across this
> > problem in the groups.
> >

CSV Render Engine

Hi

I have Reporting Services hosted on a server which has Workgroup edition of SQL Server which does not support csv export. I would like to write a csv Renderer.

I am not sure how complex this task is likely to be.
Are there any White Papers or samples that would assist ?
Any advice to point me in the right direction would be most appreciated.

Regards

JohnJames

Hi Chaps

I have just found an article on MSDN stating that
writing a Render is a very complex operation.
Will now seek another alternative.


Regards

JohnJames

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as
a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:

> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.

> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan|||"Daniel Crichton" wrote:

> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
>
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.

> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.
Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:

> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.

> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan
|||"Daniel Crichton" wrote:

> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
>
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.

> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>

csv questions

I have two questions.
If I upload a csv with a new value for an existing record will it update the
record, will it error out, or will it create havoc?
I have seen how manually entering a duplicate record can cause issues.
Related to this, i am using Excel as a tool to drop data in to then generate
the CSV.
I did a sample create and upload to a new table, and found that the CSV file
had truncated the text in the individual fields. I am using the database as a
holder for a multilanguage service, so it really is just text being stored.
But since the csv is cutting the text, the translation fields are all
incomplete.
Is this a CSV limit or an Excel 2003 limit.Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
> I have two questions.
> If I upload a csv with a new value for an existing record will it update
> the record, will it error out, or will it create havoc?
> I have seen how manually entering a duplicate record can cause issues.
It depends on how you do the update. You could upload to an empty table,
then run an UPDATE query to modify existing rows by joining on the key(s),
and then an APPEND to add new rows to the table.
> Related to this, i am using Excel as a tool to drop data in to then
> generate the CSV.
> I did a sample create and upload to a new table, and found that the CSV
> file had truncated the text in the individual fields. I am using the
> database as a holder for a multilanguage service, so it really is just
> text being stored. But since the csv is cutting the text, the translation
> fields are all incomplete.
> Is this a CSV limit or an Excel 2003 limit.
IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
limit other than the number of characters that can be on a single line,
which is dependent upon on how the data is read.
Dan|||"Daniel Crichton" wrote:
> Manny wrote on Thu, 28 Jul 2005 06:08:02 -0700:
> > I have two questions.
> >
> > If I upload a csv with a new value for an existing record will it update
> > the record, will it error out, or will it create havoc?
> > I have seen how manually entering a duplicate record can cause issues.
> It depends on how you do the update. You could upload to an empty table,
> then run an UPDATE query to modify existing rows by joining on the key(s),
> and then an APPEND to add new rows to the table.
>
I am using the potatohead wizard.
I see that it can take an excel file as an import so I may try that.
I have the database on a virtual server now so I can play with it and see
what happens to the data when I move things in and out.
> > Related to this, i am using Excel as a tool to drop data in to then
> > generate the CSV.
> >
> > I did a sample create and upload to a new table, and found that the CSV
> > file had truncated the text in the individual fields. I am using the
> > database as a holder for a multilanguage service, so it really is just
> > text being stored. But since the csv is cutting the text, the translation
> > fields are all incomplete.
> >
> > Is this a CSV limit or an Excel 2003 limit.
> IIRC Excel truncates data in cells when saving as CSV. CSV itself has no
> limit other than the number of characters that can be on a single line,
> which is dependent upon on how the data is read.
> Dan
>
>

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
:)You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> :)|||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?
:)
Adi wrote:
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > I have a bunch of stored procedures (some using temp tables) which give
> > out tables which are then used in crystal to give out reports. These
> > stored procs are run dynamically depending on values users select on
> > forms
> >
> > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > trying to find a good way of outputting to csv with using either sql
> > server or ASP. I tried two ways but none of them were ideal
> >
> > 1st method
> > first way i tried was creating a record set in asp and then using the
> > following to output the data in a comma delimited row and writing to
> > file.
> > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > The problem with the above was with data sets greater than 10,000
> > records the processing time increases exponentially because of memory
> > usage ( i found other people with the same problem)
> >
> > So i tried the following
> > 2nd method
> > I used bcp using the master.dbo.xp_cmdshell command to output the
> > csv. The problem with that is that the bcp executes the stored proc
> > three times and also doesnot give out column headers. So i had to run
> > the stored proc once in ASP and get the headers from the record set and
> > store it to a text file and then run the stored proc in bcp and send
> > the output to a csv file. Then merge the two files into a third file.
> > The problem with this method is that it takes around 4 times as long to
> > run as just the stored proceedure and also i have to create 3 files
> > instead of one.
> >
> > I would appreciate any suggestions you have to output the csv from a
> > stored proc in sql server. Note users have to do this dynamically when
> > they submit a form so cant use the query analyzer or enterprise manager
> > tools.
> >
> > Thanks for your time and help
> > :)|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
> :)
> Adi wrote:
> > You can use the OSQL utility in order to create a file with or without
> > headers (you can control the behavior of the headers with the -h
> > switch). Another tool that you can use is DTS package and run it with
> > DTSRun utility. Also I would not run the tools in a stored procedure
> > with xp_cmdshell, because this is a huge security problem. By working
> > this way you give the user a way to run there own scripts and command
> > on the operating system level with the privileges of the account that
> > runs SQL Server's service. Since you are working with ASP anyway, it
> > is better to run the OSQL (or if you choose a different utility, then
> > the other utility) from the ASP code and not from a stored procedure.
> >
> > Adi
> >
> >
> > Katie wrote:
> > > Hi,
> > >
> > > I have a bunch of stored procedures (some using temp tables) which give
> > > out tables which are then used in crystal to give out reports. These
> > > stored procs are run dynamically depending on values users select on
> > > forms
> > >
> > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > trying to find a good way of outputting to csv with using either sql
> > > server or ASP. I tried two ways but none of them were ideal
> > >
> > > 1st method
> > > first way i tried was creating a record set in asp and then using the
> > > following to output the data in a comma delimited row and writing to
> > > file.
> > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > The problem with the above was with data sets greater than 10,000
> > > records the processing time increases exponentially because of memory
> > > usage ( i found other people with the same problem)
> > >
> > > So i tried the following
> > > 2nd method
> > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > csv. The problem with that is that the bcp executes the stored proc
> > > three times and also doesnot give out column headers. So i had to run
> > > the stored proc once in ASP and get the headers from the record set and
> > > store it to a text file and then run the stored proc in bcp and send
> > > the output to a csv file. Then merge the two files into a third file.
> > > The problem with this method is that it takes around 4 times as long to
> > > run as just the stored proceedure and also i have to create 3 files
> > > instead of one.
> > >
> > > I would appreciate any suggestions you have to output the csv from a
> > > stored proc in sql server. Note users have to do this dynamically when
> > > they submit a form so cant use the query analyzer or enterprise manager
> > > tools.
> > >
> > > Thanks for your time and help
> > > :)|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly
:(
Adi wrote:
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > Thanks for your response.
> > But isnt OSQL also a command line utitlity? how would i run that from
> > inside asp. Would i use some shell command utility in vbscript?
> >
> > :)
> >
> > Adi wrote:
> > > You can use the OSQL utility in order to create a file with or without
> > > headers (you can control the behavior of the headers with the -h
> > > switch). Another tool that you can use is DTS package and run it with
> > > DTSRun utility. Also I would not run the tools in a stored procedure
> > > with xp_cmdshell, because this is a huge security problem. By working
> > > this way you give the user a way to run there own scripts and command
> > > on the operating system level with the privileges of the account that
> > > runs SQL Server's service. Since you are working with ASP anyway, it
> > > is better to run the OSQL (or if you choose a different utility, then
> > > the other utility) from the ASP code and not from a stored procedure.
> > >
> > > Adi
> > >
> > >
> > > Katie wrote:
> > > > Hi,
> > > >
> > > > I have a bunch of stored procedures (some using temp tables) which give
> > > > out tables which are then used in crystal to give out reports. These
> > > > stored procs are run dynamically depending on values users select on
> > > > forms
> > > >
> > > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > > trying to find a good way of outputting to csv with using either sql
> > > > server or ASP. I tried two ways but none of them were ideal
> > > >
> > > > 1st method
> > > > first way i tried was creating a record set in asp and then using the
> > > > following to output the data in a comma delimited row and writing to
> > > > file.
> > > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > > The problem with the above was with data sets greater than 10,000
> > > > records the processing time increases exponentially because of memory
> > > > usage ( i found other people with the same problem)
> > > >
> > > > So i tried the following
> > > > 2nd method
> > > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > > csv. The problem with that is that the bcp executes the stored proc
> > > > three times and also doesnot give out column headers. So i had to run
> > > > the stored proc once in ASP and get the headers from the record set and
> > > > store it to a text file and then run the stored proc in bcp and send
> > > > the output to a csv file. Then merge the two files into a third file.
> > > > The problem with this method is that it takes around 4 times as long to
> > > > run as just the stored proceedure and also i have to create 3 files
> > > > instead of one.
> > > >
> > > > I would appreciate any suggestions you have to output the csv from a
> > > > stored proc in sql server. Note users have to do this dynamically when
> > > > they submit a form so cant use the query analyzer or enterprise manager
> > > > tools.
> > > >
> > > > Thanks for your time and help
> > > > :)

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> |||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?

Adi wrote:[vbcol=seagreen]
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
>
> Adi wrote:|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly

Adi wrote:[vbcol=seagreen]
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:

csv matching problem

Dear all,

I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc

I would like to select the item where "any one item" in first table is same as "any one item" on second table.

I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this
select .... where dbo.CSVfunction(tableA.colA)

Thx
//CSV function
declare @.separator char(1)
set @.separator = ','

declare @.separator_position int
declare @.array_value varchar(1000)

set @.array = @.array + ','

while patindex('%,%' , @.array) <> 0
begin

select @.separator_position = patindex('%,%' , @.array)
select @.array_value = left(@.array, @.separator_position - 1)
select @.array_value = RTrim(@.array_value)
select @.array_value = LTrim(@.array_value)

Insert @.StringTable
Values (Cast(@.array_value as varchar(50)))

select @.array = stuff(@.array, 1, @.separator_position, '')
end
re @.separator char(1)
set @.separator = ','

declare @.separator_position int
declare @.array_value varchar(1000)

set @.array = @.array + ','

while patindex('%,%' , @.array) <> 0
begin

select @.separator_position = patindex('%,%' , @.array)
select @.array_value = left(@.array, @.separator_position - 1)
select @.array_value = RTrim(@.array_value)
select @.array_value = LTrim(@.array_value)

Insert @.StringTable
Values (Cast(@.array_value as varchar(50)))

select @.array = stuff(@.array, 1, @.separator_position, '')
end

The question is why are you storing data in CVS format in SQL Server when you can store the data as Char, Varchar, Nchar, Nvarchar, Text and Ntext or as Word file using Image. The reason CVS file have to be converted or tranformed with DTS before SQL server can use the file. Hope this helps.

CSV in EXCEL = Users not happy

I am rolling out reporting services at my company and so far, everything has
gone smooth. The main problem for users testing reports has been that when
they open a CSV file in EXCEL, it opens up with everything in the "A" column.
Our current reporting platform does not have this problem and the users want
to keep using the data in this format (CSV files in Excel).
What I would like to do is to modify the CSV rendering format in order for
it to put the CSV files straight out in ASCII format. Does anyone know if
this is possible or if there is a better workaround?First, to explain what is happening. The default rendering of CSV is Unicode
which excel doesn't know what to do with and so it puts it all in the same
column. Here is a workaround. Add a textbox at the top of the report (I call
it Export Data), change to blue and underlined. Then you want to do a jump
to URL. (right mouse click, properties, advanced properties, navigation tab)
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
RS 2005 will allow you to modify the config file so it defaults to rendering
in ASCII so this problem will go away. RS 2005 will be released in November.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:3AF6B09C-1FAC-48B5-A6A0-9024ABC586D5@.microsoft.com...
>I am rolling out reporting services at my company and so far, everything
>has
> gone smooth. The main problem for users testing reports has been that when
> they open a CSV file in EXCEL, it opens up with everything in the "A"
> column.
> Our current reporting platform does not have this problem and the users
> want
> to keep using the data in this format (CSV files in Excel).
> What I would like to do is to modify the CSV rendering format in order for
> it to put the CSV files straight out in ASCII format. Does anyone know if
> this is possible or if there is a better workaround?

csv import which runs stored procedure

Are there any tools which allow you to take a comma delimted file and run a stored procedure for each line in the file, passing the values in the file as parameters? i.e.

CREATE PROCEDURE add_user
@.name varchar(20),
@.first_name varchar(20),
@.last_name varchar(20)
AS
/* do some lookups */
/* validate input */
insert into users (name, first_name, last_name) values (@.name, @.first_name, @.last_name)

Then a comma delimited file such as
bpeikes,Benjamin,Peikes
rbobly,Robert,Bobly
trichard,Tom,Richards

I need a tool which can execute the stored procedure once for each line in the file. I could write one myself, but I'd like to know if any of the tools which come with SQL Server will handle it.

My preferred method would be to first load the csv file data into a 'staging' table, and then have a procedure that validates, cleanses, massages, and transfers the data to the working table(s).

And that can easily be accomplished using a SQL Agent Job, scheduled to run 'on schedule', or available to run 'as needed'.

|||

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

|||

You could add a trigger to your target table that calls your stored procedure, bcp the data into the target table and it should trigger storedproc calls for each insert.

Hope that helps,

John

|||

bpeikes wrote:

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

Maybe you should use the sql server integration services SSIS

instead

CSV Import Problem

I originally posted this on microsoft.public.sqlserver.integrationsvcs but have received no response and now after seeing only 133 posts the entire year.... I think nobody actually uses that forum....

I created several very simple flat file (csv) import packages. For the most
part they seem to work fine. Except for one of them (actually two since a
second one is now broken).

The daily files I am trying to import are comma delimited with the first row
containing the headers. The header delimiter is set to {CR}{LF} with the
text delimiter set to double quotes.

For two days (I just wrote the package's yesterday) on one package and for
today's run on the second package, I received the same error of "Error:
0xC0202055 at Import Data, File Source[16]: The column delimiter for column
"xxxxxxx" was not found". The column specified in both errors references
the LAST column which means that it cannot find the header delimiter.

Since the second package worked yesterday but not today, and the only
changed factor is the new source file from today, it makes me think that the
problem is with the source file. Problem is that the file looks fine. I even
loaded both files (yesterday and today) into a hex editor and confirmed
that the header is EXACTLY the same... all the way to the "0D 0A" indicating
{CR} {LF}. (On a side note, the source csv files for the package that failed
both days match and also contain the "0D 0A" values.)

Any ideas?

Thanks,
Jason

One idea is to use the Error Output on the Flat File source and set it to redirect rows. Hopefully, redirected rows will give you a clue of where to look for the missing delimiter.

HTH.

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.
You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>
|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>