Importing from Access to Excel - truncation

  • Thread starter Thread starter Jasneet Paintal
  • Start date Start date
J

Jasneet Paintal

When I import a datasheet from Access to Excel, where the
fields in Access contain more than 255 characters, these
get truncated in Excel. I believe this is to do with the
field size in Excel being restricted to 255 characters
during an import.

I am carrying out the import using the Tools--> Office
Links--> Analyse it with MSExcel link. I have this issue
with Office 97 and 2002.

Does anyone know if it is possible to change the import
parameters to allow fields containing data greater than
255 characters to be imported automatically without
truncation?

Many thanks in advance for your help/suggestion.

Jasneet.
 
I had the same problem but i resolved it by copying and
pasting from Access to excel directly (ie, not using the
Analyse It option).
So just select the data that u wish to copy in access,
open up excel and select Edit/Paste Special and then
select Paste As: Text.

This should fix ur problem.
 
Hi Jasneet,

This is usually because Analyze it with Excel uses the old Excel 5.0/95
file format. Try exporting the table or query from Access using
File|Export and specifying the Microsoft Excel 97-2002 format.

There can also be situations where you have a calculated field that
returns more than 255 characters, or a formatted memo field, and Access
assumes that the result is a text field and truncates it at 255
characters.

When I import a datasheet from Access to Excel, where the
fields in Access contain more than 255 characters, these
get truncated in Excel. I believe this is to do with the
field size in Excel being restricted to 255 characters
during an import.

I am carrying out the import using the Tools--> Office
Links--> Analyse it with MSExcel link. I have this issue
with Office 97 and 2002.

Does anyone know if it is possible to change the import
parameters to allow fields containing data greater than
255 characters to be imported automatically without
truncation?

Many thanks in advance for your help/suggestion.

Jasneet.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
This was a known problem in Office 97.
Fixed in 2002 for sure.
The key is that Access has to guess the datatype by examing the first 16
rows of the Excel file.
If the "memo" field in Excel is under 255 characters in each of the first 15
rows of data then Access will guess the datatype to be Text255 and all the
rest of the data will be imported that way. This obviously truncates your
memo data in any row past 16.

I suggest you make the first row of data contain a correct sample piece of
data for each field.
e.g. make sure there are more than 255 characters in the first row of data
for your memo column.

Ditto for alphanumeric data. (if the first 15 rows of data only have numbers
in it, then Access will guess Integer or something like that and then your
mixed numbers and text fields will not import correctly.)
 
Thanks John, worked brilliantly!


John Nurick said:
Hi Jasneet,

This is usually because Analyze it with Excel uses the old Excel 5.0/95
file format. Try exporting the table or query from Access using
File|Export and specifying the Microsoft Excel 97-2002 format.

There can also be situations where you have a calculated field that
returns more than 255 characters, or a formatted memo field, and Access
assumes that the result is a text field and truncates it at 255
characters.

When I import a datasheet from Access to Excel, where the
fields in Access contain more than 255 characters, these
get truncated in Excel. I believe this is to do with the
field size in Excel being restricted to 255 characters
during an import.

I am carrying out the import using the Tools--> Office
Links--> Analyse it with MSExcel link. I have this issue
with Office 97 and 2002.

Does anyone know if it is possible to change the import
parameters to allow fields containing data greater than
255 characters to be imported automatically without
truncation?

Many thanks in advance for your help/suggestion.

Jasneet.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
Back
Top