By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,365 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Forced line breaks in Excel do not import correctly into Access

P: 33
I have a problem when I try to import an Excel 2007 spreadsheet in Access. Some cells in the spreadsheet contain text with forced line breaks but when I import the data in Access the line breaks are substituted by small square boxes. Does anyone know how I can prevent this from happening without having to edit all entries manually?

Thanks in advance!
Mar 12 '10 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
That is because Excel uses Chr(10) for a linebreak and Access uses Chr(10)+Chr(13)
I think one is called Carriage Return, and one is called LineFeed, but I dont know which is which :P

Now, there may be a smarter way of doing this. What I did was import to a temp table, and then run a update query on the temp table setting the field in question:
Expand|Select|Wrap|Line Numbers
  1. =Replace(myField, chr(10), chr(13) & chr(10))
Now the reason why I do this in a temp table, is that if I performed that query on my main table, I would be adding alot of extra line breaks each time I ran the query. After running the update query, I then import the temptable to my main table, and clear out the temptable. All of this is done via VBA coding.
Mar 12 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. Chr(10)           == vbLF      == Line Feed (LF)
  2. Chr(13)           == vbCR      == Carriage Return (CR)
  3. Chr(13) & Chr(10) == vbCrLf    == Carriage Return/Line Feed (CR/LF)
  4.                   == vbNewLine == New Line
On PCs using Windows the standard line separator sequence is Carriage Return/Line Feed. On Unix type boxes the standard is a simple Carriage Return.

I don't think this is your issue though. Excel too, uses CR/LF, but it has the facility to store multiple lines accurately in a cell by using LF. As this is correct and sensible behaviour, it would seem a good idea to explain what you mean by "prevent this from happening". Are you looking to convert the data, after it's been imported into an Access table, into CR/LFs?
Mar 13 '10 #3

P: 1
Just to add to this...

When I tried this in Access 2010 it did not work with:

chr10 & chr13 as in TheSmilerCoder example.

It had to be as above in NeoPa which is:

chr13 & chr10

That worked fine...
Jun 3 '13 #4

NeoPa
Expert Mod 15k+
P: 31,494
CR followed by LF dates back to when tele-type machines were used (TTY: ==> TeleTYpe device). The CR would take far longer to execute (as the print head had to return a greater distance) than the LF, but the LF could work even while the CR was still returning (the Carriage). Thus, the total elapsed time was only the time it took the Carriage to Return.
Jun 5 '13 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Thank you jschn for reporting this. I have corrected my post.
Jun 6 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.