Importing csv file with Json and data with text qualifier and column delimiter

Question

Importing csv file with Json and data with text qualifier and column delimiter

VickyD 86

Hi Team,

I have a CSV with these details.

Text qualifier: "

Column delimiter: ,

Row delimiter: CRLF

But as attached, I have 2 columns in csv file, which has json and data with text qualifier and column delimiter.

I want to store this file as it is in my database table. No need to parse json data. I tried different ways. No luck.

Can someone put some thoughts on this.

ErroredLine.txt

Thanks,

Vikram

Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-02T07:16:43.79+00:00
Hi @VickyD

Thank you for reaching out to the community forum with your query.

To import the CSV file with JSON data and text qualifier and column delimiter into your database table, you can use Azure Data Factory (ADF) to transform and load the data. Here are the high-level steps to accomplish this:

Create a new ADF pipeline: In the Azure portal, create a new ADF pipeline and add a "Copy Data" activity to the pipeline.

Configure the source dataset: In the "Copy Data" activity, configure the source dataset to point to the CSV file. Set the file format to "DelimitedText" and set the text qualifier to double quotes ("). Set the column delimiter to comma (,) and the row delimiter to CRLF.

Configure the sink dataset: Configure the sink dataset to point to your database table. Set the table schema and column mappings to match the structure of the CSV file.

Use a mapping data flow: In the "Copy Data" activity, use a mapping data flow to transform the JSON data column into a string column. To do this, add a "Derived Column" transformation to the data flow and use the "toString()" function to convert the JSON data column to a string column.

Run the pipeline: Save and run the pipeline to load the data from the CSV file into your database table.

I hope this helps! Let me know if you have any further questions.
VickyD 86 Reputation points

2024-04-02T08:54:56.2+00:00

how to do this ?

Use a mapping data flow: In the "Copy Data" activity, use a mapping data flow to transform the JSON data column into a string column. To do this, add a "Derived Column" transformation to the data flow and use the "toString()" function to convert the JSON data column to a string column.
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-03T09:49:32.3866667+00:00

Hi @VickyD

I apologize for the previous information about using Azure Data Factory (ADF) for this task. It seems I misunderstood your requirement.

Since you want to import the CSV file directly into your database table without parsing the JSON data, ADF with data transformation wouldn't be the most efficient approach. Instead, you can use other tools or programming languages to achieve this task.

I think you have one option is to use SQL Server Integration Services (SSIS) to import the CSV file into the database table. SSIS provides a "Flat File Source" component that can handle CSV files with text qualifiers and column delimiters. You can also use the "SQL Server Destination" component to load the data into the database table.

I hope this helps! Let me know if you have any further questions.
PRADEEPCHEEKATLA 91,861 Reputation points

2024-04-26T05:30:04.4566667+00:00

@VickyD - - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-29T04:37:25.9866667+00:00

@VickyD Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-02T07:16:43.79+00:00

Hi @VickyD

Thank you for reaching out to the community forum with your query.

To import the CSV file with JSON data and text qualifier and column delimiter into your database table, you can use Azure Data Factory (ADF) to transform and load the data. Here are the high-level steps to accomplish this:

Create a new ADF pipeline: In the Azure portal, create a new ADF pipeline and add a "Copy Data" activity to the pipeline.

Configure the source dataset: In the "Copy Data" activity, configure the source dataset to point to the CSV file. Set the file format to "DelimitedText" and set the text qualifier to double quotes ("). Set the column delimiter to comma (,) and the row delimiter to CRLF.

Configure the sink dataset: Configure the sink dataset to point to your database table. Set the table schema and column mappings to match the structure of the CSV file.

Use a mapping data flow: In the "Copy Data" activity, use a mapping data flow to transform the JSON data column into a string column. To do this, add a "Derived Column" transformation to the data flow and use the "toString()" function to convert the JSON data column to a string column.

Run the pipeline: Save and run the pipeline to load the data from the CSV file into your database table.

I hope this helps! Let me know if you have any further questions.
VickyD 86 Reputation points

2024-04-02T08:54:56.2+00:00

how to do this ?

Use a mapping data flow: In the "Copy Data" activity, use a mapping data flow to transform the JSON data column into a string column. To do this, add a "Derived Column" transformation to the data flow and use the "toString()" function to convert the JSON data column to a string column.
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-03T09:49:32.3866667+00:00

Hi @VickyD

I apologize for the previous information about using Azure Data Factory (ADF) for this task. It seems I misunderstood your requirement.

Since you want to import the CSV file directly into your database table without parsing the JSON data, ADF with data transformation wouldn't be the most efficient approach. Instead, you can use other tools or programming languages to achieve this task.

I think you have one option is to use SQL Server Integration Services (SSIS) to import the CSV file into the database table. SSIS provides a "Flat File Source" component that can handle CSV files with text qualifiers and column delimiters. You can also use the "SQL Server Destination" component to load the data into the database table.

I hope this helps! Let me know if you have any further questions.
PRADEEPCHEEKATLA 91,861 Reputation points

2024-04-26T05:30:04.4566667+00:00

@VickyD - - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-04-29T04:37:25.9866667+00:00

@VickyD Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

@VickyD - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: Importing csv file with Json and data with text qualifier and column delimiter

Solution: The issue is resolved. Dataflow activity with some additional configurations worked.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Share via

Importing csv file with Json and data with text qualifier and column delimiter

1 answer

Your answer