OINV.Address new line issue when exporting to flatfile

[former_member268870]

Experts,

I need to export the OINV table with Addresses and found that the Address and Address2 fields have unseen new lines in the data.

When I write a SQL query and export to Excel this data does not pull through correctly because new lines in the data.

I cannot use INV12 for the addresses, as this is sometimes blank. I cannot use the OCRD addresses as an address can differ on the Invoice. Is there another address table linked to OINV that I'm missing?

Here is a basic query I'm using:

SELECT 
	T0.[Address] AS 'DocBillTo',
	T0.[Address2] AS 'DocShipTo',
	T0.[DocNum],
	T11.[StreetB], T11.[CityB], T11.[ZipCodeB], T11.[StateB], T11.[CountryB],
	T11.[StreetS], T11.[CityS], T11.[ZipCodeS], T11.[StateS], T11.[CountryS]

FROM 
	OINV T0  
	LEFT OUTER JOIN 
	INV12 T11 ON T0.[DocEntry] = T11.[DocEntry]

WHERE T0.[DocNum] = '199997' OR T0.[DocNum] = '200697'

Here is the Text file I generate via SQL.

address-issue.txt

Here is how it opens up in Excel, whether I use txt or csv.

[former_member390407 · Accepted solution]

Hi Marli,

If you just want to simply get rid of new lines characters you can use the REPLACE function:

SELECT DocEntry, 
	DocNum, 
	REPLACE(REPLACE(Address, CHAR(13), ''), CHAR(10), ' ') AS BillTo, 
	REPLACE(REPLACE(Address2, CHAR(13), ''), CHAR(10), ' ') AS ShipTo 
FROM OINV 
WHERE DocNum = '199997' OR DocNum = '200697'

CHAR(13) - Line feed

CHAR(10) - Carriage return

[Johan_H · Accepted solution]

And for good measure CHR(9) - tab