Need to keep my text and formatting...

November 14, 2007 1:49 PM

MS Access: Text in Excel column formatted (with returns and pseudo-bulleted lists), and more than 255 characters...

If I format the text in Excel to 'general' and not 'text' I don't lose any text when I import, but I lose the formatting RETURNS and pretty bulleted lists. Is there an easy way to fix this for the layman MS Access user?

posted by eatdonuts to Computers & Internet (10 answers total)

Is the entire bulleted list in one cell?
posted by Pants! at 3:47 PM on November 14, 2007

Response by poster: yes - all in one cell.
posted by eatdonuts at 4:48 PM on November 14, 2007 [1 favorite]

What version of Office do you have? What is the field type of the destination field in Access? Is the data stored in Access, or does Access provide a front-end to a different database for storage, such as MS SQL Server?
posted by notashroom at 5:17 PM on November 14, 2007

Response by poster: I thought it was MS Access 2003, but I just opened it on another computer and it says MS Access 2000 format. The field is formatted as text in excel (and I've recently changed it to general) to not lose any text, but memo format in access - the problem is, when I try to import it as text excel to access - it automatically denotes it as text and won't let me chose memo - that field is grey. The data is stored in Access only.
posted by eatdonuts at 5:37 PM on November 14, 2007

Okay, do you want to be able to search and process each individual bulleted item in Access as a separate record?
posted by Pants! at 7:04 PM on November 14, 2007

I suspect you are using the Import Wizard within Access? One thing that might work would be to create an empty table in access that contains all the fields with the desired field types in the order that you have them in your spreadsheet. Then try and copy and paste from Excel to the newly opened table.
posted by GregWithLime at 10:18 PM on November 14, 2007

Response by poster: hi ya, I don't want to do anything fancy like process each bullet differently. I just want for a bullet list in an excel cell for say, someone's bio, to look like a bullet list when i create it in excel so i can spit out an access report that shows various other info in the grid and then the bio - which then spits out like a bulleted list in access. nothing fancy, just want to enter the data and spit it out. no data in other sources or anything.

yes, am using wizard. have tried the create, cut & paste method but it doesn't work. I lose everything after 255 characters or it does something wonky like show everything as #### - without having the data behind it like excel does - it just keeps ####. driving me nuts.

anyone?
posted by eatdonuts at 6:56 AM on November 15, 2007

If the destination field is an Access memo-type field, copy and paste should work, and as GregWithLime points out, a temporary table is a strategy that might be helpful, as it could give you a way to copy it all over without worrying about matching records until it's in and formatted.

You should be able to keep your returns, but may not be able to keep your bulleted lists, as Access only supports Rich Text formatting in version 2007, and it seems what you have is Access 2003 in an Access 2000 format database (commonly set as default format for compatibility). Anything beyond that (font face, color, italics, etc.) will be stripped, unless it's an Access 2007 RT memo field.

I was unable to paste bullets into a memo field in my Access 2003 database when I tested a moment ago. That's unsurprising, as bullets in Access are generally unnecessary, because it's a database and bulleted items would normally represent individual records in a table and not be stored in a single field.
posted by notashroom at 7:03 AM on November 15, 2007

When you tried create, cut & paste, did you go into the table between create & paste and change that field type to memo?

I do similar things with terrible spreadsheets people send me at least once a week.
posted by fidelity at 7:09 AM on November 15, 2007

(On reload) It sounds like how you would get your best results (for flexibility, reporting, formatting, searching, etc.) for what you've described is to create one table for individuals and another table for bio items with a foreign kwey related to the primary key of your people table. For example:

People table:
1 | Smith | Thomas | 2/2/1948 | 912-555-1212
2 | May | Maggie | 6/9/1969 | 678-555-1212

Bio table:
1 | 1 | 1963 | All-American Table Tennis Champion
2 | 1 | 1969 | BS in Biology from Ivy League University
3 | 1 | 1970 | MA in Performing Cello from Artsy Univ.
4 | 2 | 1989 | Rhodes Scholar
5 | 2 | 1992 | BA in Psychology from State Univ, Salutorian

etc.

Does that help?
posted by notashroom at 7:13 AM on November 15, 2007