CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2bulkinsert
imanebz
imanebz

Reputation: 79

How to trim spaces when using a bulk insert?

I'm using a bulk insert to import an rpt file but I'm getting these errors :

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I think this is due to spaces between file columns, is there any way I can trim these spaces?

Thanks!

Upvotes: 3

Views: 4838

Answers (1)

Alex
Alex

Reputation: 21766

If there are only spaces in your file, your file format is fixed with and you will need a format file to load it.

SELECT *
  FROM  OPENROWSET(BULK  'C:\Users\Admin\Desktop\Extractions\F0005.rpt',
  FORMATFILE='C:\myTestSkipField.fmt'  
   ) AS t1;

I have included an example .fmt file below mapping the first few fields in your file using EmptyField columns to map the space. You could just omit this, as it looks like you will have to trim your fields anyway using LTRIM(TRIM([column])) 

9.0
8
1     SQLCHAR     0     4     ""      1     DRSY        SQL_Latin1_General_CP1_CI_AS
2     SQLCHAR     0     1     ""      2     Empty1      SQL_Latin1_General_CP1_CI_AS
3     SQLCHAR     0     4     ""      3     DRTT        SQL_Latin1_General_CP1_CI_AS
4     SQLCHAR     0     10     ""     4     DRKY        SQL_Latin1_General_CP1_CI_AS
5     SQLCHAR     0     1     ""      5     Empty2      SQL_Latin1_General_CP1_CI_AS  
6     SQLCHAR     0     30     ""     6     DRDL01      SQL_Latin1_General_CP1_CI_AS
7     SQLCHAR     0     1     ""      7     Empty3      SQL_Latin1_General_CP1_CI_AS
8     SQLCHAR     0     1024    "\n"  8     Remainder   SQL_Latin1_General_CP1_CI_AS

From the Microsoft documentation, this is the file format of a .fmt file:

enter image description here

Upvotes: 1

Related Questions