Writing text files as Unicode

  • Hi,

    I have this stored procedure (attached), that writes a semi colon delimited text file with column headers. It's pretty handy, but it writes files in ANSI, and I need them in Unicode. Is there a way to edit any part of this procedure to write them as Unicode?

    Is it even possible, in general?

    Thanks

  • It saddens me a bit that the code is accessing the command prompt and the file system from within T-SQL, especially as part of an applicaiton design, but from your other thread I know the situation and know you are locked into this design. That said, if you ever have the chance to rewrite things SSIS would be my preference to accomplish what is being done.

    That aside, if you swap out the -c switch for a -w switch in your bcp command I think you will get where you want to go.

    bcp Utility - SQL Server 2005

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/12/2013)


    It saddens me a bit that the code is accessing the command prompt and the file system from within T-SQL, especially as part of an applicaiton design, but from your other thread I know the situation and know you are locked into this design. That said, if you ever have the chance to rewrite things SSIS would be my preference to accomplish what is being done.

    That aside, if you swap out the -c switch for a -w switch in your bcp command I think you will get where you want to go.

    bcp Utility - SQL Server 2005

    Thanks. I was just reading a bit about that.

    I know, Google. But do you have a favorite resource for how to do things like that with SSIS? I tried reading the series of articles on here about automating file drop folders to import, but couldn't do much with it...

    I don't want to waste your time; I don't have access to a lot of server-side set up. It's kind of a situation where if I can do it and get it set up and use it and it works, it's fine, but getting other people to change things is difficult (sys admins, etc.). No one wants to 'waste' their time making changes for me to test things.

    EDIT: That didn't work so well:

    http://i.imgur.com/Nv3ij9i.png

  • Hmmm, -w works for me. Please check the command line you are issuing to xp_cmdshell.

    You can try this simple command line as a proof-of-concept.

    bcp "select top 10 object_id, name from master.sys.tables;" queryout "C:\1.txt" -t";" -T -w

    Re: SSIS, I would recommend anything from Brian Knight. You are on 2005 so here is a good book:

    Professional SQL Server 2005 Integration Services. I am actually reading the reprise of the same book geared towards 2012.

    Even though you are on 2005 I think it's worth learning 2012. Most of the concepts are the same so will still be backward compatible into 2005 development. Here is a good training book to go along with the textbook-style book above:

    Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I'm running these:

    set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+ @filePath + @file_name+'" -w -t";" -T '''

    exec(@sql)

    set @sql='exec master..xp_cmdshell ''bcp " select ' + @columns1 + ' from '+@db_name+'..'+@TableName+' where statusflag = 0 and reps >= '+ @StartRep + ' and reps <= ' + @EndRep + ' " queryout "' + @data_file+'" -w -t";" -T '''

    --print @sql

    exec(@sql)

    When I run your code, I get this error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'queryout'.

    I'm going to check those books out, for sure.

    Thanks

  • Hmm, I tested my query on a 2005 instance and it worked fine:

    PS And to confirm, the file looks good:

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Yeah, I boneheaded that one. It works now, and the file output is correct.

    So, I took it and applied it to the table I want to write as a file, and it works fine there as well. I guess it's something else in the process making it go woogy. Maybe it's when the files get combined?

    set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@filepath+@file_name+''''

  • By default TYPE is not going to preserve Unicode data for you and I do not know of a way to setup the environment or call it where it will.

    You could try building a COPY command:

    copy File1+File2+FileN DestinationDataFile /Y /B

    Example:

    copy C:\1.txt+C:\2.txt+C:\3.txt C:\DataFile.txt /Y /B

    In your code the loop is appending to the file for each iteration using TYPE. In using COPY you'll want to change it so the loop just builds the command with all the files and pluses, and then just make one call to xp_cmdshell after the loop is done to append all the files together.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/12/2013)


    By default TYPE is not going to preserve Unicode data for you and I do not know of a way to setup the environment or call it where it will.

    You could try building a COPY command:

    copy File1+File2+FileN DestinationDataFile /Y /B

    Example:

    copy C:\1.txt+C:\2.txt+C:\3.txt C:\DataFile.txt /Y /B

    In your code the loop is appending to the file for each iteration using TYPE. In using COPY you'll want to change it so the loop just builds the command with all the files and pluses, and then just make one call to xp_cmdshell after the loop is done to append all the files together.

    Does this make sense to you?

    set @sql= 'exec master..xp_cmdshell ''COPY \\server\SurveyComputing\Sample\SentToSPSS\'+@data_file+' + \\server\SurveyComputing\Sample\SentToSPSS\'+@file_name+''' \\server\SurveyComputing\Sample\SentToSPSS\'+''@file_name'+''_copied' + '/Y /B''''

  • SET @sql = 'EXEC master.sys.xp_cmdshell ' +

    '''COPY \\server\SurveyComputing\Sample\SentToSPSS\' + @data_file +

    '+\\server\SurveyComputing\Sample\SentToSPSS\' + @file_name +

    ' \\server\SurveyComputing\Sample\SentToSPSS\' + @file_name + '_copied /Y /B''';

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/12/2013)


    SET @sql = 'EXEC master.sys.xp_cmdshell ' +

    '''COPY \\server\SurveyComputing\Sample\SentToSPSS\' + @data_file +

    '+\\server\SurveyComputing\Sample\SentToSPSS\' + @file_name +

    ' \\server\SurveyComputing\Sample\SentToSPSS\' + @file_name + '_copied /Y /B''';

    It took some tinkering, but it finally works. The issues were my fault. I didn't see that the @data_file variable already had the file path concatenated, so it was unnecessary in the copy statement. After that it was copying the headers to the bottom of the new file, so I had to change the order of the statement.

    But yeah, we've got full Unicode files writing. Thank you again for the last two days of your help and time. It's much appreciated.

    --Combine 2 files above

    SET @sql = 'EXEC master.sys.xp_cmdshell ' + '''COPY ' + @filePath + @file_name + '+' + @data_file + ' ' + @filePath + @file_name + ' /Y /B''';

    exec(@sql)

  • You're very welcome! I would be happy to assist with any SSIS questions you might have down the line too 😉

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • It's going to be a few drinks before I attempt that.

    :blink:

  • Heh, no need to drink. At first, SSIS will make your head spin anyway 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply