Lakehouse - Data Lake CSV file to be loaded into new table

[joelhtown80]

I created a new shortcut to get the CSV files from the Data Lake and then loaded into a table.

The issue I have is that the CSV files has a carriage return that is throwing off the position of the data loaded (column _c0 in the screenshot below). Any idea on how to remove the carriage return prior to loading into the table?

[Anonymous]

Hi @joelhtown80 
Thanks for using Fabric Community.
You can use a Dataflow Gen2 for transforming the data before loading into a table. Please refer to the screenshots.

1.  Click "Edit Queries" in the ribbon.
2. Select the table you want, and right click the column header
3. Select "Replace Values", click in the "Value To Find"
4. Select "Advanced Options" check "Replace using special characters"
5. Select "Insert special character"
6. Click "Carriage Return" and a carriage return should show in your values section
7. Put nothing in "Replace With" and apply the changes.

Create a new dataflow gen2. Upload the CSV file which you want to load into a table.

Click on the triangle button for the column which you want to replace the carriage return and click on replace values.

Leave the Replace with tab as empty. After clicking ok the carriage returns are replaced with none.

After doing the transformations, select a destination for the Dataflow Gen2 and publish it. 

Hope this helps. Please let me know if you have any further queries.

[Anonymous]

Hi @joelhtown80 
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks

[joelhtown80]

Hi @Anonymous 

My apologies, I was pulled into another project. But here is my feedback.

Instead of uploading the CSV, my source is from the Data Lake. I used the connection below.

Everything was looking fine except I get the error below when I try to publish the dataflow. 

I changed all the data types to Text and re-publish but the error still persists.

[Anonymous]

Hi @joelhtown80 
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks