Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Getting hidden hex characters in text file from SQL query

    All,

    I have a customer who wants to send me a text file that is being created from the following SQL Query:

    SELECT
    co.FEIN as ISA05, co.[name] as ISA06,
    CASE st.shiptonum WHEN '' THEN cu.CustID ELSE cu.CustID +'-'+ st.shiptonum END as ISA08,
    CONVERT(VARCHAR(10),GETDATE(),101) as ISA09, CONVERT(VARCHAR(8),GETDATE(),108) as ISA10,
    'ST' as Set_Header, '810' as Set_ID, convert(varchar,id.Invoicenum) + Replace(STR(id.invoiceline, 3),' ', '0') as [Set_Ctrl_Num],
    'BIG' as [Set_Header_BIG], iv.invoicedate as [InvoiceDate], iv.invoicenum as [InvoiceNumber], oh.orderdate as [PODate], iv.ponum as [PONumber], iv.invoicetype as [TypeCode],
    'REF' as [Set_Header_REF], '' as [Ref_ID], '' as [DUNS],
    'N1' as [Set_Header_N1] , cu.CustID as [NameID], st.Name as[Name], '' as [IDCodeQualifier], '' as [IDCode],
    'N2' as [Set_Header_N2] , COALESCE(cc.Name, '') as [AttentionName],
    'N3' as [Set_Header_N3] , st.Address1 as [Address1],
    'N4' as [Set_Header_N4] , st.city as [City], st.state as [State], st.Zip as [Zip],
    'ITD' as [Set_Header_ITD], iv.TermsCode as [ITD01], '' as [ITD06],
    'IT1' as [Set_Header_IT1], id.poline as [POLine], id.ourshipqty as [Qty], id.ium as [UoM], id.docunitprice as [UnitPrice], '' as [ProdIDQual], id.partnum as [PartNum],
    'PID' as [Set_Header_PID], 'F' as [PID01], convert(varchar(255),id.linedesc) as[PartDesc],
    'TDS' as [Set_Header_TDS], iv.docinvoiceamt as [TotalInvoiceAmt],
    'TX1' as [Set_Header_TX1], COALESCE(ix.TaxCode, '') as [TaxCode], COALESCE(ix.doctaxamt, 0) as [TaxAmount],
    'SAC' as [Set_Header_SAC], 'C' as [ChargeID], COALESCE(im.MiscCode, '') as [ChargeCode], COALESCE(im.docmiscamt, 0) as [ChargeAmount]

    FROM MfgSys803.dbo.InvcHead iv WITH (NOLOCK)

    LEFT JOIN MfgSys803.dbo.Company co ON co.company = iv.company
    LEFT JOIN MfgSys803.dbo.InvcDtl id ON id.company = iv.company AND id.invoicenum = iv.invoicenum
    LEFT JOIN MfgSys803.dbo.ShipTo st ON st.company = id.company AND st.custnum = id.custnum AND st.Shiptonum = id.shiptonum
    LEFT JOIN MfgSys803.dbo.CustCnt cc ON cc.custnum = id.custnum AND cc.shiptonum = id.shiptonum AND cc.connum = id.shpconnum
    LEFT JOIN MfgSys803.dbo.Customer cu ON cu.company = id.company AND cu.custnum = id.custnum
    LEFT JOIN MfgSys803.dbo.InvcTax ix ON ix.invoicenum = id.invoicenum AND ix.invoiceline = id.invoiceline
    LEFT JOIN MfgSys803.dbo.InvcMisc im ON im.invoicenum = id.invoicenum AND im.invoiceline = id.invoiceline
    LEFT JOIN MfgSys803.dbo.OrderHed oh ON oh.company = iv.company AND oh.ordernum = iv.ordernum


    The problem is that when we get the file, it shows the following when viewing in hex:


    ".9.5.-.3.8.8.6.1.9.2.".,.".P.i.c.o." ETC.

    The file should look like this:

    "95-3886192","Pico"

    Somehow it's starting the file with the hex values of FF FE and inserting a hex 00 in between each character.

    When you view the file in Notepad, no errant characters, only when you use UltraEdit in hex mode do you find the problem.

    Any ideas on how to fix? I need the data as text only for processing and these extra hex values are really screwing things up. I'm anticipating a lot of data, so daily manual manipulation of the data is not an option.

    Any help you can give is appreciated.

    Jeff

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,550
    The file has been saved in UNICODE format, certainly. Open it in Notepad, and save it in ANSI to solve the problem. Then tell the person who gave you this file to save his in ANSI, ans well.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    MCrowley is correct, this is a standard Windows UNICODE text file. Ultra-Edit will gleefully edit these files, there is no problem there. If you want Ultra-Edit to make the file into conventional ASCII, use Ultra-Edits "File | Conversions | Unicode to ASCII" menu item to convert the file, but beware that this can be a one-way trip... You probably can't convert back to UNICODE once the data has been converted to ASCII.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2010
    Posts
    2

    Thanks!!

    I love it when I need to figure out a problem that my customers programmers should know. I haven't had to deal with a file conversion issue in nearly 10 years... amazing what you forget. Anyhoo, thanks to everyone for their input. Answers were spot on and the problem is solved.

    Great forum BTW.

    Jeff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •