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.googlegroups.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.googlegroups.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.googlegroups.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.googlegroups.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|||SB <othellomy@.yahoo.com> wrote in
news:1186631817.654696.162750@.19g2000hsx.googlegroups.com:

> On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> 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
You might eliminate the text file and instead:
SELECT F1 AS [Col1], ... CAST(Fn AS datetime) AS [DateCol], ...
INTO [DestinationTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8. 0;HDR=YES;Database=full_path_to\filename
.xls',
'SELECT * FROM [SheetName$]')
Use the Microsoft.ACE.OLEDB.12.0 provider if you don't have Jet or if the
Excel version is 2007 (and, for Excel 2007, change Excel 8.0 to Excel
12.0).
You will probably find this a little tricky to get working (I surely
did!) so build up from a simple import, e.g. of a single integer column.