I´m having trouble with bulk insert a file that contains a field in data type text, and it holds enters and the delimiter for the fields are pipes "|" and the row terminator is "|\n"
I get an error of truncation for the fields next to the text field. I think that the bulk insert thinks that the enters in the second field are the next rows.
DECLARE @sql varchar(2000) PRINT 'xyz table' SET @sql = 'BULK INSERT xyz FROM ''\\' + @@servername + '\Import\xyz.txt'' WITH ( DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''|\n'', TABLOCK, ROWS_PER_BATCH = 2000, MAXERRORS = 1000 ) ' PRINT @sql EXECUTE (@sql) PRINT '' GO
This is the data example
467507**|**08-20-09\ [8:55:03 AM] *** [email protected] is Not Available [Ext. away]\(CR) [9:00:57 AM] *** [email protected] is Online [Online]\(CR) [9:01:00 AM] <jeffp> Howdy. Time slip update game! Update TS#467493 & 467507 with a (CR)comment and see if you win! [9:01:30 AM] *** [email protected] is Away [Away]\ \(CR) 08-18-09\(CR) [10:13:15 AM] *** [email protected] is Online [Online]\(CR) [10:13:59 AM] <jeffp> Howdy; welcome back from lunch.. Just pinging you for an update in TS#467493 & 467507. Since 467493 is pri9, want a stock e-mail to go out to the customer to get them moving?\(CR) [10:14:47 AM] <[email protected]> thats ok i got it\(CR) [10:14:53 AM] <jeffp> Aiight.**|**2009-08-18 00:00:00**|**2009-08-20 00:00:00**|**JDP**|**JDP**|**
The table schema is:
create table xyz ( xyz_id VARCHAR(200), --INT TO VARCHAR notes text, create_date varchar(32), --DATETIME create_user varchar(12), modify_date varchar(32), --DATETIME modify_user varchar(12) )
As you can see (well, not can see) there are enters (marked with CR) and the bulk insert is confusing them with a new row.
Any help will be appreciated.