Import a csv file

Question

Import a csv file

MikeTomkies

Hall of Fame

Points: 3185
More actions
July 10, 2003 at 7:51 am

#151876

I have a csv file that contains a date DD,MMM,YYYY wich dts recognises as three different columns, I want to be able to merge these columns and use the date transformation to save to a sql date column.
Any suggetions please?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 1

Mike,

I may be oversimplifying this but this method will work. I'm assuming that the dates are in this format in the csv file:

DAY = '10'

MONTH = '07'

YEAR = '2003'

1. Import the csv file as is. Putting all naming conventions aside, I'm assuming the names of your character fields are DAY, MONTH, and YEAR. I'm also assuming your new file is called CSV_DATE_TEST.

2. Create a new datetime column (i.e., DATE_TEXT_TO_DATE_FORMAT) and update that with the [YEAR]+[MONTH]+[DAY] concatenated.

UPDATE CSV_DATE_TEST

SET DATE_TEXT_TO_DATE_FORMAT = [YEAR] + [MONTH] + [DAY]

All the best,

Dale

Edited by - DALEC on 07/10/2003 2:15:40 PM

Edited by - DALEC on 07/10/2003 2:16:40 PM

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

You may also consider using DTS with 'transfor data task (ActiveX Script)' to do that.

Here is the example of ActiveX Script.

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

DTSDestination("datetime1") = DTSSource("Col001") + "/" + DTSSource("Col002") + "/" + DTSSource("Col003")

Main = DTSTransformStat_OK

End Function

simonmeadows Old Hand Points: 376 More actions · Answer 3

I have personally had problems using ActiveX and simply concatenating the columns to form a date. I find a more reliable method is to use the dateserial function as such...

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

Function Main()

DateYear = DTSSource("Col001")

DateMonth = DTSSource("Col002")

DateDay = DTSSource("Col031")

DTSDestination("DATE") = DateSerial(DateYear, DateMonth, DateDay)

Main = DTSTransformStat_OK

End Function

I consider myself a relative novice in ActiveX and DTS so please correct me if this is inefficient.

Regards,

Simon

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 4

Thanks for all you help. I have started using active X script, Wich seems to have soved this problem and others.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

simonmeadows,

Be careful with using DateSerial and other date conversions. If you pass invalid or 'insane' values to DateSerial or even ISDATE you may or may not get a valid date answer or even the date you want. Also SQL will do date validation and conversion when attempting to use the resulting date and will fail on dates that DateSerial and expecially ISDATE find valid.

The only way is to test that the date is actually valid as a date and also a 'sane' date from the application's point of view.

When I DTS dates (from a 3rd party database) and know that there is a possiblility that date is not valid for sql I use ActiveX to check the year and if it is less than 1900 I convert it to null as the date is junk (I know you can guess that 0203 os really 2003 but can you depend on it!).

Far away is close at hand in the images of elsewhere.
Anon.

simonmeadows Old Hand Points: 376 More actions · Answer 6

Thanks David,

I actually have a check for ISDATE in that same script, I was just using the DateSerial Function as an example.

I actually am receiving dates in a text format YYMMDD, but found simply cutting them up and stringing them back together with + "/" to produce unreliable results. Sometimes SQL seemed to read the month first and sometimes the day, depending if the day was over 12.

To remove doubt i decided to use the DateSerial function and found it to be reliable. As you rightly point out though, there are many possible ways to go wrong. A particular example were dates which appeared as 000000 in the original file.

Regards,

Simon