How to trim spaces when using a bulk insert?

Question

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!

I think rpt stands for a report output file and this output is looking like a Fixed Width Data or System Data Format (SDF) data. Is there a chance that you can use C# for this job? It would be easy with C# (or any other language) to parse the column names from the first line, and determine widths fromdashes on second line to generate a datatable or a reader that you can use to insert data into SQL server (probably using SqlBulkCopy class). I have never found SQL server itself to be intuitive in importing text files. — Cetin Basoz

Alex Alex · Accepted Answer · 2015-11-26T15:15:46

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:

How to trim spaces when using a bulk insert?

1 Answers