Importing to Access impossible Thread poster: Elena Ghetti
|
|---|
Elena Ghetti 
Italy
Local time: 13:16
Member (2003)
English to Italian
+ ...
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?
| | | | | 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:
Sav... See more 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] ▲ Collapse
| | | | Elena Ghetti
Italy
Local time: 13:16
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?
| | | | | 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?
| | |
|
|
|
Elena Ghetti
Italy
Local time: 13:16
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?
| | | |
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).
| | | | Elena Ghetti
Italy
Local time: 13:16
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)...
| | | | | You may send me the Excel file | Sep 30, 2004 |
I will import it into Access 2000 and see what's wrong with it.
[email protected]
[Edited at 2004-09-30 08:41]
| | |
|
|
|
| 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".
Curren... See more 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 ▲ Collapse
| | | | | 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).
| | | | 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 | Trados Studio 2022 Freelance | The leading translation software used by over 270,000 translators.
Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop
and cloud solution, empowering you to work in the most efficient and cost-effective way.
More info » |
| | Anycount & Translation Office 3000 | Translation Office 3000
Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
