Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ANSI to UTF-8 conversion Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 12:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 8:29 PM
Points: 11, Visits: 32
I am running a .bat file that runs a SQL script that extracts data from a SQL Server database and writes the data to a flat file. The encoding of the file is ANSI. What code can I add to the .bat file to take this ANSI file and convert it to UTF-8?
Post #1431690
Posted Friday, March 15, 2013 1:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
What tool are you using to extract the data?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1431719
Posted Monday, March 18, 2013 11:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 8:29 PM
Points: 11, Visits: 32
The .bat file is running a SQL query from the command line, using the following format:

sqlcmd -S <SQL Server instance> -i <SQL query> -o <output file> -d <SQL Server database name> -h -1 -W -r 0
Post #1432278
Posted Monday, March 18, 2013 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
Adding the -u switch will give you a Windows Unicode (i.e. UCS-2) file. To get to UTF-8 you'll have to enlist the help of some other tool. Why do you need UTF-8? It's not native for Windows, UCS-2 (subset of UTF-16LE) is native.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1432343
Posted Tuesday, March 19, 2013 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 8:29 PM
Points: 11, Visits: 32
The system that the file is being imported into requires UTF-8, so there's no flexibility there.
Post #1432872
Posted Tuesday, March 19, 2013 3:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
You could try using SSIS to do the conversion for you on the fly. Use an OLE DB Source in a Data Flow to get the data from the database and send it to a Flat File Destination configured to write a file in UTF-8 format.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1432918
Posted Thursday, April 11, 2013 3:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 1,796, Visits: 5,801
trigonom (3/19/2013)
The system that the file is being imported into requires UTF-8, so there's no flexibility there.


Hi, did you find a solution?

I have one using scripting to convert the file through ADODB.Stream objects (SSIS sucks at producing UTF-8) but it is not performing well, so am looking for another..What did you end up using?

Here is the script I found on t'internet in case you want it:

function ansiToUtf8(fileName) {
var indir = "ansiin";
var outdir = "utf8out";
var ansi = WScript.CreateObject("ADODB.Stream");
ansi.Open();
ansi.Charset = "x-ansi";
ansi.LoadFromFile(indir+"\\"+fileName);
var utf8 = WScript.CreateObject("ADODB.Stream");
utf8.Open();
utf8.Charset = "UTF-8";
utf8.WriteText(ansi.ReadText());
utf8.SaveToFile(outdir+"\\"+fileName, 2 /*adSaveCreateOverWrite*/);
ansi.Close();
utf8.Close();
}



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1441144
    Posted Thursday, April 11, 2013 3:46 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:32 PM
    Points: 1,796, Visits: 5,801
    Ok, ignore that...I found my answer.

    Apparently ADODB.Stream for reading the file is terrible in this code.

    This worked much much much better:

    function ansiToUtf8(fileName) {
    var indir = "ansiin";
    var outdir = "utf8out";
    var ansi = WScript.CreateObject("Scripting.FileSystemObject");

    var utf8 = WScript.CreateObject("ADODB.Stream");
    utf8.Open();
    utf8.Charset = "UTF-8";
    utf8.WriteText(ansi.OpentextFile(indir+"\\"+fileName,1,false,false).ReadAll());
    utf8.SaveToFile(outdir+"\\"+fileName, 2 /*adSaveCreateOverWrite*/);
    utf8.Close();
    }



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1441160
    Posted Thursday, April 11, 2013 6:22 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 10:47 AM
    Points: 7,126, Visits: 12,727
    SSIS sucks at producing UTF-8

    Why?


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1441208
    Posted Thursday, April 11, 2013 6:44 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:32 PM
    Points: 1,796, Visits: 5,801
    opc.three (4/11/2013)
    SSIS sucks at producing UTF-8

    Why?


    Because I couldn't get it to do it.

    I tried.

    Lots.

    If you know how, and have actually seen it work, I would seriously love to know

    I know you can set the Flat file connection manager to UTF-8 and you can try and force your data into the right code page for it (65001 i think without checking), but I just couldn't get it to actually produce UTF-8 and the same was true for all the people that popped up on Google with the same problem.

    I haven't tried in SQL2012 yet though.



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1441215
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse