Solved Help - Problem with Line Feeds.

[gemma-the-husky]

I have an excel file with addresses including new lines that are respected in the original excel file.

If I import the file to a new table in Access (I am using A2003), the address stores this, with chr(13) chr(10) as line feeds.

2 Banana Road
Village
Leicester
Leicestershire
LE4 1BA

but it displays in the Access table, and on an Access query and form as the image below with no line breaks.

I've copied and pasted the text into notepad, and the line breaks display, and show correctly as #0D and #0A in a hex editor.

Thinking about it, I have had a similar behaviour in a different database using A2016. In both cases the details show correctly on screen, but when exported to excel or a csv, the CRLF characters just disappear, and do not get respected.

What am I missing?

Edit. Actually there is something curious, because access is reporting the length of the string in the query as 54 character, and there are 6 spaces to the right of the end of the postcode. The aren't real spaces, as trim doesn't remove them. See the second screen shot.
Notepad actually reports the string as 56 characters, being 48 characters as above plus 4 CRLF line ends.

 

[The_Doc_Man]

The CRLF sequence is, after all, text. Yes, a common set of delimiters... but text.

You say that the CRLF sequence does not appear to be properly honored in the text boxes. By any chance is the text box set for HTML or RTF ?

Another question is whether the import specifications were correct based on how you imported the Excel data in a way that didn't go through specifications.

 

[jdraw]

Dave,
You may find this Ascii display function by Crystal/strive4peace helpful.

 

[gemma-the-husky]

I've done it again, this time with Access 2016

The spreadsheet came from an external app and displays snip1.

I import the spreadsheet into an Access table, with no modifications. I then display a query with the address field and a text length.. So I have 52 displayed characters, plus 4 extra characters at the end, making 56 in total. See snip2.

But if I copy and paste the displayed text in notepad I get this, which shows as 60 characters in a hex editor. See snip-hex It's 52 characters plus 4 #0D #0A pairs. Might the #0D #0A pairs be treated as single unicode characters somehow causing the difference in the character counts?

4 Test Road
Tissington
Peak District
Derbyshire
DE17 4TY

Maybe I have an incorrect setting somewhere, but I can't see where.

 

[gemma-the-husky]

@jdraw

Thanks. That helped. Somewhere along the line the chr(13) & chr(10) is turning into just chr(10)

I ran a replace chr(10) with chr(13) & chr(10) and that seems to fix it. The length changes from 56 chars to 60 chars.

I wonder if the 4 phantom spaces occur because somewhere there is a vestige that the string length WAS actually 60, but now there are no chr(13) characters.

I assume copying and pasting changes the chr(10) to chr(13)&chr(10)

Very curious though.

@strive4peace
Thanks for that helpful utility, Crystal. I was going to step through the string in the database next, and your code saved me a fair bit of effort.

 

[jdraw]

Dave,
May be totally irrelevant.
I was helping someone whose line terminations were 10 not 13 10 or vice versa??
The bottom line was he was getting text from Unix/Linux and processing on Windows/Access.

It was a while ago, and I am not a Unix person, but, in his situation, I think we found he was not getting a Linefeed (whatever he wasn't getting both CR LF).

Just Googled:
Text files created on DOS/Windows machines have different line endings than files created on Unix/Linux. DOS uses carriage return and line feed ("\r\n") as a line ending, which Unix uses just line feed ("\n").

 

[Gasman]

Yes, you cannot edit a Linux script in Notepad, it buggers it up. Need something like Notepad++ and select the correct language type.

I got caught out by that years ago.

 

[gemma-the-husky]

^^^^
I'm not sure what system the data actually come from, it might be Oracle or SAP.

I double checked, and the data download is actually a .xlsx excel spreadsheet (so not a csv). The address fields seem to be including 1 byte per new line, so I presume it's just a chr(10) - checked by evaluating the string length in excel, but the newlines format correctly in the original spreadsheet.

So when I import the spreadsheet to access (or save to .xsl and import it, or save it to a csv and import it) it comes into access with just one character for each newline, which is definitely a chr(10), although now the data does not format correctly. There also seems to be these mysterious extra bytes at the end of the string.

So I will now run a replace process to replace chr(10) with chr(13) & chr(10), and I think this will be OK for what I want to do, and I will bear in mind the problem for the future.

I actually had an unrelated problem with another database which might actually be related. I was working with a restive API, and I was supposed to be able to download a pdf of a delivery ticket by collecting a string/array of bytes, and saving it in a text file. It worked, but every time the pdf wouldn't display, and was also the wrong size. I just couldn't get that working in Access. I thought it might be because Access uses 2 bytes per char, and somehow a string of bytes considered as chars just didn't work correctly. In that case it would have ben nice to be able to display the pdf, but it wasn't the end of the world that I couldn't. I wonder if this issue with the newline characters is somewhat similar.