Importing from excel into access table

[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.

 

[Ken Snell [MVP]]

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.

 

[onedaywhen]

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.

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.

--

 

[karen scheu via AccessMonster.com]

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.

 

[Ken Snell [MVP]]

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.

 

[karen scheu via AccessMonster.com]

The date strings are in format MM/DD/YY, 01/10/05

 

[Ken Snell [MVP]]

Use CDate:

ChgdDate: CDate([DateTextStringField])

 

[karen scheu via AccessMonster.com]

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

 

[Ken Snell [MVP]]

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).

 

[Ken Snell [MVP]]

Post the SQL statement of the append query that you're trying this in.

 

[karen scheu via AccessMonster.com]

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.

 

[Ken Snell [MVP]]

Good!

 

[Ken Snell [MVP]]

(sent too soon)

Good! And congratulations! < g >

--

Ken Snell
<MS ACCESS MVP>

Good!

--

Ken Snell
<MS ACCESS MVP>

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.