Keeping Format Importing From Excel

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am using the Import Excel Spreadsheet option on the External Data tab in
Access. The spreadsheet contains 29 columns. Of these 3 columns contain
formatted data, that is, paragraphs and new lines. The corresponding fields
are defined as Memo (plain text) in Access.

I have verified that each of the 29 columns is being correctly defined as to
data type when the spreadsheet is imported.

About the first half of the records (from a primary key standpoint) loose
the formatting when imported while the second half are formatted correctly.
It does not matter what order the records are in when imported, the same
records loose formatting and keep formatting every time.

The incorrect formatting is consistent in that, when there should be a
carriage return and line feed, there is no space at all between one line and
the next, that is, if two lines look like the following in Excel

This is test line 1.
This is test line 2.

The lines appear as follows in Access

This is test line 1.This is test line 2.

The loss of formatting occurs if the field is defined as plain text or rich
text.

Any suggestions as to how to keep the formatting?
 
Thank you for the help, I used the code and got the same lost format results.
I will keep looking.
 
Thank you for the help, I used the code and got the same lost format results.
I will keep looking.
 
EXCEL uses Chr(10) [line feed] as a new line character. ACCESS uses the
combination of Chr(13) [carriage return] and Chr(10) [line feed], expressed
as Chr(13)+Chr(10).

After you import the data, run an update query on that field that looks like
this:

UPDATE Tablename
SET Fieldname = Replace(Fieldname, Chr(10), Chr(13)+Chr(10);
 
EXCEL uses Chr(10) [line feed] as a new line character. ACCESS uses the
combination of Chr(13) [carriage return] and Chr(10) [line feed], expressed
as Chr(13)+Chr(10).

After you import the data, run an update query on that field that looks like
this:

UPDATE Tablename
SET Fieldname = Replace(Fieldname, Chr(10), Chr(13)+Chr(10);
 
That solved the problem, thank you.


Ken Snell MVP said:
EXCEL uses Chr(10) [line feed] as a new line character. ACCESS uses the
combination of Chr(13) [carriage return] and Chr(10) [line feed], expressed
as Chr(13)+Chr(10).

After you import the data, run an update query on that field that looks like
this:

UPDATE Tablename
SET Fieldname = Replace(Fieldname, Chr(10), Chr(13)+Chr(10);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jim said:
I am using the Import Excel Spreadsheet option on the External Data tab in
Access. The spreadsheet contains 29 columns. Of these 3 columns contain
formatted data, that is, paragraphs and new lines. The corresponding
fields
are defined as Memo (plain text) in Access.

I have verified that each of the 29 columns is being correctly defined as
to
data type when the spreadsheet is imported.

About the first half of the records (from a primary key standpoint) loose
the formatting when imported while the second half are formatted
correctly.
It does not matter what order the records are in when imported, the same
records loose formatting and keep formatting every time.

The incorrect formatting is consistent in that, when there should be a
carriage return and line feed, there is no space at all between one line
and
the next, that is, if two lines look like the following in Excel

This is test line 1.
This is test line 2.

The lines appear as follows in Access

This is test line 1.This is test line 2.

The loss of formatting occurs if the field is defined as plain text or
rich
text.

Any suggestions as to how to keep the formatting?
Click to expand...
Click to expand...
 
That solved the problem, thank you.


Ken Snell MVP said:
EXCEL uses Chr(10) [line feed] as a new line character. ACCESS uses the
combination of Chr(13) [carriage return] and Chr(10) [line feed], expressed
as Chr(13)+Chr(10).

After you import the data, run an update query on that field that looks like
this:

UPDATE Tablename
SET Fieldname = Replace(Fieldname, Chr(10), Chr(13)+Chr(10);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jim said:
I am using the Import Excel Spreadsheet option on the External Data tab in
Access. The spreadsheet contains 29 columns. Of these 3 columns contain
formatted data, that is, paragraphs and new lines. The corresponding
fields
are defined as Memo (plain text) in Access.

I have verified that each of the 29 columns is being correctly defined as
to
data type when the spreadsheet is imported.

About the first half of the records (from a primary key standpoint) loose
the formatting when imported while the second half are formatted
correctly.
It does not matter what order the records are in when imported, the same
records loose formatting and keep formatting every time.

The incorrect formatting is consistent in that, when there should be a
carriage return and line feed, there is no space at all between one line
and
the next, that is, if two lines look like the following in Excel

This is test line 1.
This is test line 2.

The lines appear as follows in Access

This is test line 1.This is test line 2.

The loss of formatting occurs if the field is defined as plain text or
rich
text.

Any suggestions as to how to keep the formatting?
Click to expand...
Click to expand...
 
Back
Top