I have reading rights on a sql server where I need to run a sql script to copy data to excel spread sheet. The problem I am having is that most of the columns in the returned data set results have large amount of text. some of these columns have data with different delimiters such as " or some have html tags. This causes the data when saved as csv file to be jumbled up. I tried straight copy with header and paste, this copied and pasted the data fine however, even though, I selected all rows, for some reason not all rows were copied. I was missing few hundred rows. I used the method of saving as text file csv, but the data was jumbled up due to the special characters in some of the fields and it is very hard to sort it out in excel since I am dealing with about 10000 rows of data. I used the bar (|) delimiter but still did not work. I do not have permission to create any object in this sql server since it is production server, however, I can create new query and run my script. what is the best way to get the data out to excel?
Did you try bcp or OPENROWSET?
Perhaps you could use a format file with bcp to get data based on your custom format to cause no issues due to delimiters present.
See a simple example here
Thank you very much for your reply. I tried that but I am not sure about few things:
1) how do I create the .fmt file?
2) does this the script create the .txt file?
I tried it but did not get anything except the results in the sql server. what should I so after that?
- Bearbeitet al.sallam Sonntag, 27. Oktober 2013 22:06
Try below links
sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.
Try import export wizard to move the data in and out of the sql server.
Here is way to it: , you can push data to varity of provider( like excel, csv files and sql server).