Hello, I tried to use SQLCMD and it still has spaces in my txt file with pipe delimited.
command use:C:\Windows>sqlcmd -S bmprolawsql01 -d prolaw -s "|" -y -i "d:\sqldata\climat.sql" -o e:\import\newmatter.txt
my output file has spaces before and after the pipe delimiter.
USE prolaw
declare @s varchar(100)='(FILE';
select clientid
,ClientSort as ClientName
,'Y' as Enabled
,'N' as HIPPA
,matternum
,case
when charindex(@s,[description])>0
then left(cast([description] as varchar(max)),charindex(@s,[description])-1)
else [description]
end as MatterName
,'Y' as Enable
,'N' as HIPPA
,Status
,CASE
WHEN areaoflaw = 'Family Law' then 'FL'
WHEN areaoflaw = 'Workers Comp' then 'WC'
WHEN areaoflaw = 'Criminal' then 'CR'
WHEN areaoflaw = 'Corporate Business' then 'CB'
WHEN areaoflaw = 'Personal Injury' then 'PI'
WHEN areaoflaw = 'Litigation' then 'LI'
WHEN areaoflaw = 'Estate Matters' then 'EM'
WHEN areaoflaw = 'Miscellaneous' then 'MI'
WHEN areaoflaw = 'Appeals' then 'AP'
WHEN areaoflaw = 'Real Estate' then 'RE'
END as areaoflaw
,Status
--- , OpenedDate
from matters
where OpenedDate > DATEADD(DAY,-16,GETDATE())
and status = 'ACTIVE'
order by Clientid
,matterid
;