Read CSV and perform SQL in SQL Server Management Studio

Question

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?

Answer 1

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:

1. The file should be converted to UTF-16.
2. 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" 

Answer 2

A simple and easy way to do this would be to import the csv into a table, then manipulate/view. If you'd prefer a graphical method instead of using tsql; right click your database and select Tasks, and then select Import Data.

Answer 3

May be SSMS: How to import (Copy/Paste) data from excel can help (If you don't want to use BULK INSERT or don't have permissions for it).

Answer 4

You can refer to below link for how to read csv file in SQL Server:

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2015/02/09/t-sql-read-csv-files-using-openrowset/

Based on that you will be able to work with your csv data as a table and then you can create the update query as required.