Import to Excel resulting in hash marks and chinese looking chars?

[Jacqui]

I'm getting a strange behaviour I haven't been able to straighten out and
hope someone might be able to help. I've got a memo field in an Access table
that I am importing into Excel.

Some of the strings come through ok and in full as memo and others are
coming in only partially, appearing trucated. When I select a cell that text
appears truncated in I see funny characters beginning part way through the
string and for the text that appears trucated in the formula bar. Others
cells are only displaying hash marks and when I select them I see the text
and funny characters too.

My field in Access is formatted Memo. My cells in Excel are formatted to
wrap text and as Text. The behaviour seems really inconsistent and I haven't
been able to find anything that might explain what's happening in my
searches.

Any ideas...please.

Thanks
Jacqueline

 

[John Nurick]

Hi Jacqui,

Exactly how are you "importing" the table into Excel?

When you compare the Access and Excel versions, do you find that values
less than 255 characters long came through all right, and that there
were only problems with longer values?

What happens if you export the table to a CSV (text) file and open the
CSV file in Excel?

 

[Ken Snell \(MVP\)]

First, EXCEL cells that are formatted as Text cannot display more than 255
characters -- if you have more than that, you'll see the # characters.
Change it to General format.

Second, many of the methods for exporting from ACCESS to EXCEL will truncate
memo fields -- using totals queries, using calculated fields in the query,
using OutputTo, using File | Export menu, etc.

From your desription of the "Chinese"-looking characters, I would guess that
you're exporting a Totals query where the memo field is one of the fields in
the GROUP BY clause -- this is a common problem with such queries.

Tell us more about how you're doing the export and we can assist.

 

[Jacqui]

'Thanks John. I'm importing Data->Import External...->New Database Query. All
characters are coming through (even greater than 255 - I thought Text cells
limit 32,000+ characters).

I've learned a little more in that the hash marks will display as text
correctly when I change the cell format to General from Text but the strange
characters still appear. When I change back to Text the hash marks reappear.
I'll move the data through a CSV file and see what happens.

I was hoping I could minimize how much I have to munch this data because I
need to move data from Excel to Access and then back Excel on a regular basis
and it's messy despite my efforts to define data types.

Thanks
Jacqueline

 

[Jacqui]

Hi Ken,

Thanks for responding. I thought text cells could hold +32000 characters?
The cells are holding more than the 255, just displaying weird. In Access
I've concatenated Memo fields using a query and then put the results in a
Make Table query. Then I initiate importing the table contents from within
Excel.

There seems to be a two fold problem in Excel 1) getting all of the text to
display 2) getting the text to display correctly ie: the chinese characters.

I can get it all (the characters) to display in the cell (all be it not
correctly) by changing cell format to General. I eventually need to export it
back to Access though and need it to be of text data type for my Memo field
or I run into a whole set of problems back in Access. One suggestion was to
move through a CSV file so I'll try that. I am just completely lost on the
chinese characters though.

Thanks
Jacqueline

 

[Ken Snell \(MVP\)]

As noted elsethread by you, the cells hold more than 255 characters, but
will not display them (the # characters) if the cell is formatted as Text.
Changing the cell to General format will allow EXCEL to display all the
characters.

As for the Chinese-like characters, you'll have to tell us more about the
actual query that you're trying to import.