SQL Bulk import

Question

I have a simple bulk import script which is working fine

BULK
INSERT tablename
FROM 'd:\www\ftp\test.csv'
  WITH
  (
  KEEPIDENTITY,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  ERRORFILE = 'd:\www\ftp\test7.log' 
  )
GO

Now in the test.csv file there are values like:

Bike, Red, "100,00", Blue
Step, Blue, "60,40", Black

My question is how can I handle the "100,00" ("xxx,xx") value in the script?

Thanks!

see http://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server — Rich Andrews, Oct 20 '13 at 16:44

M.Ali · Answer 1 · 2013-10-20T18:38:17.207

Insert the records into a Temp Table and then do something like this.. to get the result and then you can add the rows into you desired Table.This is your CSV File

CSV_FILE

Your can do the following to insert it into a temp table .

Create Table #Temp
(
Product NVARCHAR(50),
Colour NVARCHAR(50),
Number NVARCHAR(50),
Spare NVARCHAR(50),
Colour1 NVARCHAR(50)
)
GO

BULK
INSERT #Temp
FROM 'C:\Users\ali\Desktop\test.csv'
  WITH
  (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
  )
GO

UPDATE #Temp
SET Number = Number + ',' + Spare

ALTER TABLE #temp
DROP Column Spare

UPDATE #Temp
SET Number = REPLACE(Number, '"', '') 

SELECT * FROM #Temp

Result Set

enter image description here

SELECT * INTO [Destination_Table] FROM #Temp
DROP TABLE #Temp

SQL Bulk import

1 Answers1