reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.
|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegro ups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> You should change the source data type to datetime or use text for
> both.
>
|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.
|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroup s.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>
|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroup s.com...
>
>
>
>
>
>
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB