Excel column issue reading
Hi Experts, We have excel with one column which is actually Custom column and type is h:mm:ss (when I right click and seleting format cells). So when I'm selecting any of the cell into the excel file, it has values in the for of h:mm:ss but when I look above the value "fx" it is different some thing like date + time + AM/PM ( for the same seleted cell ) ---- which means two different values for the same selected cell. Now we need to load this excel file using SSIS to our DB table, I tried but it is loading upper value ( date + time + AM/PM ) and not the value which the selected cell is having h:mm:ss. Suppose for an example I have selected on cell whose value is 41:48:57 but when I see above near by to fx it is having 1/1/1900 4:14:40 AM and SSIS is loading this value and not the actual value ( 41:48:57 "h:mm:ss" format). So how to tell SSIS to load value in the form of h:mm:ss and not the date+time+AM/PM value? Tried my best to explain you guys. Please let me know if you have any specific quesiton. Thanks Kumar
October 21st, 2010 6:50pm

Inside SSIS package use a derived column with the expression as : REPLACE(ColName,"1/1/1900 ","")Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 6:58pm

Hi Nitesh, Thank You for your response. Firstly it is not always having date at the prefix so I cannot use your replied logic, check below link which describe exactly my problem:- http://www.keepandshare.com/doc/2317735/excel1-xls-october-21-2010-12-49-pm-26k?da=y Please let me know if you have any question. Kumar
October 21st, 2010 7:52pm

Hi Kumar I am sure that Nitesh is right, but the way I see it you may have a variety of business rule, so my suggestion is to use a "SCRIPT COMPONENT" and implement the variety of your business ruleSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 8:17pm

Hi Nik, We are receiving this excel file from our users, and they are generating through their front-end application so we don't have any logic how to read excel cell value and NOT fx cell value. According to my giving example ( from my previous link ), if can tell me how to write script component to make it work? Please let me know if you have any more questions. Thanks Regards, Kumar
October 21st, 2010 8:22pm

what is it you want to see please provide examples scenario by scenario? as an example Excel file You want 00:00:00 ------> 12:00:00 AM please provide few examples Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 8:28pm

Hi Nik, Thank You for replying, as per my excel file:- if it is A1 than I want 0:00:00 instead of 12:00:00 AM if it is A2 than I want 0:00:00 instead of 12:00:00 AM if it is A3 than I want 27:45:05 instead of 1/1/1900 3:45:05 AM if it is A4 than I want 41:48:57 instead of 1/1/1900 5:48:57 PM Please let me know if you have more question(s). Thanks Kumar
October 21st, 2010 8:37pm

OK i am working on your file what you are trying to get is a display value of what you see , but the real values are what is in the fx section i.e you see 00:00:00 but its 12/30/1899 12:00:00 am before that yous hould consider that SQL server 2005 does not have a TIME data type it has SmallDateTime that is close to your need but the question is how are you going to save Time in SQL server??????????????? and please provide excamples i am still waiting thanksSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 8:41pm

Hi Nik, Firstly it is have 0:00:00 and NOT 00:00:00 (first zero is coming extra) Secondly, I have my DB table column as VARCHAR so that I can easily store 0:00:00 Please let me know if you have more doubts. Kumar
October 21st, 2010 8:46pm

OK wait i am working on itSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 8:56pm

Thanks Nik
October 21st, 2010 8:58pm

OK wow, good question as I had mentioned you are seeing what has been displayed on the excel sheet, not seeing the real value, SQL server will pick up the value not what’s its been displayed, anyways bottom line is that SQL server will still pickup the real value and it’s up to you to displayed or convert the value to the way it’s been displays and then save it as a string Best practice is 1- Read the file with a package and save the data as what the data are (as you mentioned what they are in the fx part) in a table in SQL server 2- Make a view that will display what you want OR by using an extra field and an update query convert the date to the DISPLAY numbers that you want This is the code that you will need as a view; I have coded each step one by one so that you can trace what I have done. CHECK THE LAST COLUMN AND CHANGE THE "SET @mDate = '1900-01-01 3:45:05 AM'" VALUES TO CHECK THE CODE (USE IT IN ssms) DECLARE @mDate as DateTime SET @mDate = '1900-01-01 3:45:05 AM' SET @mDate = '1900-01-01 5:48:57 PM' SELECT ----- STEP 1 ---------------------------------------------------------------- @mDate AS 'The main date' ----- STEP 2 ---------------------------------------------------------------- , RIGHT (CONVERT (nVarChar, @mDate , 121) , 10) AS 'Min, Sec, Mili part' , LEFT (RIGHT (CONVERT (nVarChar, @mDate , 121) , 10) , 6 ) AS 'Min, Sec part' ----- STEP 3 ---------------------------------------------------------------- , DateDiff (d, '1899-12-31' , @mDate ) AS ' Date Difference' , CONVERT (nVarChar, @mDate , 121) AS 'The main date again displayed' , Left (CONVERT (nVarChar, @mDate , 121 ), 13) AS 'Date and the Hour part only' , RIGHT (Left (CONVERT (nVarChar, @mDate , 121 ), 13) , 2 ) AS 'The Hour only' , CAST ( RIGHT (Left (CONVERT (nVarChar, @mDate , 121 ), 13) , 2 ) AS Integer) 'The Hour only as a Integer' ----- STEP 4 ---------------------------------------------------------------- , (CAST ( DateDiff (d, '1899-12-31' , @mDate ) AS Integer ) * 24 ) + CAST ( RIGHT (Left (CONVERT (nVarChar, @mDate , 121 ), 13) , 2 ) AS Integer) AS 'Date Diff * 24 + hours' , CAST ( (CAST ( DateDiff (d, '1899-12-31' , @mDate ) AS Integer ) * 24 )+ CAST ( RIGHT (Left (CONVERT (nVarChar, @mDate , 121 ), 13) , 2 ) AS Integer) AS nVarChar) + LEFT (RIGHT (CONVERT (nVarChar, @mDate , 121) , 10) , 6 ) AS 'Date diff * 24 + Hours + (Min Sec mil) all as a string' YOU MAY NEED TO MODIFY THE CODE Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 9:37pm

one last thing is that you can make a .NET code in SSIS in a SCRIPT COMPONENT to make the same think insted of using my code in SSMS , but i still prefer doing it in SSMSSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
October 21st, 2010 9:40pm

I think that you should use DECLARE @mDate as DateTime SET @mDate = '1900-01-01 3:45:05 AM' -- < --------- Tested with SET @mDate = '1900-01-01 5:48:57 PM' -- < --------- Tested with SET @mDate = '1900-01-01 0:00:00 AM' -- < --------- Tested with SELECT CASE WHEN @mDate = '1900-01-01 0:00:00 AM' THEN '00:00:00' ELSE CAST ( (CAST ( DateDiff (d, '1899-12-31' , @mDate ) AS Integer ) * 24 )+ CAST ( RIGHT (Left (CONVERT (nVarChar, @mDate , 121 ), 13) , 2 ) AS Integer) AS nVarChar) + LEFT (RIGHT (CONVERT (nVarChar, @mDate , 121) , 10) , 6 ) END AS 'Date diff * 24 + Hours + (Min Sec mil) all as a string' Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 9:56pm

Hi Nik, Thank you for your efforts. Thanks Kumar
October 22nd, 2010 4:20pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics