Sql server: Bulk insert. Change from comma to dot

Question

Hi
I need to load a csv file using bulk insert.
In the csv file the numeric fields have comma instead of dot. (So i m seeing it as a varchar not a numeric)
How can i change this during bulk insert.
What should i use in the file format?
Thanks

Answer 1

Hi @Rals ,

You can try along the following conceptual example.

SQL

INSERT INTO dbo.destinationTable (ID, decimalColumn)  
SELECT ID, TRY_CAST(REPLACE(column1, ',','.') as DECIMAL(10,2))   
FROM OPENROWSET(  
   BULK 'E:\Temp\input.csv',  
   FORMATFILE = 'e:\Temp\formatFile...'  
) AS t;  

Answer 2

Hi thanks, but i have 7csv, with around 100 columns each…i need to find something to use it for all

Answer 3

Unfortunately, I was not very successful. My idea was to use the bcp command-prompt utility, because bcp has the option -R which means that regional settings should be respected.

But, alas, it does not seem to apply when reading data for numeric/decimal, but bcp wants a decimal point also when I give -R. I was able to load data, when I changed the data type to money. money corresponds to decimal(18,4), and your data seems to have more decimals than just four, so I don't think this is a workable way for you.

However, there may be an even more fundamental problem. In your sample file above, there are several entries with two commas in them. I don't know if you were sloppy when you composed the sample, of if this reflects the actual data. (When trying to load the file, I assumed the former, and just removed the extraneous commas.) But it could be a good idea that you investigate this first before we continue the conversation.

Answer 4

The only other option I can see to a replace() for every column, is to massage the files before you load them. That is, replace all commas with period. If you are only doing this once, you could do this a in a text editor. Else you would have to write a short program in the language of your choice for the task.

One caveat though: if there is string data with commas, those commas will also be changed into periods.

If you want to do everything inside SQL Server, I am afraid that the original solution suggested by Yithak is your only option. Although you could generate the SELECT with all the replace() calls with help of dynamic SQL.

Answer 5

You can try to load data into stage tables for these files as string type first and merge these tables/data in a combined table with your desire data type.