a bulk insert question

I was given a text file to use to insert bulk-ly into a table.

The file is made up in a way that at the end of each line it has a very very long line of spaces (i.e a string). The last column in the table is supposed to accepted an nvarchar no bigger than 3. I know there's a possibility to increase the size of the nvarchar, but I want to avoid putting the string of spaces in the table.

How can I over come the problem with the file? obviously deleting each row of spaces is too much trouble for a file that has over 40000 rows...

I hope you can help me.

hi,
you can insert bulk insert the it first to a temporary table with the last column has a bigger size then have a SP that cleanse the data and insert it to the destination table.

- clintz|||

If it is just leading blanks then you need not to worry..

Instead of BULK INSERT use the OPENROWSET..

It will automatically remove the leading blanks..

SELECT

*

FROM

OPENROWSET

(

'MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\','SELECT * FROM Data.csv'

);

|||

I often consider it a good practice to first load data to a 'staging' table, a table with no constraints, indexes, etc.

Then after the data has been loaded, it is easy to have other procedures that 'cleanse' or conform the data before loading into the destination table. I can identify exceptions and set them aside for further inspection, I can 'transform' data into whatever form is required by the database, for example, 0 and 1 may be stored as 'M' and 'F' in the database (or vice-versa.)

I never assume that imported data will always meet the requirements of the database.