My guess is that you will end up having to massage the data coming in and
use something other than bulk insert. unless you want to upload and then put
into the proper tables. Format files will not solve this problem, although I
do recommend using them with these types of inserts.

If there is a way, I know of none.

********************************************

No, you'll have to import it into a staging table then transform it.

Bulk insert to a work table that has varchar for that column instead of
numeric.  Clean the data, then move it to the real table.

Better yet, fix the source system.

On 7/29/08 12:20 PM, in article
25ABDDAD-6887-4AF7-8E54-C4FA3CD0D47C@microsoft.com, "MT"

Can you change the text file with an editor before you try to load
it?  We can write this in SQL, but a real text tool would be soooo
much faster.

Assuming to can't fix the source...

Do your bulk insert into a staging table; insert these columns as
varchar.  Then use a view to fix and insert into your production
table.  I would recommend keeping the staging table "as is" so that
any disreprencies and be tracked down more easily, and I would advise
against doing anything to change the text file itself, because it is
harder to reconcile if something goes wrong.

INSERT INTO SELECT OPENROWSET BULK provide you a way to bulk insert data
while performing some king of transformation without a staging table.

Regards:

Jesús López
www.solidq.com

So just to make it clear, here is a tiny example of what we're confronted with.
Here is the create table for one of the almost 500 tables we'll be suing
(I've left
out some columns so that the victims remain anonymous). See the numeric
fields?
Now imagine a delimted data file with stuff like: 123.456-

(Anyone ever read Sartre's Nausea? That's exactlly how I feel:
"His blue cotton shirt stands out joyfully against a chocolate-coloured
wall. That too
brings on the Nau­sea. The Nausea is not inside me: I feel it out there in
the wall, in
the suspenders, everywhere around me. It makes itself one with the café, I
am the
one who is within it.")

CREATE TABLE vbap(
    mandt varchar(3) NULL,
    vbeln varchar(10) NULL,
    posnr varchar(6) NULL,
    matnr varchar(18) NULL,
    matwa varchar(18) NULL,
    pmatn varchar(18) NULL,
    charg varchar(10) NULL,
    matkl varchar(9) NULL,
    arktx varchar(40) NULL,
    pstyv varchar(4) NULL,
    posar varchar(1) NULL,
    lfrel varchar(1) NULL,
    fkrel varchar(1) NULL,
    uepos varchar(6) NULL,
    grpos varchar(6) NULL,
    abgru varchar(2) NULL,
    prodh varchar(18) NULL,
    zwert money NULL,
    zmeng decimal(17, 3) NULL,
    zieme varchar(3) NULL,
    umziz decimal(6, 0) NULL,
    umzin decimal(6, 0) NULL,
    meins varchar(3) NULL,
    smeng decimal(17, 3) NULL,
    ablfz decimal(15, 3) NULL,
    abdat datetime NULL,
    absfz decimal(17, 3) NULL,
    posex varchar(6) NULL,
    kdmat varchar(35) NULL,
    kbver decimal(3, 0) NULL,
    kever decimal(3, 0) NULL,
    vkgru varchar(3) NULL,
    vkaus varchar(3) NULL,
    grkor varchar(3) NULL,
    fmeng varchar(1) NULL,
    uebtk varchar(1) NULL,
    uebto decimal(4, 1) NULL,
    untto decimal(4, 1) NULL,
    faksp varchar(2) NULL,
    atpkz varchar(1) NULL,
    rkfkf varchar(1) NULL,
    spart varchar(2) NULL,
    gsber varchar(4) NULL,
    netwr money NULL,
    waerk varchar(5) NULL,
    antlf decimal(1, 0) NULL,
    kztlf varchar(1) NULL,
    chspl varchar(1) NULL,
    kwmeng decimal(19, 3) NULL,
    lsmeng decimal(19, 3) NULL,
    kbmeng decimal(19, 3) NULL,
    klmeng decimal(19, 3) NULL,
    vrkme varchar(3) NULL,
    umvkz decimal(6, 0) NULL,
    umvkn decimal(6, 0) NULL,
    brgew decimal(19, 3) NULL,
    ntgew decimal(19, 3) NULL,
    gewei varchar(3) NULL,
    volum decimal(19, 3) NULL,
    voleh varchar(3) NULL,
    vbelv varchar(10) NULL,
    posnv varchar(6) NULL,
    vgbel varchar(10) NULL,
    vgpos varchar(6) NULL,
    voref varchar(1) NULL,
    upflu varchar(1) NULL,
    erlre varchar(1) NULL,
    lprio varchar(2) NULL,
    werks varchar(4) NULL,
    lgort varchar(4) NULL,
    vstel varchar(4) NULL,
    route varchar(6) NULL,
    stkey varchar(1) NULL,
    stdat datetime NULL,
    stlnr varchar(8) NULL,
    stpos decimal(4, 0) NULL,
    awahr varchar(3) NULL,
    erdat datetime NULL,
    ernam varchar(12) NULL,
    erzet datetime NULL,
    taxm1 varchar(1) NULL,
    taxm2 varchar(1) NULL,
    taxm3 varchar(1) NULL,
    taxm4 varchar(1) NULL,
    taxm5 varchar(1) NULL,
    taxm6 varchar(1) NULL,
    taxm7 varchar(1) NULL,
    taxm8 varchar(1) NULL,
    taxm9 varchar(1) NULL,
    vbeaf decimal(6, 2) NULL,
    vbeav decimal(6, 2) NULL,
    vgref varchar(1) NULL,
    netpr money NULL,
    kpein decimal(5, 0) NULL,
    kmein varchar(3) NULL,
    shkzg varchar(1) NULL,
    sktof varchar(1) NULL,
    mtvfp varchar(2) NULL,
    sumbd varchar(1) NULL,
    kondm varchar(2) NULL,
    ktgrm varchar(2) NULL,
    bonus varchar(2) NULL,
    provg varchar(2) NULL,
    eannr varchar(13) NULL,
    prsok varchar(1) NULL,
    bwtar varchar(10) NULL,
    bwtex varchar(1) NULL,
    xchpf varchar(1) NULL,
    xchar varchar(1) NULL,
    lfmng decimal(17, 3) NULL,
    stafo varchar(6) NULL,
    wavwr money NULL,
    kzwi1 money NULL,
    kzwi2 money NULL,
    kzwi3 money NULL,
    kzwi4 money NULL,
    kzwi5 money NULL,
    kzwi6 money NULL,
    stcur decimal(10, 5) NULL,
    aedat datetime NULL,
    ean11 varchar(18) NULL,
    fixmg varchar(1) NULL,
    prctr varchar(10) NULL,
    mvgr1 varchar(3) NULL,
    mvgr2 varchar(3) NULL,
    mvgr3 varchar(3) NULL,
    mvgr4 varchar(3) NULL,
    mvgr5 varchar(3) NULL,
    kmpmg decimal(18, 3) NULL,
    sugrd varchar(4) NULL,
    sobkz varchar(1) NULL,
    vpzuo varchar(1) NULL,
    paobjnr varchar(10) NULL,
    ps_psp_pnr varchar(24) NULL,
    aufnr varchar(12) NULL,
    vpmat varchar(18) NULL,
    vpwrk varchar(4) NULL,
    prbme varchar(3) NULL,
    umref float NULL,
    knttp varchar(1) NULL,
    kzvbr varchar(1) NULL,
    sernr varchar(8) NULL,
    objnr varchar(22) NULL,
    abgrs varchar(6) NULL,
    bedae varchar(4) NULL,
    cmpre money NULL,
    cmtfg varchar(1) NULL,
    cmpnt varchar(1) NULL,
    cmkua decimal(10, 5) NULL,
    cuobj varchar(18) NULL,
    cuobj_ch varchar(18) NULL,
    cepok varchar(1) NULL,
    koupd varchar(1) NULL,
    serail varchar(4) NULL,
    anzsn int NULL,
    nachl varchar(1) NULL,
    magrv varchar(4) NULL,
    mprok varchar(1) NULL,
    vgtyp varchar(1) NULL,
    prosa varchar(1) NULL,
    uepvw varchar(1) NULL,
    kalnr varchar(12) NULL,
    klvar varchar(4) NULL,
    sposn varchar(4) NULL,
    kowrr varchar(1) NULL,
    stadat datetime NULL,
    exart varchar(2) NULL,
    prefe varchar(1) NULL,
    knumh varchar(10) NULL,
    clint varchar(10) NULL,
    chmvs varchar(3) NULL,
    stlty varchar(1) NULL,
    stlkn varchar(8) NULL,
    stpoz varchar(8) NULL,
    stman varchar(1) NULL,
    zschl_k varchar(6) NULL,
    kalsm_k varchar(6) NULL,
    kalvar varchar(4) NULL,
    kosch varchar(18) NULL,
    upmat varchar(18) NULL,
    ukonm varchar(2) NULL,
    mfrgr varchar(8) NULL,
    plavo varchar(4) NULL,
    kannr varchar(35) NULL,
    cmpre_flt float NULL,
    abfor varchar(2) NULL,
    abges float NULL,
    wktnr varchar(10) NULL,
    wktps varchar(6) NULL,
    skopf varchar(18) NULL,
    kzbws varchar(1) NULL,
    wgru1 varchar(18) NULL,
    wgru2 varchar(18) NULL,
    knuma_pi varchar(10) NULL,
    knuma_ag varchar(10) NULL,
    kzfme varchar(1) NULL,
    lstanr varchar(1) NULL,
    techs varchar(12) NULL,
    mwsbp money NULL,
    berid varchar(10) NULL
);

Working, flexible example:
/*-- Simple example
DECLARE @staging TABLE ( col1 VARCHAR(10), col2 VARCHAR(10), col3
VARCHAR(10) )
DECLARE @target TABLE ( col1 NUMERIC(10,4), col2 NUMERIC(10,4), col3
NUMERIC(10,4) )

INSERT INTO @staging VALUES ( '123', '456.78-', '90.12' )
INSERT INTO @staging VALUES ( '3456-', '78.90', '1234.5678' )

INSERT INTO @target
SELECT
    CASE CHARINDEX( '-', col1 ) WHEN 0 THEN col1 ELSE '-' + LEFT( col1, LEN(
col1 )-1 ) END,
    CASE CHARINDEX( '-', col2 ) WHEN 0 THEN col2 ELSE '-' + LEFT( col2, LEN(
col2 )-1 ) END,
    CASE CHARINDEX( '-', col3 ) WHEN 0 THEN col3 ELSE '-' + LEFT( col3, LEN(
col3 )-1 ) END
FROM @staging

SELECT * FROM @staging
SELECT * FROM @target

SELECT *
INTO tempdb.dbo.staging
FROM @staging

!!bcp tempdb.dbo.staging out c:\temp\table1.txt -S.\sql2005 -T -c -t"|"
--BULK INSERT tempdb.dbo.staging FROM 'c:\temp\table1.txt' WITH (
FIELDTERMINATOR = '|' )

*/

USE _test
GO

-- Set up tables with NUMERICs which are already there.
IF OBJECT_ID ( 'table1' ) IS NOT NULL DROP TABLE table1
CREATE TABLE table1 ( col1 NUMERIC(10,4), col2 NUMERIC(10,4), col3
NUMERIC(10,4) )
GO

-- OK, we're gonna bulk load for table1.  We know the structure for table1,
so we can create a copy of it with varchars in tempdb.
-- Assuming you have an example text file called c:\temp\table1.txt which
contains two lines:
/*
123|456.78-|90.12
3456-|78.90|1234.5678
*/
IF OBJECT_ID( 'dbo.usp_create_temp_table_copy' ) IS NOT NULL DROP PROC
dbo.usp_create_temp_table_copy
GO
CREATE PROC dbo.usp_create_temp_table_copy

    @table_name        SYSNAME,
    @debug            BIT = 0

AS

DECLARE @sql VARCHAR(MAX)

SET @sql = 'USE tempdb
IF OBJECT_ID ( ''@table_name'' ) IS NOT NULL DROP TABLE @table_name

CREATE TABLE tempdb.dbo.@table_name
(
'

SET @sql = REPLACE( @sql, '@table_name', @table_name )

SELECT @sql = @sql + name + ' VARCHAR(50),'
FROM sys.columns
WHERE object_id = OBJECT_ID( @table_name )

SELECT @sql = @sql + '
)'

IF @debug = 1
    SELECT @sql
ELSE
    EXEC(@sql)
GO

IF OBJECT_ID( 'dbo.usp_do_bulk_insert' ) IS NOT NULL DROP PROC
dbo.usp_do_bulk_insert
GO
CREATE PROC dbo.usp_do_bulk_insert

    @table_name    SYSNAME,
    @filename    VARCHAR(500),
    @debug        BIT = 0
AS

DECLARE @sql VARCHAR(MAX)

-- Create the BULK INSERT
SET @sql = 'BULK INSERT tempdb.dbo.@table_name FROM ''@filename'' WITH (
FIELDTERMINATOR = ''|'' )'
SET @sql = REPLACE( @sql, '@table_name', @table_name )
SET @sql = REPLACE( @sql, '@filename', @filename )

IF @debug = 1
    SELECT @sql
ELSE
    EXEC(@sql)
GO

IF OBJECT_ID( 'dbo.usp_stage_to_main' ) IS NOT NULL DROP PROC
dbo.usp_stage_to_main
GO
CREATE PROC dbo.usp_stage_to_main

    @table_name        SYSNAME,
    @debug            BIT = 0

AS

DECLARE @sql VARCHAR(MAX)
DECLARE @column_list1 VARCHAR(MAX)
DECLARE @column_list2 VARCHAR(MAX)
DECLARE @case VARCHAR(MAX)

SET @sql = 'INSERT INTO dbo.@table_name ( @column_list1 )
SELECT @column_list2
FROM tempdb.dbo.@table_name'

SET @case = 'CASE CHARINDEX( ''-'', @name ) WHEN 0 THEN @name ELSE ''-'' +
LEFT( @name, LEN( @name )-1 ) END AS @name'

SELECT
    @column_list1 = ISNULL( @column_list1 + ', ', '' ) + name,
    @column_list2 = ISNULL( @column_list2 + ',
    ', '' ) + REPLACE( @case, '@name', name )
FROM sys.columns
WHERE object_id = OBJECT_ID( @table_name )

SET @sql = REPLACE( @sql, '@table_name', @table_name )
SET @sql = REPLACE( @sql, '@column_list1', @column_list1 )
SET @sql = REPLACE( @sql, '@column_list2', @column_list2 )

IF @debug = 1
    SELECT @sql
ELSE
    EXEC(@sql)
GO

EXEC dbo.usp_create_temp_table_copy 'table1'
EXEC dbo.usp_do_bulk_insert 'table1', 'c:\temp\table1.txt'
EXEC dbo.usp_stage_to_main 'table1'

SELECT *
FROM dbo.table1