[dba-SQLServer] BCP import spec

I have a situation where I have to import into SQL Server millions of records, fixed width, same 
format, different data, over and over again.


The format is given to me in a spreadsheet looking like this:

FROM	TO	LENGTH	MODE	             DESCRIPTION
1	40	40	C	FULL NAME (PARSED NAME IN POS 421-482)
41	80	40	C	COMPANY
81	120	40	C	ADDRESS 1
121	160	40	C	ADDRESS 2
161	190	30	C	CITY
191	192	2	C	STATE
193	197	5	C	ZIP
198	198	1	C	ZIP 4 -

MANY more columns.

I need to turn import this into a table.  I have done this once, brute force, using the import wizard.

The bottom line is that I now have an existing table in SQL Server that "fits" the spec.  The fields 
are all nvarchar() with the correct number of characters in each field.  One problem is that I do 
NOT want the trailing spaces which pad the data to the fixed width.  I do have a udf which strips 
off padding but it is going to be a PITA to apply that to all of the fields for millions of records 
(though I am capable of doing that if necessary).

Is there any way to tell SQL Server to import the data into this table.  Preferably in a stored 
procedure that I can execute from my existing C# code base which knows how to execute stored procedures.

I have to do this many times in the future, and I do NOT want to do this manually every time. 
Somebody mentioned on time doing something with the wizard and then peeking at the code that the 
wizard generated.  I don't know how to do that but if I could, I could then cut that into a SP and 
execute the SP as needed.

Something like that.

Anybody have any ideas?

TIA

-- 
John W. Colby
www.ColbyConsulting.com