Forced line breaks in Excel do not import correctly into Access

[rhonda6373]

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!

[TheSmileyCoder]

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:

=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.

[NeoPa]

Chr(10)           == vbLF      == Line Feed (LF)
Chr(13)           == vbCR      == Carriage Return (CR)
Chr(13) & Chr(10) == vbCrLf    == Carriage Return/Line Feed (CR/LF)
                  == 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?

[jschn]

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...

[NeoPa]

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.

[TheSmileyCoder]

Thank you jschn for reporting this. I have corrected my post.