Import of formatted Excel data into Access memo field

  • Thread starter Thread starter Neil Deinhardt
  • Start date Start date
N

Neil Deinhardt

Hello

I'm having great difficulty importing some "formatted" excel data into
into my Access database and preserving the formatting. I'm hoping
that someone here might be able to help.

I have an Excel workbook with 2 sheets of data that I want to import.
Most of the data is unformatted (ie. no line breaks etc). However,
there are a couple of columns that contain "formatted" data (ie. line
breaks etc. created using CRTL+Enter in Excel). I want to preserve
these line breaks when I import the data into an Access memo field.

Currently, the only way I seem to be able to do this is manually, by
individually copying the text from the formula bar in Excel and
pasting it into the relevant table field. Attempts to copy/paste the
excel cell loses the formatting, as does the usual import function in
Access.

Does anyone know any quick way to import data in this format, or
whether there is a better way of setting up the access table field?

I'm using Office 2000 on XP Home.
Thanks in advance.

Neil
 
(e-mail address removed) (Neil Deinhardt) wrote ...
I have an Excel workbook with 2 sheets of data that I want to import.
Most of the data is unformatted (ie. no line breaks etc). However,
there are a couple of columns that contain "formatted" data (ie. line
breaks etc. created using CRTL+Enter in Excel). I want to preserve
these line breaks when I import the data into an Access memo field.
Click to expand...

When you press alt+enter in an Excel cell, the resulting character is
ascii=10 (line feed). I had no problem importing an Excel Memo
column/field containing these characters into a Jet (MS Access)
column/field using:

SELECT MyMemoCol
INTO MemoColumnHasLineBreaks
FROM
[Excel 8.0;HDR=YES;Database=C:\Tempo\db.xls;].[MemoColumnHasLineBreaks$]
;

Jamie.

--
 
When you press alt+enter in an Excel cell, the resulting character is
ascii=10 (line feed). I had no problem importing an Excel Memo
column/field containing these characters into a Jet (MS Access)
column/field using:

SELECT MyMemoCol
INTO MemoColumnHasLineBreaks
FROM
[Excel 8.0;HDR=YES;Database=C:\Tempo\db.xls;].[MemoColumnHasLineBreaks$]
;

Jamie.

--
Click to expand...

I tried this approach, but I still lost the formatting and got
unwanted characters when the table was created in Access. I've set a
test workbook with 3 records and a dummy database to experiment with,
but I'm still getting nowhere. The only way I seemto be able to
preserve the formatting is by copying the contents of the XL cell into
the clipboard and pasting directly into the relevant field of the
Access record.

You can see what I mean from the files linked below: In the Access
DB, "RequiredTable" is what I need, and "NewTable" is what I'm
actually getting with every approach that I try....

http://www.adbc04.hemscott.net/db/source.xls
http://www.adbc04.hemscott.net/db/target.mdb

Neil
 
Back
Top