Type Conversion Error While Importing (Time Field)

[James]

Hi All,

I'm having a problem importing a csv file into an existing table. My
time field is erroring out. I set the data type of the of field to be
date/time and adjusted the input mask correctly and still getting the
error. When I changed the time field to a data type of text it
imported fine. My goal is to import hourly and I don't want to
change the data types each time I import. What can I do? I'm
thinking of importing into a temporary table and appending the data
while converting the time field from text to time, but I don't if that
is possible? All suggestions are welcomed and appreciated. Thanks!


James

 

[John W. Vinson]

Hi All,

I'm having a problem importing a csv file into an existing table. My
time field is erroring out. I set the data type of the of field to be
date/time and adjusted the input mask correctly and still getting the
error. When I changed the time field to a data type of text it
imported fine. My goal is to import hourly and I don't want to
change the data types each time I import. What can I do? I'm
thinking of importing into a temporary table and appending the data
while converting the time field from text to time, but I don't if that
is possible? All suggestions are welcomed and appreciated. Thanks!


James

Could you post an example of the time field? Input masks do not apply to
imports (only to keyboard input), AFAIK; you may need an expression to
explicitly convert.

For example, if the time in the csv file is like 0918 (for 9:18 am), it won't
be automatically converted. You may need to link to the .csv file (rather than
importing it) and use an Append query with a calculated field such as

CDate(Format([csvtime], "@@@"))
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David W. Fenton]

:

I'm having a problem importing a csv file into an existing table.
My time field is erroring out. I set the data type of the of
field to be date/time and adjusted the input mask correctly and
still getting the error. When I changed the time field to a data
type of text it imported fine. My goal is to import hourly and I
don't want to change the data types each time I import. What can
I do? I'm thinking of importing into a temporary table and
appending the data while converting the time field from text to
time, but I don't if that is possible?

This may be one of those cases where you want to import the source
data as plain text into a buffer field, then run a query to update
the real date/time field with the plain-text version properly
interpreted as time.

 

[James]

Hi All,

I'm having a problem importing a csv file into an existing table.  My
time field is erroring out.  I set the data type of the of field to be
date/time and adjusted the input mask correctly and still getting the
error.  When I changed the time field to a data type of text it
imported fine.  My goal is to import hourly and I don't want  to
change the data types each time I import.  What can I do?  I'm
thinking of importing into a temporary table and appending the data
while converting the time field from text to time, but I don't if that
is possible?  All suggestions are welcomed and appreciated.  Thanks!

James

Could you post an example of the time field? Input masks do not apply to
imports (only to keyboard input), AFAIK; you may need an expression to
explicitly convert.

For example, if the time in the csv file is like 0918 (for 9:18 am), it won't
be automatically converted. You may need to link to the .csv file (ratherthan
importing it) and use an Append query with a calculated field such as

CDate(Format([csvtime], "@@@"))
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

The format is hh:mm:ss AM or 10:10:35 AM

Thanks.

 

[James]

This may be one of those cases where you want to import the source
data as plain text into a buffer field, then run a query to update
the real date/time field with the plain-text version properly
interpreted as time.

Thanks for the information David.

 

[a a r o n _ k e m p f]

wow it must really suck to have to use Access as an ETL tool, huh?

you guys can't even do transforms while you import / export???

WHAT A JOKE!

 

[James]

wow it must really suck to have to use Access as an ETL tool, huh?

you guys can't even do transforms while you import / export???

WHAT A JOKE!




- Show quoted text -

Aaron I think your a joke. If you can't add to this conversation why
join. Beat it Kid!

 

[a a r o n . k e m p f]

james;

stfu and move to SQL Server, kid. The rest of the world did a long
time ago.

-Aaron

 

[a a r o n . k e m p f]

I usually use BULK INSERT or BCP or DTS or SSIS in order to do this

it must really suck to use a program without a real ETL tool