Importing from Excel2000 into Access2000 loses all formatting

[jmreinwald]

Hi all,

I am running into a problem when importing an Excel 2000 worksheet into an Access 2000 db.

To make a long story short, we had to extract ~1400 entries from a Lotus notes db. The only way for us to get to it was to export it into a delimited format. From there, we opened the delimited file in Excel, and did all the formatting there. In the individual cells, the text is formatted the way we want--line breaks, spacing, etc.

I imported the worksheet into Access, but now all the formatting is gone. Rather than the line breaks, showing up are the ubiquitous little squares. All the data types were correctly set using the Access wizard (memo, number, date, and so on).

Any ideas what's wrong here?

Thanks,
Joe

 

[lespaul]

Why are you trying to store formatting in the database? Why don't you just do the formatting when showing the data?

The little squares you see is how Access translates the ASCII code for the line breaks that you have entered in Excel.

Besides if your database is normalized you shouldn't have multiple pieces of data in a single field (which I'm assuming you do since you have breaks within a single field)

Leslie

 

[jmreinwald]

Thanks for the reply. I think the main problem is that we were taking things from a Lotus Notes db and having to export it to a delimited format. In Notes, at least the way I understand it, the field type in question was an rtf, and in exporting, it truncated the data after about 255 characters.

No one here in my group knows much about Notes, so we decided to get it into a delimited format, then go the Excel->Access route. Excel was decided on for the formatting because basically the people massaging the data are afraid of Access, thogh I clearly told them I could create forms for their use.

Just to complicate things, our intranet development team will then be pulling the data into an Oracle db and interfacing with it through ColdFusion.

I know there could have much easier and shorter ways to do this, but I was involved only when the Excel component was done and they said "Hey, we need this in Access, do it."

The original Notes database was a "KnowledgeBase" of our company's tips & tricks, that sort of thing. It contained everything from emails from development to release notes on our products. Those line breaks are because the one memo field is plain text with multiple paragraphs.

Thanks!