3

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?

  • Look at BULK INSERT and the options there. It's pretty simple actually. – Pimp Juice IT Jul 7 '16 at 14:27
  • Wouldn't you be better off just opening this in Excel (or your chosen spreadsheet provider) and just updating it using filters? Tip: Selecting multiple cells, typing a value and pressing Ctrl+Enter enters the same value in all cells. – wBob Jul 8 '16 at 8:40
3

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.

|improve this answer|||||
3

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" 
|improve this answer|||||
1

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).

|improve this answer|||||
New contributor
Denis is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
-1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.