Excel Data Importing

[Guest]

There is this Excel 97/2000/2002 file with 64 columns that needs to be imported into Access. The first 41 columns import okay, however, fro 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 the importing and avoid the errors mentioned

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

Thanks in advance

 

[Byron]

I don't know what process you are using now, but you
might take a look at article: 129304 in the Micorsoft
Knowledge Base. Also articles: 247412 or 246335.

Hope this helps.

Byron

-----Original Message-----
There is this Excel 97/2000/2002 file with 64 columns

that needs to be imported into Access. The first 41
columns import okay, however, fro 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.

 

[Guest]

Is there a function or method for checking the excel side of things to see what data type it is on the way in? If there is, can some one who knows give me an example

Thanks

----- Byron wrote: ----

I don't know what process you are using now, but you
might take a look at article: 129304 in the Micorsoft
Knowledge Base. Also articles: 247412 or 246335

Hope this helps

Byro

-----Original Message----
There is this Excel 97/2000/2002 file with 64 columns

that needs to be imported into Access. The first 41
columns import okay, however, fro 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

 

[Andy]

Is there a function or method for checking the excel side of things to see

what data type it is on the way in? If there is, can some one who knows give
me an example?

Have you tried importing the data into a temporary table comprising strings
or memo fields?
That way all the data should be transferred and then you can run macros or
queries to check and transfer the data.

Andy

 

[Guest]

Yes, I have. I am actually importing the data into a temp table. This, is where I am having the problems

----- Andy wrote: ----

Is there a function or method for checking the excel side of things to se

what data type it is on the way in? If there is, can some one who knows giv
me an example

Have you tried importing the data into a temporary table comprising string
or memo fields
That way all the data should be transferred and then you can run macros o
queries to check and transfer the data

And

 

[Andy]

Yes, I have. I am actually importing the data into a temp table. This, is

where I am having the problems.

I have used a table where every column is text (255 characters long) to
import excel, text normally accepts all that excel can throw at it. This
will get around problems such as number too large or leading spaces etc.
Are you using overlarge fields or field where size is what you are
expecting?
Other problem could be Long type number where a Single is expected or
floating where integer is expected. Have you tried checking this?

Andy

 

[Adrian Jansen]

If you are using the Import wizard, then it doesnt matter what the table
format is set to, if the wizard finds data in the first few rows of the
Excel sheet which are numeric, it will assume the data is all numeric, and
create an error when it finally finds one which can only be text. The
solution is to explicitly set the format of all the columns on the Excel
sheet as text. One way is to set a dummy value of text - eg any alpha
character - in the first row of every column. After the import, just delete
that record from the table.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Is there a function or method for checking the excel side of things to see

what data type it is on the way in? If there is, can some one who knows give
me an example?