Discussions

Expand all | Collapse all

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

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

     
    Posted 03-27-2019 14:13
    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!


  • 2.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

    Posted 03-27-2019 14:30
    Try removing carriage returns and replacing them with newlines.
    https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/


  • 3.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

     
    Posted 03-27-2019 14:39
    Thanks, Justin! Removing the carriage returns makes sense, but can you please elaborate on how to replace them with newlines? 


  • 4.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

    Posted 03-27-2019 14:48
    Good point, lol.
    Try using the Clean function. https://exceljet.net/excel-functions/excel-clean-function


  • 5.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

     
    Posted 03-27-2019 14:51
    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?


  • 6.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

    Posted 03-27-2019 14:56
    Are you importing from file or clipboard.


  • 7.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

     
    Posted 03-27-2019 14:58
    I'm importing from a file. 


  • 8.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

    Posted 03-27-2019 15:03
    What is the file extension?
    I imagine it is XLSX, you should try saving the sheet as a CSV.


  • 9.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

     
    Posted 03-27-2019 15:15
    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!!


  • 10.  RE: Importing Excel file to multi-line text field -- Quick Base adds ""_x000D_

    Posted 03-27-2019 15:16
    No problem!