February 10, 2009 at 2:29 pm
I use the following code to write a csv document (stored in a varchar(max) field in my database) to the file system:
SET @cmd = 'bcp "SELECT Document FROM CSV_Archive.dbo.DocStore WHERE DocID ='+ CAST(@DocID AS VARCHAR)+'" queryout '+@oFile+' -S -T -N';
EXEC master..xp_cmdshell @cmd;
When I view the file output by the above code it has some odd characters at the beginning of the file like so:
Ü‚Nul,Nul,Nul‚Nul,Nul,Nul
The rest of the file is as expected.
Does anyone have any idea how I might get rid of these characters (which were not in the original file before storage)? Or can anyone suggest an alternative method of getting the file out of the database?
February 10, 2009 at 2:50 pm
[font="Verdana"]The -N option for bcp is "-N keep non-text native". That means it is storing the binary form of your numbers in the file. Try using a character form, such as "-c character type" instead.[/font]
February 11, 2009 at 2:00 am
Bruce - Thanks for your reply I appreciate it. However using -c returns a file full of garbage (a single row of digits to be precise). Just to be clear the files stored are csv files that contain text, dates and numbers. Text values are quoted, the rest is not. Its a standard csv output from an MS Access application. I've put them into SQL 2005 using:
INSERT INTO dbo.DocStore (DocName, Document)
SELECT ,
BulkColumn FROM Openrowset(Bulk , SINGLE_BLOB) AS blob.
I have a SQL client tool that allows me to view blobs and the stored document looks good in that - i.e. without the odd characters at the front.
The only real annoyance is that most users view the csv files in MS Excel. When I output them they don't open correctly in Excel. If I open them in a text editor and remove the Ü‚Nul,Nul,Nul,Nul,Nul,Nul
characters from the front then they are just fine.
It's got me baffled.
Any other suggestions will be most welcome.
😉
February 12, 2009 at 12:34 pm
[font="Verdana"]I think you might be better switching to SSIS to create your output. You have a lot more options on how you format the results.
To get you started, in SQL Server Management Studio, right click on the database containing your data, then click on Tasks, then Export Data...
This should bring up a wizard that's fairly easy to work through. Work through that until you have the output in a form you like, then you can save the results and assign it to a job (with or without schedule), so the file will get created on a schedule or whenever you decide to run the job.
[/font]
February 13, 2009 at 1:48 am
Thanks again Bruce - you've got it! Although the export wizard dosn't provide the solution SSIS does. The 'export column' transform does the trick without any spurious characters and I can build a package that gets a 'required file name or id' from the user (probably me!) and a file path to output to.
Thanks for your interest in my problem. You have restored my (very) tarnished faith in forums. I posted the same question in two Microsoft forums with not a single response to date. I can't even find my posts again now!
God bless SQLServerCentral and all who sail in her.
February 15, 2009 at 2:30 pm
[font="Verdana"]You're welcome. I'm glad SSIS did the trick![/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply