topic Re: convert .cns file read from sharepoint to .csv in dataflow gen2 in Data Engineering

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

ShivaniTyagi01 — Fri, 22 Nov 2024 14:29:49 GMT

Hi,

I am new to MS fabric.

I have a requirement where I have got the data from my sharepoint folder and the files are in .cns format which is actually .csv format if you open the file. But the query is not reading the file . How should I convert the file from .cns to .csv in dataflow gen2 ?

Thanks,

Shivani Tyagi

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

NandanHegde — Fri, 22 Nov 2024 14:52:53 GMT

Unfortunately, based on my understanding, Fabric Dataflow Gen 2 doesnt support this file extension to be read directly (so it wont be possible to convert it) via dataflow Gen 2.

You would have to do some custom coding for that conversion which you can achive via Fabric notebooks

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

prasbharat — Sat, 23 Nov 2024 01:43:07 GMT

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.

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.
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.

Connect to the SharePoint Folder:
- Use the SharePoint Folder Connector in Dataflow Gen2 to load the .cns files.
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"))
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.

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

prasbharat — Sat, 23 Nov 2024 01:46:06 GMT

Hi,

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.

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.
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.

Connect to the SharePoint Folder:
- Use the SharePoint Folder Connector in Dataflow Gen2 to load the .cns files.
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"))
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.