Import Text file with and Extral Column DelimiterPosted: November 24, 2014
I ran into a text file (generated from informix(?) ) with an extral pipe (|) appended at the row end. We need to manipulate the importing procees from import/export wizard to skip the last pipe.
You can play with the an extra column from the source and ingnore it from target to do it.
However, I found a easy way :
You can add a pipe (|) directly to Row delimiter (both header and regular Columns) and it should work.
If you are using bcp or bulk insert, you can define both column and row delimiter to import the data to skip the list pipe.
Here is the sample code for bco and bulk insert.
--- Bulk Insert BULK INSERT [TestFullMode].[dbo].[InfoData2] FROM 'C:\temp\myDataPipetail.txt' WITH ( FIELDTERMINATOR ='|', ROWTERMINATOR ='|\n', FirstRow=2 ); --bcp DECLARE @cmd NVARCHAR(4000) set @cmd = 'BCP [TestFullMode].[dbo].[InfoData2] IN C:\temp\myDataPipetail.txt -c -T -t\^| -F 2 -r ^|\n -S'+ @@servername exec master..xp_cmdshell @cmd