Import Excel Into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is this Excel 97 file with 64 columns that needs to be imported into Access 97/2000/2002. The first 41 columns import okay completely, however, from column 42 through 63 I get every column headers and the first two target rows fine. It's rows from 3 through row(s) 53 starting at column 42 is where I am getting conversion errors all over the place. I've checked the column formats to see if they are the same as the first two rows and they seem to be the same

Any ideas on how to proceed with or force the importing and avoid the errors mentioned

Please, help if you can. I am running out of time on this one.....

Thanks in advance
 
Hi
have you checked what Access uses as field type for theses columns
(maybe it has set the type to a number but your row also contain text?)

--
Regards
Frank Kabel
Frankfurt, Germany

Russ said:
There is this Excel 97 file with 64 columns that needs to be imported
Click to expand...
into Access 97/2000/2002. The first 41 columns import okay completely,
however, from column 42 through 63 I get every column headers and the
first two target rows fine. It's rows from 3 through row(s) 53 starting
at column 42 is where I am getting conversion errors all over the
place. I've checked the column formats to see if they are the same as
the first two rows and they seem to be the same.
 
Hi Frank

All fields in Access are set to 30 Char text in the temp table. However, only the first two rows of data import okay. The remainder of the rows on the excel side generate conversion errors on the way in. Is there a function or method of checking these to see what they may be on the way into Access. If there is, can you supply an example

Thanks,
 
Hi
if you lime email me an example of your files and I'll take a look at
it

--
Regards
Frank Kabel
Frankfurt, Germany

Russ said:
Hi Frank:

All fields in Access are set to 30 Char text in the temp table.
Click to expand...
However, only the first two rows of data import okay. The remainder of
the rows on the excel side generate conversion errors on the way in. Is
there a function or method of checking these to see what they may be on
the way into Access. If there is, can you supply an example?
 
1. A vague question (errors mentioned? what errors?!) deserves a vague
answer such as, check the following registry settings:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

2. From an Excel perspective, shouldn't your post be entitled, 'Export
Excel data to MS Access'? Or is yours an MS Access question in the
wrong forum?

--

Russ said:
There is this Excel 97 file with 64 columns that needs to be
Click to expand...
imported into Access 97/2000/2002. The first 41 columns import okay
completely, however, from column 42 through 63 I get every column
headers and the first two target rows fine. It's rows from 3 through
row(s) 53 starting at column 42 is where I am getting conversion
errors all over the place. I've checked the column formats to see if
they are the same as the first two rows and they seem to be the same.
 
No, it's an excel question. However, if any one with both back grounds has the answer, by all means chime right in. I will appreciate any input I can get on the issue

Thanks,....
 
Frank

It seems that this formula is causing the problem. The empty string at the end of the function is killing the excel import into Access.

=IF(B11<>"",SUMIF(Events!A10:A311,'General log'!B11,Events!N10:N311),"")
 
Hi
try the following prior to your export:
- copy this sheet
- select all cells and copy them (CTRL+C)
- now goto 'Edit - Paste Special' and choose 'Values' to clear all
formulas
Try the export again

--
Regards
Frank Kabel
Frankfurt, Germany

Russ said:
Frank:

It seems that this formula is causing the problem. The empty string
Click to expand...
at the end of the function is killing the excel import into Access.
=IF(B11<>"",SUMIF(Events!A10:A311,'General
Click to expand...
log'!B11,Events!N10:N311),"")
 
If you appreciate any input you can get on the issue, did you research
those registry settings I mentioned? Have you even been to MSDN?

Check your perspective. If you were *exporting* to Jet using a data
access technology available to Excel, such as ADO, I would advise you
to check the aforementioned registry settings and ensure you are using
IMEX=1 in the extended properties of your connection string.

However, if you are using the MS Access GUI tools, there is no
explicit connection string in which to set the IMEX property. Where
does MS Access store this property, is it editable, is MS Access
(unlike ADO) hard-coded to respect the registry settings...? These
sound like MS Access questions to me so would be more suited to the
microsoft.public.access.gettingstarted ng.
 
Back
Top