Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 File Format

Author  Topic 

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-08-26 : 14:41:07
I have a SQL table that I need to export into a file. The requirements need to be this...

File should be pipe ("|") delimited .txt file with date in filename (CCYYMMDD)

How can I do that?

My Query that I have is like this that goes into my table Unless, this portion doesn't matter, but I wanted to share with you.



SELECT Distinct

LEFT([First Name],100) [First Name],
LEFT([Last Name],50) AS [Last Name],
LEFT([Product],40) AS [Network],
LEFT([Degree],10) AS [Title],
LEFT([Tax ID],9) AS [TAX ID],
LEFT([Address 1],75) AS [Address 1],
LEFT([Address 2],75) AS [Address 2],
LEFT([City],25) as [City],
LEFT([St],2) AS [ST],
LEFT([Zip],10) AS [ZIP],
LEFT([COUNTY],100) AS [County],
LEFT([Phone],12) AS [Phone],
CONVERT(varchar(10), [Effective Date], 112) AS [Provider Effective Date], ---CCYYMMDD
CONVERT(VARCHAR(10), [Termination Date], 112) AS [Provider Termination Date], ---CCYYMMDD

Case

When [Address 1] = [Billing Address] or [pro_btag] = 'Y' Then 'P'
Else ''
End as [Location Type],

LEFT([Spec 1],50) AS [Primary_Specialty],
LEFT([NPI],40) as [Physician_NPI],
LEFT([PCS Number],40) as [Provider_Key],
LEFT([PRO ID],40) as [Sequence_Key]

From vw_test

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 15:01:15
bcp YourDb.dbo.YourTbl out c:\temp\YourTbl_20140826.txt -T -Sserver1\instance1 -c -t"|" -r\r\n


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-08-26 : 15:14:32
bcp MonthEnds.dbo.HMHP out c:\temp\YourTbl_20140826.txt -T -bisql\instance1 -c -t"|" -r\r\n

I added my info, but I am getting an error saying below

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.







quote:
Originally posted by tkizer

bcp YourDb.dbo.YourTbl out c:\temp\YourTbl_20140826.txt -T -Sserver1\instance1 -c -t"|" -r\r\n


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 15:24:57
You need to run it from a command prompt. (Start..Run..cmd)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -