Excel to Access - Text LF and CR

[TriAdX]

So I am trying to import a sheet from Excel into Access and one of my data columns has large amounts of text in each cell.

This data has carriage returns and such like:

This is sample text.
I have lines of text.

I also have full paragraphs.



Now this text ends up in Access like this:

This is sample text.I have lines of text.I also have full paragraphs.

How can I maintain my lf and cr when importing into access?

[TriAdX]

Nothing on this? It seems like this would be a simple thing...

[MarvinP]

Hi TriAdX,

Access has a memo field that will allow CrLf characters in it. In VB talk they are called "vbNewLine".

My best answer is to give it a try and see what happens.

[TriAdX]

I have tried using the Memo data type and it still just treats the CR and LF as if they werent there.

[MarvinP]

Post a sample spreadsheet and let me try.
I wonder if 2003 Excel and Access were as versatile as 2010?
Click on Go Advanced below the message area and then on the paper clip icon above the advanced message area to attach a sample.

[alansidman]

You have limited flexibility when importing from Excel to Access. Access tries to determine the field type on its own and doesn't give you the opportunity to change. I have found that when there are particular fields that I want to import with particular characteristics, it is best to convert the Excel file to a .CSV file and then import it. While the import wizard is open, click on the Advanced button and you can select the type of field each Excel column should be. This is true on earlier as well as later versions of Access.

Alan

[TriAdX]

AccessImport.xlsx

above is a simple sample sheet. I just want to maintain the carriage returns and line feeds in "Data 4"

[MarvinP]

Hi,

I took your XL and imported it into 2010 Access and let the long filed be a type of "memo". It looked good when I got done. This site won't allow me to attach a .accdb or I'd give it back to you.

[TriAdX]

hmm.. Maybe it's new for 2010 that it imports no issues. I might need to upgrade.

[alansidman]

I tested the data import in 2002 (converting the Excel file to an .xls first) and it did parse the data in Memo format. It did show ASCII characters, however where the LF and CR were located.

Marvin, if you zip the file, I think you can upload it to the site.

[TriAdX]

So I just imported my sample into access 2007 and chose memo as the field type and it still crammed all CR and LF together when I view the table in access...

what am I doing wrong. it seems very straight forward yet, I get no formatting in access... I get no ASCII characters.. just all the lines ran into one.

[alansidman]

I just imported into AC 2007 and got the same as you described. I then imported it into AC2010 and saw no discernible difference between what I got in AC2007. I think that this is a quirk within Access. I don't think that there is a viable solution. Everything was crammed together.
Alan

[MarvinP]

Hey Alan,

Great idea. See if you can unzip this and read it.

[TriAdX]

AccessExample2.jpg
So I opened your DB and as you can see from my attached screenshot your DB still shows all of the text crammed together.

it didnt even show the ASCII characters for LF or CR...

Is this an issue or setting in my access?

Any other ideas? This is very important to my project as it MUST be in Access and it is currently in excel and I NEED to keep all cr and lf... there is no way I can go through the access fields and re-add all the cr and lf....

[alansidman]

Doing a little bit of research and found this. May be a bit of a hassle but it will probably work. Use the Find and Replace Icons in both Excel and Access.

http://msgroups.net/microsoft.public...d-cr-lf/123927

[TriAdX]

How do I find and replace an "Alt-Enter" in Excel?

[alansidman]

Just found a solution. Google ASAP Utilities for a free download. I've been struggling on this for the last hour and that may be the solution. Replace the LF and CR with another character that you can reverse in Access. Perhaps something that you would not use in the comments, ie. # or ^.

Let us know if this is successful for you. I am curious.

Alan

[TriAdX]

How do I find and replace an "Alt-Enter" or the LF/CR in Excel?

[alansidman]

You don't. That is why you need to get the ASAP utility. I researched this and Excel doesn't have the same find feature that word has for special characters.

Here is a link to the download which is at the top of the page.

http://www.asap-utilities.com/blog/i...urns-in-cells/

[TriAdX]

So.. here is what I have gotten!

First... in excel I just did a find and replace
Find: Ctrl-J
Replace: "|"

Then I imported my sheet into access.

Then ran a SQL query:

UPDATE MyTable SET MyTableColumn = Replace([CardRulesText],"|",Chr(13));

And all of my data now has the line returns that it had in Excel! Thanks for all your help guys!