Cursors vs. Queries: file

Showing posts with label file. Show all posts

Wednesday, March 21, 2012

Current SQL Server Logs

I am not able to open the current sql server log file from
enterprise manager in one of my servers. I can open the
archive logs below the current one in enterprise server.
When I double click the current log to open it hangs
everything. What is going on on this server, any help
please.Aboki,
Try recycling the error log using sp_cycle_errorlog
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Aboki" <waco361@.hotmail.com> wrote in message
news:0bda01c36b22$517e07e0$a501280a@.phx.gbl...
> I am not able to open the current sql server log file from
> enterprise manager in one of my servers. I can open the
> archive logs below the current one in enterprise server.
> When I double click the current log to open it hangs
> everything. What is going on on this server, any help
> please.

Sunday, February 19, 2012

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

CSV Scheduled Report

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 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 into ms sql 2000

I've got a csv file (75000 records) with 5 columns, one is a date of birth column which shows as an 8 digit long integer (yyyymmdd). I've tried sql dts but I just don't know how to show this column in sql as a date field in dd/mm/yyyy format. Can anyone please help?Using Excel you can change the cell format as you required & upload data in the SQL database but in Excel maximum row limit is 65536.

You can divide this data in two excel sheet & can proceed.

If you are not going to do this task frequently than it is fine, else find some better solution.|||Thanks Rajesh - i've tried that, the problem is that the date of birth field has no date separators, and being in number format, a excel just fills the cell with "###############". Today (2nd sept 2006) shows like an integer 20060902.
Thanks again for your quick reply.
Arthur|||Still Excel helps you lot. Check the enclosed file, it is self explanatory.

Add four columns next to your date column & add formulas same as enclosed file.

At the last check data then Copy -> Paste Special -> Value. Remove unwanted columns & upload data.

Little hard work but fruitful.|||Brilliant - all works well. Thanks a milliuon for all your help.

csv generation

hi
I want to generate excel file which contain table name , column name,datatype ,size
how we can do in sql server
is there any way
pleases tell the stepsI'm pretty sure DTS or SSMS can export in Excel format.

Or you can create a link directly to the database schema objects within Excel.|||SEe this http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx blog.

CSV from SQL

Hi guys,
I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.
Can anyone help me! How should I solve this issue!
Thanks a lot in advance
AsthaIn sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Originally posted by astha_raj
Hi guys,

I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.

Can anyone help me! How should I solve this issue!

Thanks a lot in advance
Astha|||Originally posted by abidulla
In sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Thanks for your help - But what I need to actually do is to generate the CSV file for the query result dynamically - Something by which I can create a file to run as a job to do the process automatically at regular intervals - as the table values changes frequently

Can it be done programitacally some how - may be in Cold Fusion or something?

Please guide me if you have any idea.

Thanks anyways
Astha|||I know that you can use the BULK INSERT command to get CSV into a SQL Server table.

I believe that it is just an argument in order to change from an import to an export.

This BULK INSERT command is basically the same thing as BCP, if i understand it correctly..

if this doesnt work, then you can just use BCP throgh the CLI and xp_cmdshell

cheers and good luck|||Use OSQL utility with -s option, refer to BOL for more information.|||Originally posted by Satya
Use OSQL utility with -s option, refer to BOL for more information.

Thank you very much - I was able to do my stuff with bcp utility.

Thanks again

astha|||I have used OSQL many times before to do so. Ofcourse BCP also does the thing.

CSV files

How would I turn a CSV file into a working SQL Database?Do you mean to import data from CSV file to a table?|||I think he means that.
Use DTS. Just import your CSV file into SQL Database.

CSV file..

I do have a csv file and I want to save the file into log table but I
wondernig that datatype would be text or image ? and after that I want to
fetch the data from that file and save it into different table.. can any let
me know what would be the best way to do this ?
Thanks in advance
Hi,
Do this activity using some programing language.. Visual studio . Net will
be a best option.
Thanks
hari
SQL Server MVP
"Noor" <noor@.istcl.com> wrote in message
news:ew0hil6tFHA.256@.tk2msftngp13.phx.gbl...
>I do have a csv file and I want to save the file into log table but I
>wondernig that datatype would be text or image ? and after that I want to
>fetch the data from that file and save it into different table.. can any
>let me know what would be the best way to do this ?
> Thanks in advance
>
|||Thanks HARI...
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u0I$TGAuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do this activity using some programing language.. Visual studio . Net will
> be a best option.
> Thanks
> hari
> SQL Server MVP
> "Noor" <noor@.istcl.com> wrote in message
> news:ew0hil6tFHA.256@.tk2msftngp13.phx.gbl...
>
|||Thanks HARI......
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u0I$TGAuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do this activity using some programing language.. Visual studio . Net will
> be a best option.
> Thanks
> hari
> SQL Server MVP
> "Noor" <noor@.istcl.com> wrote in message
> news:ew0hil6tFHA.256@.tk2msftngp13.phx.gbl...
>

csv file to sql table

Hi...

I have .csv file(with headers) that i want to send its data to temporary table in sql.

Do i need to get the data as dataTable and than send it to sql table?

any way, i need help to do so...

thank you...

If you are using SQL 2005, you can use SSIS to load the data. If not see the example in http://www.codeproject.com/aspnet/ImportExportCSV.asp

|||

Hi may215,

may215:
I have .csv file(with headers) that i want to send its data to temporary table in sql.
Do i need to get the data as dataTable and than send it to sql table?

in the past i had a similar problem and solved it at this way you asked. Anyway. If you interested on it, here is a method in C# to read a structured textfile in a DataTable.

1DataTable table =null;2// set the delimeter3char _delim =';';45private void createTable()6{7// check if file exits8if (!File.Exists(_fileName))9{10// you have to change this to youre requirement11this._errorText =string.Format(CultureInfo.InvariantCulture,"Datei {0} ist nicht vorhanden, oder der Zugriff wurde Verweigert.",12this._fileName);13this._hasError =true;14throw new FileNotFoundException(_errorText);15}1617// create new DataTable18table =new DataTable();19table.Locale = CultureInfo.InvariantCulture;2021// String and StringArray initialize22string kontent =null;23string[] kontentArray =null;2425// create a FileStream26FileStream fs = File.OpenRead(_fileName);27// create a StreamReader and read the FileStream28using (StreamReader sr =new StreamReader(fs))29{30// read one line from the textfile , to create the columns in the DataTable31kontent = sr.ReadLine();32kontentArray = kontent.Split(_delim);33int i = 0;34// create the columns in the DataTable,35// the same number like the columns in the file36// beginn with 037foreach (string sin kontentArray)38{39table.Columns.Add(i.ToString(CultureInfo.InvariantCulture),typeof(string));40i++;41}4243// delete the old data from the buffer of the StreamReader44sr.DiscardBufferedData();45// set FileStream position at the line 2 from beginning46sr.BaseStream.Seek(1, SeekOrigin.Begin);4748// read the whole file in the DataTable.49while ((kontent = sr.ReadLine()) !=null)50{51kontentArray = kontent.Split(_delim);52table.Rows.Add(kontentArray);53}54}55// if FileSream not closed, close now56if (fs !=null)57{58fs.Close();59}60}

Hope could help a little.

|||

Hi...

thank you for your answer...

I have an odd problem, my csv file has headers, 10 in Hebrew, and 10 in English , the headers in hebrew are translate to "" sign...the headers in English are ok.

Why is that?

|||

may215:
Why is that?

Different cultures need different codepages and character sets.

may215:
I have an odd problem, my csv file has headers, 10 in Hebrew, and 10 in English , the headers in hebrew are translate to "" sign...the headers in English are ok.

I did not really understand the problem. As far as i understand your first post, you simple want to insert the content of the csv-file in a temporary table. So whats the matter with the headers? You can name the columns in your table however you want. So the header culture from the csv file is irrelevant. In the method i've postet prior, i simple read the header line into an array and count the contents from the array to generate the same count of columns. So when the hebrew content of a column is translated to " ", its also a content of an array. An empty one but a content, and so it is counted and a table column is generated.

If i understand your problem wrong, so please post back.

CSV file saved as an Excel file - error message

I hope someone can help me with this - I started receiving this error message in the past month or so when I open a csv report and save it as an Excel file in a folder I use on my VPN and in My Documents. It does not show up when I save it to my Desk Top.

I have Microsoft Office Student and Teacher and Office XP Professional installed on my notebook. I tried to uninstall Office XP and it would not let me. Something about a "patch could not be opened......"

The error message is as follows:

Header: .NET-BroadcastEventWindow.2.0.0.0.33c0d.0.EXCEL.EXE-Application Error

Excel error message The instruction at 0x0beab865 referenced memory at "0x00000008"

The memory could not be "read".

Click ok to terminate the program.

I hope someone could please help me with this I received 60 - 80 csv files a week and everythime I save on I get this pop up message!

Thank you!

Leslie

This is an SSIS (SQL Server Integration Services) forum. You might have better success in the Office forums: http://www.microsoft.com/office/community/en-us/default.mspx

CSV file into SQL Server

Hello GUyz,

When I do a Bulk insert to import a CSV file into the SQL Server i get the following error.

Msg 511, Level 16, State 1, Line 1

Cannot create a row of size 20809 which is greater than the allowable maximum of 8060.

The statement has been terminated.

Is there a way to get over it. Thank you.

--SRI.

Did you specify both a Row Delimiter and a Column Delimiter?

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes. I did it. But doesn't work.|||

Sri. wrote:
Yes. I did it. But doesn't work.

You're going to have to provide plenty more details than, "doesn't work."

Can you provide sample data? How long is each row, typically? What have you tried for the various settings in the source config?|||

It has like 982 columns and 2545 rows.

Some of the data in the rows is really big . It has like more than 200 characters.

SO I went to the advanced properties in the data source and changed the type as Unicode string and changed the OutputColumnwidth to 300 and tried to execute it but it says "Text was truncated". So I tried with bulkInsert but it gave me the error I showed above.

|||If it's truly a comma separated file, then you shouldn't have any issues, unless the data types defined in the source connection aren't long enough. You need to go through the advance properties and select the columns to verify that the lengths are correct.|||

Are you sure your file is properly formatted? All delimiters in each row need to be present in order for the file to be properly parsed.

Try to look for rows that cause problems, either by dividing the file in halves or redirecting rows with errors/truncations.

HTH.

|||

What are the data types of the columns in the SQL Server table? If you define them all as VARCHAR(8000) or NVARCHAR(4000) for example, it is possible to have a valid table definition, but have scenarios where the data being inserted to create a new row (or supplied to update an existing row) exceed the maximum table size.

A single "standard" row in SQL Server cannot exceed the maximum row size. You can get around that by defining your columns as TEXT or NTEXT instead of VARCHAR or NVARCHAR, or by defining them as VARCHAR(MAX) or NVARCHAR(MAX) if you are using SQL Server 2005. These BLOB data types are stored outside the row itself, so you can get around the row size limit.

CSV file as a data source

Hi,
Can a CSV file be used as a data source for Microsoft Analysis Services 2005?

Rgds
hari

I tried it, i couldn't get it to work. I even tried being sneaky and linking the csv in an access mdb then using the jet provider, but it didn't seem to like that either..

C|||You can create SSIS pipeline, which will read CSV's and then uses AS Processing Task to load data into partitions. I have done it in the past, and while it is not a smooth ride (especially when your partitioning scheme of the cube is not trivial), it can be made work.|||

Ahh...thanks guys!!
I will try your suggestions.

CSV file as a data source

Hi,
Can a CSV file be used as a data source for Microsoft Analysis Services 2005?

Rgds
hari

I tried it, i couldn't get it to work. I even tried being sneaky and linking the csv in an access mdb then using the jet provider, but it didn't seem to like that either..

C
|||You can create SSIS pipeline, which will read CSV's and then uses AS Processing Task to load data into partitions. I have done it in the past, and while it is not a smooth ride (especially when your partitioning scheme of the cube is not trivial), it can be made work.|||

Ahh...thanks guys!!
I will try your suggestions.

csv file

how to set the connection string to connect a csv file with reporting
service.
TIA.
DonaldI have not used csv files, but you should be able to setup an ODBC datasource
with the text driver. You can then use this source to use your csv file as
table. I have successfully done this with txt files. You might need to
rename the files to be .txt versus .csv.
Good Luck
Stan
"Donald" wrote:
> how to set the connection string to connect a csv file with reporting
> service.
> TIA.
> Donald
>
>

csv export without headings

Hi,

is it possible to export a report to a csv file without the headings?

at best i have been able to hide the headings in the report, but when exported to csv file it includes a row at the top of commas, then the data follows. I need the file to be just the data.

thank.

Hi,

Most of the elements on a report contain some properties concerning the output format. You can disable/enable certain elements.

For example if you don't want to export the headers of a table, then you need to perform the following actions:

select a cell in the header of the table|||Hi,

thanks for replying, that kind of worked. I am still getting the first row to be a row of commas. do you know how i can get it to omit this first row of commas?

if not, we could have all the column headings, but because it's a matrix, it's not exporting my static column headings. any ideas on this?

Thanks.|||I'm still having trouble with this so if anyone has any ideas i'd love to hear them.

My report has a matrix no it, which has 6 static columns. Above the static columns i've placed a table object. In that table object i've remove the footer and details section and added 6 columns so that the 6 static column in my matric can have column headings.

When i export to CSV the static column headings do not get exported, instead it exports just the columns that seperate the fields. The column headings for the matrix appear after the 6 commas.

I either need to hide this whole first row in the CSV file, or have it populated properly with the static column names.|||

To eliminate column title row that you see before the data, use NoHeader option:

http://msdn2.microsoft.com/en-us/library/ms155365.aspx

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row.

CSV renderer doesn't stack data from 2 different data regions, so your trick with table above matrix won't get you the result you are looking for.

|||thank you so much. that has worked!!!!!|||

Hi, Denis

I have been trying for two days to figure out how to export reports in csv without headers. You've explained it but I still can't figure out how to do it. Can you assist me?

1) I'd like to be able to have the user select the csv format and export (from Report Manager) to csv format w/o headers...and

2) in a subscription that will automatically export a report to users in a csv w/o headers format. Can you give me some examples?

thanks!!!

|||

Denis

Specifically, you indicated:

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row....

how do I (in RS2005) configure my csv rendering extension to always omit the header row....

|||Hi Chaps

I have a Matrix Report with dynamic column headings
ie headings change subject to date
I cannot get the coulmn headings to export.

Is this possible with a Matrix report ?
any guidance most appreciated.

Regards

JohnJames

csv export without headings

Hi,

is it possible to export a report to a csv file without the headings?

at best i have been able to hide the headings in the report, but when exported to csv file it includes a row at the top of commas, then the data follows. I need the file to be just the data.

thank.

Hi,

Most of the elements on a report contain some properties concerning the output format. You can disable/enable certain elements.

For example if you don't want to export the headers of a table, then you need to perform the following actions:

select a cell in the header of the table|||Hi,

thanks for replying, that kind of worked. I am still getting the first row to be a row of commas. do you know how i can get it to omit this first row of commas?

if not, we could have all the column headings, but because it's a matrix, it's not exporting my static column headings. any ideas on this?

Thanks.
|||I'm still having trouble with this so if anyone has any ideas i'd love to hear them.

When i export to CSV the static column headings do not get exported, instead it exports just the columns that seperate the fields. The column headings for the matrix appear after the 6 commas.

I either need to hide this whole first row in the CSV file, or have it populated properly with the static column names.
|||

To eliminate column title row that you see before the data, use NoHeader option:

http://msdn2.microsoft.com/en-us/library/ms155365.aspx

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row.

CSV renderer doesn't stack data from 2 different data regions, so your trick with table above matrix won't get you the result you are looking for.

|||thank you so much. that has worked!!!!!
|||

Hi, Denis

I have been trying for two days to figure out how to export reports in csv without headers. You've explained it but I still can't figure out how to do it. Can you assist me?

1) I'd like to be able to have the user select the csv format and export (from Report Manager) to csv format w/o headers...and

2) in a subscription that will automatically export a report to users in a csv w/o headers format. Can you give me some examples?

thanks!!!

|||

Denis

Specifically, you indicated:

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row....

how do I (in RS2005) configure my csv rendering extension to always omit the header row....

|||Hi Chaps

I have a Matrix Report with dynamic column headings
ie headings change subject to date
I cannot get the coulmn headings to export.

Is this possible with a Matrix report ?
any guidance most appreciated.

Regards

JohnJames

csv export without headings

Hi,

is it possible to export a report to a csv file without the headings?

at best i have been able to hide the headings in the report, but when exported to csv file it includes a row at the top of commas, then the data follows. I need the file to be just the data.

thank.

Hi,

Most of the elements on a report contain some properties concerning the output format. You can disable/enable certain elements.

For example if you don't want to export the headers of a table, then you need to perform the following actions:

select a cell in the header of the table|||Hi,

thanks for replying, that kind of worked. I am still getting the first row to be a row of commas. do you know how i can get it to omit this first row of commas?

if not, we could have all the column headings, but because it's a matrix, it's not exporting my static column headings. any ideas on this?

Thanks.
|||I'm still having trouble with this so if anyone has any ideas i'd love to hear them.

When i export to CSV the static column headings do not get exported, instead it exports just the columns that seperate the fields. The column headings for the matrix appear after the 6 commas.

I either need to hide this whole first row in the CSV file, or have it populated properly with the static column names.
|||

To eliminate column title row that you see before the data, use NoHeader option:

http://msdn2.microsoft.com/en-us/library/ms155365.aspx

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row.

CSV renderer doesn't stack data from 2 different data regions, so your trick with table above matrix won't get you the result you are looking for.

|||thank you so much. that has worked!!!!!
|||

Hi, Denis

I have been trying for two days to figure out how to export reports in csv without headers. You've explained it but I still can't figure out how to do it. Can you assist me?

1) I'd like to be able to have the user select the csv format and export (from Report Manager) to csv format w/o headers...and

2) in a subscription that will automatically export a report to users in a csv w/o headers format. Can you give me some examples?

thanks!!!

|||

Denis

Specifically, you indicated:

You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row....

how do I (in RS2005) configure my csv rendering extension to always omit the header row....

|||Hi Chaps

I have a Matrix Report with dynamic column headings
ie headings change subject to date
I cannot get the coulmn headings to export.

Is this possible with a Matrix report ?
any guidance most appreciated.

Regards

JohnJames

CSV export with single quotations for string fields

I need to export data to CSV file from Sql Reporting Services.

I am including single quotations in my view to display in SRS for string fields.

After export if we open in Notepad, for string field it is adding multiple quotations.

How to add single quotations for string fields - CSV files,

Please let me know if anybody knows.

Thx

Vijji

Yes. You can specify this value (it's called the "Qualifier") for the CSV renderer -- see http://msdn2.microsoft.com/en-us/library/ms155365.aspx

-- and I believe you can set the default for the value in the rsreportserver.config file as well -- see http://msdn2.microsoft.com/en-us/library/ms156281.aspx

>L<

|||

Hi Lisa Nicholls

Thank you so much for the response,

I changed in the config file as below

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<NoHeader>True</NoHeader>
<Qualifier>False</Qualifier>
</DeviceInfo>
</Configuration>
</Extension>

It is working fine.

I have another issue, any suggestions will be very helpful.

In my data string field having comma, while importing it is treating as seperate column.

How do we control with in the string comma values.

Thank you again

Vijji

|||

hi there,

This is on the same page of the docs I've already sent you to <s>. Use a different value for FieldDelimiter along with Qualifier, if your data contains a comma.

(Actually I think that they got the terms backwards -- it should be that the item for which the RS default is a comma is termed a "Field Separator" and the item for which the RS default is quotation mark is called the "Field Delimiter" -- but you get the idea <s>.)

HTH,

>L<

Wednesday, March 21, 2012

Current SQL Server Logs

Sunday, February 19, 2012

CSV to SQL Server import

CSV Scheduled Report

Friday, February 17, 2012

CSV Scheduled Report

csv import which runs stored procedure

csv import into ms sql 2000

csv generation

CSV from SQL

CSV files

CSV file..

csv file to sql table

CSV file saved as an Excel file - error message

CSV file into SQL Server

CSV file as a data source

CSV file as a data source

csv file

csv export without headings

csv export without headings

csv export without headings

CSV export with single quotations for string fields

Cursors vs. Queries

Blog Archive

About Me