• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

Need to import a CSV file into an existing Azure SQL Server table

I have Visual Studio Community 2015.
I have an existing table with the correct columns, matching in order and data type the CSV file...
I was expecting something like a right click, import.   Seems like such a basic functionality.
What do you recommend?
0
Francois Koutchouk
Asked:
Francois Koutchouk
  • 3
  • 2
3 Solutions
 
lcohanDatabase AnalystCommented:
0
 
lcohanDatabase AnalystCommented:
Or if you can run a query against the database table you could use a process like below - just adjust table name/definition and name/path to actual CSV file. You can test this on a local SQL as well prior to running it against live cloud DB table:


--IMPORT
CREATE TABLE MySample
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

--Create CSV file in drive C: with name Sample-Output.csv.txt with following content. The location of the file is C:\Sample-Output.csv.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202


--Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT MySample
FROM 'c:\Sample-Output.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM MySample
GO
--From here on you can do anything you want with data imported via T-SQL Ccommands

--Drop the table to clean up database.
--DROP TABLE MySample
GO
0
 
Francois KoutchoukCTOAuthor Commented:
Whoops except I get
Msg 40526, Level 16, State 1, Line 8
'BULK' rowset provider not supported in this version of SQL Server when connected to my Azure SQL Server.
0
 
Francois KoutchoukCTOAuthor Commented:
In SQLyog front-end to MySQL, I just right-click on my RDS instance, select the CSV file and done...
0
 
lcohanDatabase AnalystCommented:
Right...arghhh...well in that case BCP is the one that can save you and you should run a command like:

bcp database_name.schema.table in C:\import.csv -c -U username @servername -S servername.database.windows.net -P password -t

I added -t syntax to specify that it was comma delimited assuming that is your CSV delimiter and for more switches please see BCP command.

https://azure.microsoft.com/en-us/blog/bcp-and-sql-azure/
https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-with-bcp/
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now