Importing from excel into access table

  • Thread starter Thread starter karen via AccessMonster.com
  • Start date Start date
K

karen via AccessMonster.com

I have an excel file that contains extracted data from AS400. I need to
import this data into an access table. I have been importing the data into
a table, but would like to change the date fields from text(the default
data type when importing) to date data type. I also wanted to change the
text defaults of 255 to the actual size. Are there problems importing data
from excel when working with an existing structure. I have been geting an
error when importing into an existing table that does not give any detail
information but just that the import failed. Is there a better way to
import the data into access from excel?


I'd appreciate any help on this issue.

Thanks.
 
Continue importing as you do now (use text data type), but import into a
temporary table. Then use an append query to copy the data (with the correct
data type) to the permanent table.
 
Ken said:
Continue importing as you do now (use text data type), but import into a
temporary table. Then use an append query to copy the data (with the correct
data type) to the permanent table.
Click to expand...

If the data has been *curtailed* at 255 characters, choosing a
different data type in MS Access won't restore the missing data. The OP
needs to get the data type correct *before* it gets to the MS Access
UI. Take a look at the following:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--
 
Hi Ken,

I imported into a temporary access table, then created an append query to
append into an existing table that has the date fields defined as datetime
instead of text. The append did not work for the date fields. All the
date fields were blank. The temp table had the correct values. Do I have
to convert from text to date within the append query? If yes, what is the
convert function I need to use? I could not find one that works.
 
Yes, you convert the text string for the date to a date value in the append
query. How you do it completely depends upon the syntax of the date string.
Post an example of the date string that you import.
 
Hi Ken,

This function, CDate did not work. Here is my syntax:
order: CDate(ONDATE).

I can't find any documentation on date conversion functions. Can you tell
me where I can find out more about this topic.

Thanks,
Karen
 
RE: where to find info on CDate and such... open Help file, and browse to
the Functions heading under Visual Basic heading (I can never recall which
one it is, but only one has Functions as a subheading).
 
Hi Ken,

Thanks. I got it to work. The problem was that some dates are blank and
the import was getting error when it tried to convert blank to date. I
added an IIF statement to check if date was blank, save Null otherwise,
convert the textstring to date.
order: IIf([ONDATE]=" ",Null,CDate([ONDATE]))
It is working now with no error messages.
 
(sent too soon)

Good! And congratulations! < g >

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Good!

--

Ken Snell
<MS ACCESS MVP>

karen scheu via AccessMonster.com said:
Hi Ken,

Thanks. I got it to work. The problem was that some dates are blank and
the import was getting error when it tried to convert blank to date. I
added an IIF statement to check if date was blank, save Null otherwise,
convert the textstring to date.
order: IIf([ONDATE]=" ",Null,CDate([ONDATE]))
It is working now with no error messages.
Click to expand...
Click to expand...
 
Back
Top