how to import an Excel xlsx file into Azure Data Studio without converting it to a csv

Question

hello

I am trying to import an Excel Workbook(xlsx file) into Azure Data Studio.

I have installed the SQL server importer which could only import txt or csv file but not the xlsx file.

I would appreciate it if anyone could help with this.

Thank you.

Answer 1

Good day @Oluwafemi Ajibola and welcome to the Microsoft QnA forums,

I think that there is a few misunderstandings (or simply mistake), which must be clarified before we find a solution which fits you need.

I am trying to import an Excel Workbook(xlsx file) into Azure Data Studio.

You cannot 🙃

Azure Data Studio is not a database but a tool to manage databases servers like SQL Server, Azure SQL Database, Azure Managed instance, Cosmos DB, and so on. It cannot store data so you cannot import data to it.

You must clarify what is your target database in order to have the discussion.

For the sake of the discussion, I will assume that you meant to migrate the data from Excel to Azure SQL Database.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can't read Excel files directly.

The above text, which @Tech-Hyd-1989 mentioned (a copy) from the Microsoft document, includes a link to the document. This is a VERY GOOD tutorial if this is your needs (migrate data from Excel to SQL Server or Azure SQL). This sentence is from the section about using Azure Data Factory, but it presents multiple other options.

Note that Azure Data Factory (ADF) is totally different tool from Azure Data Studio (ADS). ADF is an Azure service while ADS is a desktop application which is by the way, open source and have versions for all common operating system (it based on Visual studio Code which is also Open source).

In a way, you can think about Azure Data studio as the Azure equivalent service of the SQL Server Integration Service (SSIS) desktop tool.

The document does not speak about import to Azure Data studio and not even about using Azure Data studio for the task, but about other tools.

You can use Azure Data Studio to import data to SQL Server or Azure SQL, if this what you want 😀. As I mentioned, ADS is open source and It has tens of thousands of extensions, some of which were developed by Microsoft's teams and most of which were developed by people from the community. All you need is to Install the SQL Server Import extension. It supports flat files like .txt and .csv.

https://learn.microsoft.com/sql/azure-data-studio/extensions/sql-server-import-extension?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

My 2 cents:

• Even so you can use Azure Data Studio wizard (extension) it's not the simplest option. The document about import data to SQL Server or Azure SQL is the best starting point to read.
• If you want to use flat file, then my preferred option is using BCP (much simpler for most cases for flat file than Azure Data Factory, BULK INSERT, Import Flat File Wizard...)
• Remember that SQL Server Integration Services (SSIS) is a local app which requires a license!
• OPENROWSET is not relevant to you, since Azure SQL Database only supports reading from Azure Blob Storage.

I hope this help a bit

Have a great day🙂

Answer 2

Hi Oluwafemi Ajibola

Good day!

As described in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can't read Excel files directly. Excel files have a proprietary format and are not simple delimited files. Azure Data Factory does not have a direct option to import Excel files, eg you cannot create a Linked Service to an Excel file and read it easily. Your options are described in the below blogs

https://stackoverflow.com/questions/52514153/how-to-read-files-with-xlsx-and-xls-extension-in-azure-data-factory

I recommend looking at this third party article as well that described the problem well. https://www.mssqltips.com/sqlservertip/6909/import-data-from-excel-to-azure-sql-database-azure-data-factory/

To start learning how to copy data with Azure data factory, see the following topics:

• Move data by using Copy Activity
• Tutorial: Create a pipeline with Copy Activity using Azure portal

Hope this helps!
If this does answer your question, please accept it as the answer and upvote as a token of appreciation.