How to import .CSV and Excel files into Azure SQL Server

Question

I have used database import/export to move in and out data out of SQL Server with no problems.

After 3 hours of searching, I have not seen the SQL server Import/Export equivalent for an Azure SQL server database.

Is there a simple tool/utility from MS or other that can import/export data to Sql Azure?

Answer 1

I made a simplified workaround. You can import your excel or csv into Local sql server using import task and out of this import table, you can create a Sql script with insert data statement. Run this script on your Azure Database. If you dont have any sql server installed, just download a simple sqlitebrowser, you can do the same using sqlite. search for SQLiteDatabaseBrowserPortable

Answer 2

You could use the BCP command line utility. https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-with-bcp/

Answer 3

BCP is the way to go.

BCP <databasename>.dbo.<tablename> IN <localfolder>\<filename>.txt -S <servername> -d <database> -U <username>@<servername> -P <password> -q -c -C -t ;

The -C enables you to use UTF-8 as needed for cases with special chars (like æøå).

Answer 4

I always use BULK. However, as the server of Azure obviously located in somewhere else other than your pc, you need to modify it a bit:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
      LOCATION = 'https://xxxxxxxx.blob.core.windows.net/miladcontainer'
      );

The above code first creates a virtual DATA SOURCE, and then you can use BULK INSERT to upload from it with the following code:

BULK INSERT clinical_survey
FROM 'clinical_survey.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage',
  FORMAT = 'CSV');

Steps should be taken before executing the code is as follows:

1- Create a blob storage container (search at Azure and create a public one)
2- When asked, choose BLOB PUBLIC STORAGE
3- Upload your CSV file in Blob that was just created
4- Execute the above codes separately

Answer 5

As previously stated you can use BCP, but remember that Azure SQL also has BCP built in which you can use.

Here is example on how to do it https://marczak.io/posts/azure-loading-csv-to-sql/

Alternatively you can easily load data from blob storage into database using Azure data Factory, you can example find here: https://azure4everyone.com/posts/2019/07/data-factory-intro/

Answer 6

Azure SQL Database supports import and export of .bacpac/.dacpac files. Please see the following article for more details:

• https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/
• https://azure.microsoft.com/en-us/documentation/articles/sql-database-export/
• https://azure.microsoft.com/en-us/documentation/articles/sql-database-import/

Answer 7

You can go to "New" button on top left and click "Dataset" - local File to import any file that you would want into Azure machine. That is the best and easiest way to import.