If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > Getting hidden hex characters in text file from SQL query

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jan 2010
Posts: 2
Getting hidden hex characters in text file from SQL query


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

co.FEIN as ISA05, co.[name] as ISA06,
CASE st.shiptonum WHEN '' THEN cu.CustID ELSE cu.CustID +'-'+ st.shiptonum END as ISA08,
'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.-.".,.".P.i.c.o." ETC.

The file should look like this:


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.

Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,477
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.
Reply With Quote
  #3 (permalink)  
Resident Curmudgeon
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
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.

In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Jan 2010
Posts: 2

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.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On