convert .cns file read from sharepoint to .csv in dataflow gen2

Hi,

Great question! Here how i think you can resolve the issue with .cns files in Microsoft Fabric Dataflow Gen2. Since .cns files are essentially .csv files, we need to ensure Dataflow Gen2 can recognize and process them correctly. There are two options you can choose from based on your preference. let me know if this works and update which one you had considered & working and finanlly mark as resolved if you are fine with provided solution.

Option 1: Rename .cns Files to .csv in SharePoint

If renaming the files is feasible, this option will allow Dataflow Gen2 to natively recognize them as CSV files.

1. Automate Renaming with Power Automate:
   • Set up a Power Automate flow to monitor the SharePoint folder for .cns files.
   • Automatically rename the files by replacing the .cns extension with .csv.
2. Load .csv Files in Dataflow Gen2:
   • Once the files are renamed, connect your Dataflow Gen2 to the SharePoint folder.
   • Process the renamed .csv files without any additional steps.

This option is straightforward if you have many .cns files and renaming them upfront is not an issue.

Option 2: Process .cns Files Directly in Dataflow Gen2

If renaming is not an option, you can configure Dataflow Gen2 to recognize and process .cns files as CSV files.

1. Connect to the SharePoint Folder:

   • Use the SharePoint Folder Connector in Dataflow Gen2 to load the .cns files.

2. Filter for .cns Files:

   • In the Power Query editor, filter files with the .cns extension:
     powerquery

     Copy code

     Table.SelectRows(Source, each Text.EndsWith([Name], ".cns"))

3. Parse .cns Files as CSV:

   • Add a transformation step to parse the .cns file content as CSV:
     powerquery

     Copy code

     Csv.Document(File.Contents([Folder Path] & [Name]), [Delimiter=","])
     Replace "," with the appropriate delimiter if your .cns files use a different one (e.g., tab).

This option allows you to process .cns files directly without needing to rename them.