Decimal point and comma

[KiP.Kolodziejczyk]

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

[Dan Guzman]

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/

<Ki***************@gmail.comwrote in message
news:ad**********************************@d1g2000h sg.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

[Erland Sommarskog]

(Ki***************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx