question

ntrav-6392 avatar image
0 Votes"
ntrav-6392 asked ·

Multiple CSVs in Blob storage, trying to copy them to a single table in SQL

I have approx 30 csv files in blob storage and I am trying to copy them all to a single table in SQL. I have read several forums with similar goals, and I feel like they have pushed me close to the solution, but I have still not been successful.

I have a table in SQL ready for the data, and connected to a pipeline. And I think I have correctly set up the blob dataset to have the container holding the files selected. I then try to use 'ForEach' and activity 'Copy' but this is as far as I get. I am never able to preview this data.

I think if this were to work I would have another pipeline activity to take all the copies and append them to a single table in SQL, but I haven't made it that far. I also haven't found a similar example to this.

I think my blob dataset might not be configured properly. Just generally struggling as I am new to Azure.

Any help would be appreciated

azure-data-factory
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ntrav-6392,

Just checking to see if the below suggestions were helpful. If it answers your query, please do consider to click “Accept Answer” and "Up-Vote" on the post that helped, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.

0 Votes 0 · ·

Hi @ntrav-6392,

Following up to see if you query was resolved with below suggestion. Please do consider to click “Accept Answer” and "Up-Vote" on the post that helped, as it might be beneficial to other community members reading this thread.

0 Votes 0 · ·
MalleswarReddy avatar image
0 Votes"
MalleswarReddy answered ·

Hi,

Are you facing any error? Place your code if it is fine.

Please go through this video which explains step by step -
https://www.youtube.com/watch?v=EpDkxTHAhOs&list=PLGjZwEtPN7j8b9dPA0HrtJDptOB69B506

https://www.mssqltips.com/sqlservertip/6187/azure-data-factory-foreach-activity-example/

Regards,
Eshwar

· 3 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I followed this video and successfully copied 1 csv to 1 table.

Now I can't figure out how to use the ForEach to have all csv files copied to a single table.

0 Votes 0 · ·

Please follow the second link for foreach.

Regards,
Eshwar

0 Votes 0 · ·

Please check this video for foreach if the above link does not help you -- https://www.youtube.com/watch?v=P9uk-q-N8a4

0 Votes 0 · ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered ·

Can you share source blob dataset configuration screenshot. It should preview the data if it is set correctly or at least fail if config are not correct.

Other option would be to merge all files to single file using copy activity and simply another copy activity to load singel file to SQL


If the response helped, do "Accept Answer" and upvote it - Vaibhav

· 2 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Would the option to merge all csv files into a single file also be a for each? This option could work.

I also posted my blob configuration below

0 Votes 0 · ·

To merge all files to single file, try to use Copy activity. Copy from files in source folder and copy to same storage in some other folder. Mention Copy behavior to - Merge files

  • MergeFiles: Merges all files from the source folder to one file. If the file name is specified, the merged file name is the specified name. Otherwise, it's an autogenerated file name.

Reference - https://docs.microsoft.com/en-us/azure/data-factory/connector-file-system#file-system-as-sink




If the response helped, do "Accept Answer" and upvote it - Vaibhav

0 Votes 0 · ·
ntrav-6392 avatar image
0 Votes"
ntrav-6392 answered ·

9170-blob-config.jpg





I also get this comment when trying to preview the blob data:

Please provide actual value of the parameters to preview data
Parameters for dataset binancedataset


blob-config.jpg (153.8 KiB)
· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MalleswarReddy avatar image
0 Votes"
MalleswarReddy answered ·

Please check this video for foreach if the above link does not help you -- https://www.youtube.com/watch?v=P9uk-q-N8a4

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.