Importing bullets formatting from Excel

[dhoang25]

Dummy1.zipHello,

I want to important the data from an excel file and keep all of its formatings "as is" (bullets and hard enters) in an Access table. For example, in the attached dummy file, I want to keep column C formatting as is in Access table. However, when I import this file into access by selecting Memo and Plain Text, it showed up as one big clump together like this: (Adopt Integration Server/Continuous Integration for ETL:• Option A• Option B• Option C• Option D• Option E). And when I run the report it shows up with the same format, when it should look like this below. Please HELP!!!

Adopt Integration Server/Continuous Integration for ETL:
• Option A
• Option B
• Option C
• Option D
• Option E

Thanks,


DH

[June7]

So you say the bullet symbols do come with the import? Interesting, I would not have thought so. What you are missing are CrLf's before each option. If the bullet is a searchable character, could use Replace function.

Let x represent the bullet character.
Let y represent the fieldname.

Replace([y], x, Chr(13) & Chr(10) & x)

If there is an ASCII code for the bullet:

Replace([y], Chr(x), Chr(13) & Chr(10) & Chr(x))

149 might be the ASCII for bullet http://ascii-code.com/

[dhoang25]

Bullets were not searchable! ...

[June7]

Sorry, that's all I have for simple idea.

Anything else would involve complex VBA custom function.

I would have thought the CrLf's and not the bullets would have imported. Weird.

How did you set the bullets in Excel? Did you insert special characters?

[dhoang25]

Thanks June7! I figured out a workaround method for it but it would involve manually changing the field from "Plain Text" to "Rich Text" each time after I imported into Access. Well, I guess this is how I will have to go about it. Thanks again anyway!

-DH

[June7]

I tested import of data from excel with bullets and alt+return characters. I inserted special character decimal 149

The import looks like:

•alkdjfldkjf?•glkjkjg?•slfdkgj

The ? represents the alt+return symbol that shows in the imported string (box with question mark in it). A Lf does import with the data.

I use the suggested Replace function and the result is:

•alkdjfldkjf?
•glkjkjg?
•slfdkgj

So I tried this:

Replace([fieldname], Chr(10), Chr(13) & Chr(10))

The result is:
•alkdjfldkjf
•glkjkjg
•slfdkgj