Click here to Skip to main content
15,901,853 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have imported a biometric attendance file(Excel) to sqlserver using c#. In this the time field contains multiple values. So the first value must be extracted as In time and the last value must be out time.
Plz anyone help.
structure of excel sheet is:

SrNo|Emp Code|Emp Name|Time|                    |Date
1   |0111    |aaa     |9.00 9.00 9.00 6.15 6.15 |12-feb-2016

i want to extract first value from column Time i.e 9.00 as In time and last value i.e 6.15 as last time.

What I have tried:

I have tried following query:
"select SUBSTRING([Time],0,CHARINDEX(' ',[Time],0))[In Time],
SUBSTRING([Time],CHARINDEX(' ',[Time],0)+1,LEN([Time]),[Out Time]"

this query on sucess able to separate the the first value as in time but all remaining as out time . so for out time I need last value
Updated 13-Apr-16 20:35pm
PIEBALDconsult 14-Apr-16 1:31am    
I you have found, SQL is very poor at string manipulation. Doing it in C# should be better. String.Split should do what you specify above.

1 solution

Import the file into the database (for later checks and to have the original) and trigger immediate re-parsing into new table that will contain properly formatted data (this part can be done in .NET as it is better suited for text manipulation).

You could also create file watcher component that will take the excel file as it is copied into some folder and parse it. That way you get properly formatted excel with columns already prepared.

You could do it as macro command if you have access to the excel (this is harder to automate).

Finally, you could convert the data during import (That is, you open the file, read it, convert the data and only then write it to the database)
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900