Read CSV and perform SQL in SQL Server Management Studio

Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line?

Example

data.csv

"column1","column2,"column3"
"column1","column2,"column3"
"column1","column2,"column3"
"column1","column2,"column3"
"column1","column2,"column3"


I want to run a SQL update based on the data in each line such as

UPDATE [data].[orders] set order_number = $column1, something_else = $column2 where order = $column3

Is this possible to do in SQL Server Management Studio?

You may use BULK INSERT for that:

CREATE TABLE #orders (
  Column1 int,
  Column2 nvarchar(max),  
  Column3 datetimeoffset
)

BULK INSERT #orders
FROM 'X:\orders.csv'
WITH
(
  FIRSTROW = 1,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

If you exported the CSV from SQL Management Studio then some preparations are needed:

• The file should be converted to UTF-16.

• All NULLs should be replaced with empty strings.

Here is a Powershell script for that:

Get-Content .\orders_raw.csv -Encoding UTF8 | % { $_ -replace "NULL","" } | Out-File ".\orders.csv"

StackExchange Database Administrators Q#143215, answer score: 10