Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joelhtown80
Regular Visitor

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

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

joelhtown80_1-1708111279148.png

 

joelhtown80_2-1708111391409.png

 

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?

joelhtown80_3-1708111836875.png

 

 

4 REPLIES 4
v-nikhilan-msft
Community Support
Community Support

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.

vnikhilanmsft_0-1708186717162.png

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

vnikhilanmsft_1-1708186837138.png

 

vnikhilanmsft_2-1708186893661.png


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

vnikhilanmsft_3-1708187007054.png

 

 

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

vnikhilanmsft_4-1708187075796.png


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

 

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

Hi @v-nikhilan-msft 

 

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.

 

joelhtown80_1-1709045015242.png

 

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

joelhtown80_4-1709072138165.png

 

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

joelhtown80_0-1709072284063.png

 

 

 

 

 

 

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.