How to import from Excel and keep the line breaks

_{Wednesday, December 21, 2011 3:06 PM | 1 vote}

I have an excel file with a large number of message texts. The texts are somewhat "formatted" through the use of line breaks - i.e. the message is displayed over several lines.

Now I want to import the texts into memo-fields in Access - but here this formatting disappears. I even get some strange characters at the end of my texts.

How do I import from Excel and maintain this line formatting?

_{Wednesday, December 21, 2011 5:50 PM ✅Answered}

There are two ways excel handles line returns

1) You have auto wrap property set for eith a cell or a column which will automatically add returns depending on the width of the column.  There is no characters in the line which indicates where these wraps occur so there is nothing that can be done in access do perform these wraps

2) Manual wraps were put into the cell using Alt - Enter.   I think these are being shown as the strange characters but not sure because other invisble characters (like a tab) may be in the text.  Sometimes using replace these characters will change or remove these characters.

The strange characters may also or been in the original data that was put into excel.  Often when you copy from a webpage and paste into excel invisible characters will be inserted into the text.  You may not be able to see these characters in excel and my be able to see them in Access.

Normally when I paste data into excel or access I first paste the data into notepad which will remove these characters.  Then copy and then paste the data into either excel or access.

jdweng

_{Wednesday, December 21, 2011 6:01 PM ✅Answered | 1 vote}

I have an excel file with a large number of message texts. The texts are somewhat "formatted" through the use of line breaks - i.e. the message is displayed over several lines.

Now I want to import the texts into memo-fields in Access - but here this formatting disappears. I even get some strange characters at the end of my texts.

How do I import from Excel and maintain this line formatting?

Excel uses line-feed character (ASCII 10) as the line separator, while Access uses the combination of carriage-return + line-feed (ASCII 13 followed by ASCII 10) as the line separator.  After importing, you can use the Replace function to replace Chr(10) with Chr(13)+Chr(10).  For example, you could execute a query like this:

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(10),Chr(13) & Chr(10));

Of course, you'd only want to run this query once for any record.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Wednesday, December 21, 2011 9:39 PM ✅Answered}

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(10),Chr(13) & Chr(10));

Of course, you'd only want to run this query once for any record.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

and although it would be completely redundant to do so, if you didn't know (or want to know) if a replace had already occurred, you could strip chr(13) out and then run the update (essentially running the update twice - once to strip the carriage return from all places and once to put it back in tandem with the line feed)

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(13),"");

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(10),Chr(13) & Chr(10));

EDITED: it would be better to check for chr(13) first though - then you would know it was probably already run on that record.

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

_{Wednesday, December 21, 2011 9:56 PM ✅Answered | 1 vote}

and although it would be completely redundant to do so, if you didn't know (or want to know) if a replace had already occurred, you could strip chr(13) out and then run the update (essentially running the update twice - once to strip the carriage return from all places and once to put it back in tandem with the line feed)

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(13),"");

    UPDATE ImportedExcelTable SET MyField = Replace(MyField,Chr(10),Chr(13) & Chr(10));

 

EDITED: it would be better to check for chr(13) first though - then you would know it was probably already run on that record.

Good thinking!  Or you might filter your update query so that it only acts on records that don't contain the CR character:

    UPDATE ImportedExcelTable
    SET MyField = Replace(MyField,Chr(10),Chr(13) & Chr(10))
    WHERE MyField Not Like '*' & Chr(13) & '*'

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Wednesday, December 21, 2011 9:58 PM ✅Answered | 1 vote}

What about one pass --

 UPDATE ImportedExcelTable SET MyField = Replace(Replace(MyField,Chr(13),""),Chr(10),Chr(13) & Chr(10));

_{Sunday, July 22, 2012 4:00 PM}

Pardon if this is a stupid question but I am new to Access:

Do I create a update query in access "Query Tools" tab and and then insert the  UPDATE ImportedExcelTable SET MyField = Replace(Replace(MyField,Chr(13),""),Chr(10),Chr(13) & Chr(10)); into the "update to: line in the query? or did i miss something in the "execute a query" comment????

_{Sunday, July 22, 2012 4:29 PM}

open the query designer and switch to "SQL" view.  then you can paste in the update statement.  Make sure you use the correct table and field names in your statement.

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.