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

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.

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

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