×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

importing csv datetime problems! Please Help

importing csv datetime problems! Please Help

importing csv datetime problems! Please Help

(OP)
Hi All,
I am Having Problems Importing a csv file. I have tried importing to an existing table, and import to a newtable.
At the moment it seems to error on date field.
the date data held in the csv is like this:
"01.01.2006 15:45"
The default datetime size is "8" in the existing table, but I cant seem to change this.
p.s. I am a newbie to sql server 2005 so please bare with me.
I can provide the csv for anybody if needed "Weather station data"
Thanks in advance
Tony

RE: importing csv datetime problems! Please Help

Your problem is that the date time format you are using isn't a recognized format.  Import the data in to a text field, then replace the "."s with "/"s.  From there you can load it into the production table using the datetime data type.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: importing csv datetime problems! Please Help

Why not just do the replace within the dataflow? saving the overhead of inserting data then updating it.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: importing csv datetime problems! Please Help

This is true, is can be done within the dataflow as well.

I'm still in the DTS mindset and DTS isn't the greatest in the world for the more advanced stuff.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: importing csv datetime problems! Please Help

(OP)
Thanks to you both,
I thought it should be possible to transform this in the data flow. But I am a little lost in how to do this.
Any help would be fantasic.
At the moment I am importing the date/time as a text just to get the import to work, this is ok because the data in the table is flushed be fore the next import.
Thanks Again
Tony
 

RE: importing csv datetime problems! Please Help

you may want to try the replace in your Datasource object and place the Replace function there.  


I also am currently still in the DTS mindset as I haven't even begun to move my stuff to SSIS  but from what I have seen and read you should be able to something this basic in the dataflow saving the IO overhead of updating.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: importing csv datetime problems! Please Help

(OP)
Hi All,
Sorry to reserect this thread.
I am still having problem with the date(s) (above mentioned) has anybody got any further, in there knowlege of ssis that can help with this one.
proving to be a real pain.
Thanks Again
Tony

RE: importing csv datetime problems! Please Help

Was reading Kimball's latest book on DW Lifecyclce  which focuses on the DW in SQL 05.  In the portions where he addresses ETS using SSIS there sounded like a few pieces that might work.  One being the Derived Column I believe it was.  Sorry in the processes of cleaning my machine at home so I can have a good install of 05 and all the BI goodies.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: importing csv datetime problems! Please Help

MDXer and mrdenny are both correct that you should import as a text field and use the Derived Column task to attack this.  
Derived column uses the expression language which is part of SSIS and a bit quirky to former DTS users.  You can import the date as a text field and through the Derived Column task, perform a string replace and assign the value to a new field defined as DateTime.  

BTW - we are all new to Sql Server 2005. I'm probably 2 pages ahead of a few others!!!

Good Luck!



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close