Insert a csv file into Azure SQL from an Azure Blob

Question

I have an asp.net (c#) site in Azure and an accompanying Azure SQL database. I need to upload 1gb+ csv files and get them parsed and into my Azure SQL database. I can get the csv files into an Azure blob. I now have the URL to the blob (newGuid.txt).

Either from SQL or from the web app, how do I parse this csv and get it inserted into my Azure SQL database? (the csv has 36 columns if that helps)

I can't figure out how to reference the URL to use SqlBlukCopy. I initially thought I would BULK INSERT but Azure SQL doesn't allow that. I can't download the files locally and use BCP for each one.

instead of trying a sqlbulk copy I would suggest trying to bulk insert the data into the table using XML you can parse the data into a datatable then from there create a stored procedure which would comprise of a temp table and insert into the temp table the data in xml format.. I do this all the time to get around having to explicitly use sqlBulkInserts — MethodMan
– MethodMan, Commented Apr 14, 2015 at 17:15

Leo von Barbarosa · Accepted Answer · 2019-10-01 20:48:15Z

2

I agree this is an old question, but as gets traction, here is the answer:

You can use Azure Data Factory to move data from Blob Storage (and many more data sources) to Azure SQL Database (and many other data sinks).

There is nothing simpler today to achieve your goal. Here are some tutorials:

You can configure a data pipeline to just run once. Or run on specific time, or run on a schedule. You can make it copy single file, or using file pattern to get more files, etc.

edited Oct 1, 2019 at 20:48

Leo von Barbarosa

7658 silver badges23 bronze badges

answered Sep 21, 2016 at 20:31

astaykov

30.8k3 gold badges72 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jos Verlinde · Accepted Answer · 2023-03-16 17:00:45Z

0

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16#f-import-data-from-a-file-in-azure-blob-storage

answered Mar 16, 2023 at 17:00

Jos Verlinde

1,72616 silver badges29 bronze badges

Collectives™ on Stack Overflow

Insert a csv file into Azure SQL from an Azure Blob

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related