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

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

joelhtown80 — Fri, 16 Feb 2024 19:32:11 GMT

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?

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

Anonymous — Sat, 17 Feb 2024 16:26:29 GMT

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.

Click "Edit Queries" in the ribbon.
Select the table you want, and right click the column header
Select "Replace Values", click in the "Value To Find"
Select "Advanced Options" check "Replace using special characters"
Select "Insert special character"
Click "Carriage Return" and a carriage return should show in your values section
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.

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

Anonymous — Tue, 20 Feb 2024 10:44:19 GMT

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

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

Anonymous — Wed, 21 Feb 2024 16:22:53 GMT

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

joelhtown80 — Tue, 27 Feb 2024 22:20:34 GMT

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.