I need to import a CSV file that has a reserved word as column name column name examples: date, day

@Carlos Montoro Thanks for posting your question on Microsoft Q&A. Import csv file having reserved words to Azure SQL Database can be done by using "Copy Data activity" by making "Auto Create table" as enabled at Sink tab. Also, you need to make sure that you provide a table name in your Sink Dataset by using "Edit" checkbox. You can refer to the below pipeline I have created which imports csv with column names (day and year) from blob storage to Azure SQL DB. Sample CSV file Pipeline Please let me know if you have any other questions. ---------- Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Import CSV with invalid header name into Sql Database using Azure Data Factory

Accepted answer

Saurabh Sharma 23,761 Reputation points Microsoft Employee

2020-11-25T01:11:03.263+00:00

@Carlos Montoro Thanks for posting your question on Microsoft Q&A.
Import csv file having reserved words to Azure SQL Database can be done by using "Copy Data activity" by making "Auto Create table" as enabled at Sink tab. Also, you need to make sure that you provide a table name in your Sink Dataset by using "Edit" checkbox. You can refer to the below pipeline I have created which imports csv with column names (day and year) from blob storage to Azure SQL DB.
Sample CSV file

Pipeline

Please let me know if you have any other questions.

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.
Please sign in to rate this answer.

1 person found this answer helpful.
Carlos Montoro 21 Reputation points

2020-11-25T18:40:12.393+00:00

SaurabhSharma-msft ,
i made the change, but now i have this problems:

Carlos Montoro 21 Reputation points

2020-11-25T18:53:43.163+00:00

42801-e3009111-agent-adherence-report-1721022895.txt 42630-e3009111-agent-adherence-report-1797135163.txt

Saurabh Sharma 23,761 Reputation points Microsoft Employee

2020-11-25T23:55:48.63+00:00

@Carlos Montoro I see all files are different and have different number of columns. You need to use different data flows (with different table names in target) in order to handle different files.
Also one of the files - e3009111-agent-adherence-report-1721022895.txt has more columns than expected in one of the rows and thus you are getting errors while using data flow to import files as the target table is being created with 3 columns - "SL No.","Heading ","Description" initially and later the pipeline is expecting same number of columns in all other rows while importing the data to your SQL table.
'e3009111-agent-adherence-report-1721022895.txt' with row number 17: found more columns than expected column count 3.,Source=Microsoft.DataTransfer.Common,

Saurabh Sharma 23,761 Reputation points Microsoft Employee

2020-11-25T23:57:31.613+00:00

Also, if you are trying to import files with varying schema and columns, then you need to look for scrubbing your data using "mapping data flows" and probably check for schema drifts if require to handle incoming data. Please refer to the documentations - concepts-data-flow-overview and concepts-data-flow-schema-drift for details.

Carlos Montoro 21 Reputation points

2020-11-27T01:14:27.5+00:00

Thank you very much!
Sign in to comment

Share via

Import CSV with invalid header name into Sql Database using Azure Data Factory

0 additional answers