Sql Server table data to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Sql Server table data to Excel

Author

Topic

Shanky_coder
Starting Member

3 Posts

Posted - 2012-02-17 : 02:28:24

Hi

I am currently woking on transfering the table contents in sql server in an csv file i have created a stored procedure which would do same but the prob i am facing is the data in the table is not clean it contains tab,newline etc so i had to clean the data i had applied the folowing procedure
declare @columns varchar(8000), @sql varchar(8000), @sql1 varchar(8000),@data_file varchar(100)
set @columns=''
--'@columns+''replace(replace(replace(''+column_name+'',Char(10),''''''''),Char(13),''''''''),Char(19),'''''''')'''
--print @sql
select

@columns=@columns+'replace(replace(replace('+column_name+',Char(10),''''),Char(13),''''),Char(19),'''') as '+column_name+ ', '
from
information_schema.columns
where
table_name='Table_name'
ORDER BY ORDINAL_POSITION
print @columns
set @sql=len(@columns)
Print @sql
the table_name contains around 300 columns with the column_name of min 20 characters
When i run the above query i get only few columns instead of all the columns so i tried to find the length which gives me the result as 4000.

I had declared @columns as Varchar(8000) i dont know why this issue is coming up?? is there any other way i can clean the data an the transfer it into the file

It would be of great help if any one can help me in this

Many thanks in advance!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 15:07:29

why not make it varchar(max) as it may go over 8000 limit due to other functions used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Shanky_coder
Starting Member

3 Posts

Posted - 2012-02-21 : 03:03:30

I am using sql server 2000 which doest support Varchar(MAX)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:42:56

can you explain what you're trying to do with above code? is intention to tidy up all columns in your databse?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Shanky_coder
Starting Member

3 Posts

Posted - 2012-02-22 : 01:26:24

I have to transfer the table contents in sql server to a file and i am using the bcp command to do the same. but the issue that i came across was the data was not clean and it contained \n \t which causes it to print in the nextline of the csv file midway in the record.so i thought of transfering the cleaned record to an temp table an use bcp on it.hence i was trying to have the replace command in one variable to varchar(8000) transfer it a table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources