Prev: Stop hotlinking to a specific file & keeping a counter
Next: OLEDB configuration when deploying multiple SSIS packages
From: KiP.Kolodziejczyk on 30 Jun 2008 05:56
I 'd like to insert (via "bulk insert") between 10-20 files (about
300MB each) into one table.
The problem is that in input files decimal point is written as "," and
without leading zeros (i.e.: ",45"), and sever uses ".".

So I 've got an "type mismatch" error.
I can't re-build input file, as well as I can't change configuration
of mssql sever.
Is there any option to force the server to convert decimal point while
importing?
Maybe "file format" option in bulk insert, or another way of
import...?

Piotr

From: Dan Guzman on 30 Jun 2008 08:43
> The problem is that in input files decimal point is written as "," and
> without leading zeros (i.e.: ",45"), and sever uses ".".

Try SSIS (or DTS) instead BULK INSERT so that you have the flexibility to
fix the data during the import process. If you must use BCP, you'll need to
import into a staging table with the decimal column defined as char/varchar
so that you can UPDATE the decimal value and INSERT into the target table.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<KiP.Kolodziejczyk(a)gmail.com> wrote in message
news:adba0123-d336-4477-bef5-522f29314dd1(a)d1g2000hsg.googlegroups.com...
>I 'd like to insert (via "bulk insert") between 10-20 files (about
> 300MB each) into one table.
> The problem is that in input files decimal point is written as "," and
> without leading zeros (i.e.: ",45"), and sever uses ".".
>
> So I 've got an "type mismatch" error.
> I can't re-build input file, as well as I can't change configuration
> of mssql sever.
> Is there any option to force the server to convert decimal point while
> importing?
> Maybe "file format" option in bulk insert, or another way of
> import...?
>
> Piotr
>

From: Erland Sommarskog on 30 Jun 2008 18:26
(KiP.Kolodziejczyk(a)gmail.com) writes:
> I 'd like to insert (via "bulk insert") between 10-20 files (about
> 300MB each) into one table.
> The problem is that in input files decimal point is written as "," and
> without leading zeros (i.e.: ",45"), and sever uses ".".
>
> So I 've got an "type mismatch" error.
> I can't re-build input file, as well as I can't change configuration
> of mssql sever.
> Is there any option to force the server to convert decimal point while
> importing?
> Maybe "file format" option in bulk insert, or another way of
> import...?

With BULK INSERT you are out of luck. With BCP, it is possible that the
-R option can help you. This option causes BCP to honour regional settings.
You would need to have regional settings where , is the delimiter, but
if you are located in Poland, I guess you have.

I need to add the discalimer that I am not sure that this will work. I have
had problems with the -R options to work in the past, as I recall. And
if you have a mix of decimal comma and decimal point, you really have a
problem.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: Stop hotlinking to a specific file & keeping a counter
Next: OLEDB configuration when deploying multiple SSIS packages