Using Azure Data Factory I want to achieve 2 similar things. 1-) Many files (csv or such) in a blob container under different folders, I want to take first line (which is header and in some cases remove multiple starting lines) from each file and concat all left from all files into a single file also in the blob 2-) Many json files (each containing multiple json but all files in the same folder), I also want to convert them to a single csv file (concat all csv version of the json files) Then we will import that single file into a sql server or synapse table using bulk insert or openrowset or such. Import section we got it working. How do we concat many files in different directories into one or similarly many json files after converting them to csv concat them.
Few addons Assume 5 csv files are new, I will hit a sql server database and see if those files are imported already, lets say only 3 is not imported, sql server will return a resultset adding a unique integer fileid and the filename. In the concat csv the first column is the fileid we get from the database, that column does not exists in the csv, similar concept for the json file, each json file contains multiple records and the fileid will be repeated for the record in the same json file during concat csv file creation
Also in the same blob in the root there are multiple folders, each folder for a certain file type. Within that folder many subfolders (multiple levels) created when new files are added. When we ran the import process 30 minutes ago, we need a way to detect all new files added to the subfolder structure since the last import
This solution must be fast and efficient and it will be part of our ADF pipeline