Importing Excel file to multi-line text field -- Quick Base adds "_x000D_"

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
Each week, I import data from an Excel sheet into one of my Quick Base tables. One of the fields is a multi-line text field. In the process of importing, Quick Base adds "_x000D_" wherever there was a line break in the Excel cell. For example: 

This is how my data looks in Excel: 
"This is an example of the comment I'd like to upload.

Sometimes there are multiple lines in the comment." 

This is how it ends up in Quick Base: 
"This is an example of the comment I'd like to upload._x000D_
_x000D_
Sometimes there are multiple lines in the comment."

I like that the line breaks are preserved, but it's a pain to grid-edit after my import and manually delete "_x000D_" wherever it appears. Does anyone know how to prevent this from happening? 

Thank you!
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
Try removing carriage returns and replacing them with newlines.
https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
Thanks, Justin! Removing the carriage returns makes sense, but can you please elaborate on how to replace them with newlines? 
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
Good point, lol.
Try using the Clean function. https://exceljet.net/excel-functions/excel-clean-function
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
Thank you! 

It looks like both of these options will just remove the line breaks, but I want to preserve them, because the text will be more readable in Quick Base. Do you know if there's a way to preserve the line breaks without getting "_x000D_" added?
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
Are you importing from file or clipboard.
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
I'm importing from a file. 
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
What is the file extension?
I imagine it is XLSX, you should try saving the sheet as a CSV.
(Edited)
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
You're right, I've been importing from an .xlsx file, but I just tested a CSV file and that fixes the problem. Thank you!!
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
No problem!