Add csv file to Synapse Studio

Question

Add csv file to Synapse Studio

Jake Watson 0

I just want to add a csv file to my azure synapse workspace. Seems simple enough, but microsoft makes it not so.

I have the csv file loaded into a container, and it's accessible from my linked services, but the headers are not properly formatted. I'd like to add this as a table that I can query from in synapse and join other tables.

I've tried going through the data ingest copy tool, but it won't connect.

Screenshot 2024-04-10 at 11.24.11 AM.png

Screenshot 2024-04-10 at 11.24.38 AM.png

Screenshot 2024-04-10 at 11.24.02 AM.png

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-12T09:34:29.6266667+00:00

@Jake Watson We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jake Watson 0 Reputation points

2024-04-15T14:22:29.2833333+00:00

not sure how to mark your answer as correct
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-15T17:21:20.56+00:00

@Jake Watson Now you have the option to 'accept answer' below. If this answers your question, please click 'Accept Answer' and 'Yes' if you found it helpful. If you have any more questions, feel free to ask.
Jake Watson 0 Reputation points

2024-04-15T19:14:26.75+00:00

What do I put in MyCredential?

1 answer

Your answer

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-12T09:34:29.6266667+00:00

@Jake Watson We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jake Watson 0 Reputation points

2024-04-15T14:22:29.2833333+00:00

not sure how to mark your answer as correct
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-15T17:21:20.56+00:00

@Jake Watson Now you have the option to 'accept answer' below. If this answers your question, please click 'Accept Answer' and 'Yes' if you found it helpful. If you have any more questions, feel free to ask.
Jake Watson 0 Reputation points

2024-04-15T19:14:26.75+00:00

What do I put in MyCredential?

Answer 1

Smaran Thoomu 24,110 Microsoft External Staff Moderator

Hi @Jake Watson

Thanks for the question and using MS Q&A platform.

To create a table from a CSV file in Synapse Analytics, you can use the following steps:

Upload the CSV file to an Azure Blob Storage container. You can do this using the Azure portal or Azure Storage Explorer.
Create an external data source in Synapse Analytics that points to the Azure Blob Storage container where the CSV file is located. You can do this using the following T-SQL command:

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://mystorageaccount.blob.core.windows.net/mycontainer',
    CREDENTIAL = MyCredential
);

In this example, MyDataSource is the name of the external data source, https://mystorageaccount.blob.core.windows.net/mycontainer is the URL of the Azure Blob Storage container where the CSV file is located, and MyCredential is the name of the credential that provides access to the container.

Create an external file format in Synapse Analytics that describes the format of the CSV file. You can do this using the following T-SQL command:

CREATE EXTERNAL FILE FORMAT MyFileFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2
    )
);

In this example, MyFileFormat is the name of the external file format, and the FORMAT_OPTIONS specify the delimiter, string delimiter, and whether the first row contains headers.

Create an external table in Synapse Analytics that references the CSV file. You can do this using the following T-SQL command:

CREATE EXTERNAL TABLE MyTable
(
    Column1 INT,
    Column2 VARCHAR(50),
    Column3 DATE
)
WITH (
    LOCATION = '/path/to/myfile.csv',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);

In this example, MyTable is the name of the external table, and the columns are defined based on the structure of the CSV file. The LOCATION specifies the path to the CSV file within the Azure Blob Storage container, and the DATA_SOURCE and FILE_FORMAT reference the external data source.

I hope this helps! Let me know if you have any further questions.

Jake Watson 0 Reputation points

2024-04-15T19:13:42.04+00:00

What do I add as MyCredential?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-16T08:56:53.4833333+00:00
Jake Watson In the CREATE EXTERNAL DATA SOURCE command, MyCredential is the name of the database scoped credential that you created to provide access to the Azure Blob Storage container.

To create a database scoped credential, you can use the following T-SQL command:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<your-shared-access-signature>';

In this example, MyCredential is the name of the database scoped credential, and <your-shared-access-signature> is the shared access signature that you generated for the Azure Blob Storage container.

You can generate a shared access signature for the container in the Azure portal or using Azure Storage Explorer. The shared access signature should have at least the read permission to allow Synapse Analytics to access the CSV file.

I hope this helps! Let me know if you have any further questions.
Jake Watson 0 Reputation points

2024-04-16T18:56:15.42+00:00

Receiving an error. Is there not an easier way to add csv files to Azure Synapse Studio? This seems very complicated.
Jake Watson 0 Reputation points

2024-04-16T18:57:43.45+00:00

Error here
Jake Watson 0 Reputation points

2024-04-16T18:58:38.81+00:00

Does not seem to want to attach my screenshot:

Started executing query at Line 1

CREATE DATABASE SCOPED CREDENTIAL is not supported in master database. Visit this article to learn more about this error

Total execution time: 00:00:00.401
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-04-17T08:35:15.6766667+00:00
Jake Watson I'm sorry to hear that you're encountering an error. The error message indicates that you are trying to create a database scoped credential in the master database, which is not supported in Synapse Analytics.

To create a database scoped credential in Synapse Analytics, you need to create it in the user database where you want to use it. You can use the following T-SQL command to switch to the user database:

USE <your-user-database>;

Replace <your-user-database> with the name of the user database where you want to create the database scoped credential.

Once you have switched to the user database, you can create the database scoped credential using the following T-SQL command:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<your-shared-access-signature>';

In this example, MyCredential is the name of the database scoped credential, and <your-shared-access-signature> is the shared access signature that you generated for the Azure Blob Storage container.

You can generate a shared access signature for the container in the Azure portal or using Azure Storage Explorer. The shared access signature should have at least the read permission to allow Synapse Analytics to access the CSV file.

Regarding your question about an easier way to add CSV files to Azure Synapse Studio, you can also use Azure Data Factory to copy data from a CSV file to a Synapse Analytics table. This approach may be simpler if you are not familiar with T-SQL commands.

I hope this helps! Let me know if you have any further questions.

Share via

Add csv file to Synapse Studio

1 answer

Your answer