Extracted CSV file through Bulk Insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Extracted CSV file through Bulk Insert

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-20 : 02:55:27

Hi,

I have a CSV file from attendance raw punch using kronos.
This data need to extract and replicate into sql table.
I perform a bulk insert using a csv file to extract the data.
I just created a temp table which I used Nvarchar dataype to all the fields.

This is the result of bulk insert data:

ID          FullName            code    location   Date          IN        OUT           TOTAL 
117750	"Angel	 Jeffrey"	2204	PL8	2/18/2014	7:25:00	18:51:00	11:26:00
117750	"Angel	 Jeffrey"	2204	PL8	2/19/2014	8:12:00	18:25:00	10:13:00
117750	"Angel	 Jeffrey"	2204	PL8	2/20/2014	8:34:00	18:12:00	9:38:00

I wanted to remoived the double quote and convert date into datetime
and also the punch in, punch out and total hour. Just want to know if converting the punch in and out and total hours is correct. thanks.

My Query:

Select	EmployeeID, 
		Fullname = REPLACE(Surename,'"','')+', '+REPLACE(Firstname ,'"',''),
        REPLACE(Surename,'"','') AS SName,
        REPLACE(Firstname ,'"','') AS FName,
        CostCenter,
        Location,
        --TransDate,
        CAST(TransDate AS Datetime) AS AttDate,
        --CONVERT(datetime, TransDate),
        Convert(Datetime,Punchin,101) AS Punch_In,
        Convert(Datetime,PunchOut,101) AS Punch_Out,
        Convert(Datetime,TotalHours,101) AS Total_Hrs
From #ManHours
Where Location <> 'Date'

REsult of my Query: I got a negative value which it should ha 8 hrs rendered from Punch IN and OUT. Using datediff (hh, punch_in, punch_out) to get total hours rendered.


ID          FullName              LName    Fname         code  location   Date                             IN                     OUT                   TOTAL HOURS      HRS
117750	Angel,  Jeffrey 	Angeles	 Jeffrey 	2204	PL8	2014-02-18 00:00:00.000	1900-01-01 07:25:00.000	1900-01-01 18:51:00.000	1900-01-01 11:26:00.000  11
117750	Angel,  Jeffrey 	Angeles	 Jeffrey	2204	PL8	2014-02-19 00:00:00.000	1900-01-01 08:12:00.000	1900-01-01 18:25:00.000	1900-01-01 10:13:00.000  10
117820	Jago,     Dara    	Dar 	 Jago   	2243	RMAP	2014-02-15 00:00:00.000	1900-01-01 21:31:00.000	1900-01-01 06:10:00.000	1900-01-01 08:39:00.000	-15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources