Export Excel File as a Pipe Delimited Text File Through bcp (Just an Option with bcp )

If your excel file have valid column name (no space, no special character…)

Step 1:
You can run this query to load the Excel file into a table

--into myview2
  'Excel 12.0;Database=c:\temp\test.xlsx;HDR=YES;IMEX=1', 
  'SELECT * FROM [Sheet1$]'


Step 2:

      DECLARE @cmd VARCHAR(4000) 
   set @cmd = 'BCP  [test1].[dbo].[myView2] format nul   -f  "C:\temp\myFormatFile2.xml" -x -c -t\^| -r ^|\n -T -S'+ @@servername
exec master..xp_cmdshell @cmd
 --   -r -r ^|\n is for special row delimiter to prefix a pipe in front of {CR}{LF}


Step 3:

Export the text file

 DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].myView2 OUT  "C:\temp\myData2.csv" -f  "C:\temp\myFormatFile.xml" -T -t\^|  -S'+ @@servername
exec master..xp_cmdshell @cmd2


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s