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


SELECT * 
--into myview2
FROM 
OPENROWSET (      
  'Microsoft.ACE.OLEDB.12.0',
  '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


 
Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s