Importing to Access impossible
Thread poster: Elena Ghetti

Elena Ghetti  Identity Verified
Italy
Local time: 20:17
Member (2003)
English to Italian
+ ...
Sep 29, 2004

I have troubles importing a large Excel table in Access (both with Office XP 2002 and 2003)

- when I try the import wizard (Get external data), I get the error "Subscript out of range"

- if I use the option Link Tables, the excel sheet is imported in the table list of Access (it keeps its Excel icon), but some long memo fields (i.e. the field "description") are truncated, so I don't get the whole description I have in the excel file...

any solution?


Direct link Reply with quote
 

Harry Bornemann  Identity Verified
Mexico
English to German
+ ...
Create and format the table before importing the content Sep 29, 2004

I don't know the cause of the error "subscript out of range" (maybe a manual line break in an Excel cell?), but you can avoid truncation by importing into a field defined as "memo".

If you need the sorting and filter functions (which don't work on memo fields), you can use a parallel field of type "text" and accept truncation there. Disadvantage of text fields: max. 255 chars and no manual linebreaks allowed.

In case this won't work, there is also a safer way:
Save the Excel file as text, check, edit and format it in Word (or Notepad) and then import it into a predefined table.

HTH,
Harry

[Edited at 2004-09-29 12:01]


Direct link Reply with quote
 

Elena Ghetti  Identity Verified
Italy
Local time: 20:17
Member (2003)
English to Italian
+ ...
TOPIC STARTER
Improvement but still mistakes when importing Sep 30, 2004

Harry_B wrote:

I don't know the cause of the error "subscript out of range" (maybe a manual line break in an Excel cell?), but you can avoid truncation by importing into a field defined as "memo".

If you need the sorting and filter functions (which don't work on memo fields), you can use a parallel field of type "text" and accept truncation there. Disadvantage of text fields: max. 255 chars and no manual linebreaks allowed.

In case this won't work, there is also a safer way:
Save the Excel file as text, check, edit and format it in Word (or Notepad) and then import it into a predefined table.

HTH,
Harry

[Edited at 2004-09-29 12:01]


thanks a lot for your answer!
I am importing column by column of the Excel file into an ad-hoc table I created in Access, and it works, eccept for the fact that even in memo fields it truncates sentences - i.e. with text fields it truncated my sentence after 255 characters as you say, with memo fields somewhere near 400 characters...
any idea why this happens?


Direct link Reply with quote
 

Harry Bornemann  Identity Verified
Mexico
English to German
+ ...
unbelievable Sep 30, 2004

Elena Ghetti wrote:
with memo fields somewhere near 400 characters...

1.) Are you sure the text is not already truncated at 400 characters in Excel?
2.) Is there any special character at char number 400?


Direct link Reply with quote
 

Elena Ghetti  Identity Verified
Italy
Local time: 20:17
Member (2003)
English to Italian
+ ...
TOPIC STARTER
Unfortunately no Sep 30, 2004

Harry_B wrote:

Elena Ghetti wrote:
with memo fields somewhere near 400 characters...

1.) Are you sure the text is not already truncated at 400 characters in Excel?
2.) Is there any special character at char number 400?


thanks!
the answer is no to both of your question. The only doubt I have is the following: some Excel cells appear as a series of \"number signs\" (that is, \"#####################\") but if you position the cursor on these cells the text is displayed correctly in the formula line. Might it be that Access does not like this?


Direct link Reply with quote
 

Harry Bornemann  Identity Verified
Mexico
English to German
+ ...
no Sep 30, 2004

Elena Ghetti wrote:
...some Excel cells appear as a series of "number signs" (that is, "#####################") but if you position the cursor on these cells the text is displayed correctly in the formula line. Might it be that Access does not like this?

The number signs only mean that the text is too long to be displayed in the Excel column.

BTW, it seems you are not really importing the columns, you are just copying them in Excel and pasting them into Access?
If you would import them (right click in Access, import...) you would at least get some informative import error messages (which are automatically saved in another table).


Direct link Reply with quote
 

Elena Ghetti  Identity Verified
Italy
Local time: 20:17
Member (2003)
English to Italian
+ ...
TOPIC STARTER
when I import single columns I get no error messages Sep 30, 2004

Harry_B wrote:

Elena Ghetti wrote:
...some Excel cells appear as a series of "number signs" (that is, "#####################") but if you position the cursor on these cells the text is displayed correctly in the formula line. Might it be that Access does not like this?

The number signs only mean that the text is too long to be displayed in the Excel column.

BTW, it seems you are not really importing the columns, you are just copying them in Excel and pasting them into Access?
If you would import them (right click in Access, import...) you would at least get some informative import error messages (which are automatically saved in another table).


To import a single column I copy this column into an empty Excel file, so that it contains only one column, then I use the get external data function in Access, and I select the option "existing table" in the import wizard - no error messages when I do this, only, when I check imported text some of it is missing (truncted)...


Direct link Reply with quote
 

Harry Bornemann  Identity Verified
Mexico
English to German
+ ...
You may send me the Excel file Sep 30, 2004

I will import it into Access 2000 and see what's wrong with it.

Harry.Bornemann@t-online.de

[Edited at 2004-09-30 08:41]


Direct link Reply with quote
 

Harry Bornemann  Identity Verified
Mexico
English to German
+ ...
handing over to community Sep 30, 2004

Observations:
1) When you import the Excel file into Access
you don't have the option to save to an existant table
and the column format is automatically set to "text".
2) When you save the Excel file as text
some fields are saved only as number signs!!

I have to abandon now, because I have urgent work to do,
but I found out that you can get rid of the number signs
by selecting the column and formatting the cells as "Standard".
Currently they are "text".
When you save the file in text format now, no number signs
are contained.

You will have to play with tabulator signs and column separators to get an importable format, which may take some hours...

Cheers,
Harry


Direct link Reply with quote
 
sansibarlavende
English
Link and make table Nov 26, 2007

Alternatively to all the suggestions you might link the xls-Table and than copy it to an access table by using a Make-Table Query (choose a different name for the table to which you copy your data).

Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Importing to Access impossible

Advanced search






PDF Translation - the Easy Way
TransPDF converts your PDFs to XLIFF ready for professional translation.

TransPDF converts your PDFs to XLIFF ready for professional translation. It also puts your translations back into the PDF to make new PDFs. Quicker and more accurate than hand-editing PDF. Includes free use of Infix PDF Editor with your translated PDFs.

More info »
memoQ translator pro
Kilgray's memoQ is the world's fastest developing integrated localization & translation environment rendering you more productive and efficient.

With our advanced file filters, unlimited language and advanced file support, memoQ translator pro has been designed for translators and reviewers who work on their own, with other translators or in team-based translation projects.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search