如何使用C＃从Excel列的单列拆分多个值 [英] How can I split multiple values from single column of excel sheet using C#

问题描述

我已使用c＃将生物识别出勤文件（Excel）导入sqlserver。在此，时间字段包含多个值。所以第一个值必须被提取为及时，最后一个值必须是时间。

Plz任何人帮助。

excel表的结构是：

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

i想首先提取列时间值即9.00为时间和最后一个值，即6.15为最后一次。



我尝试过：



我试过以下查询：

选择SUBSTRING（[时间]，0，CHARINDEX（''，[时间]，0））[在时间]，

SUBSTRING（[时间]，CHARINDEX（''，[时间]，0）+ 1，LEN（[时间]），[外出时间]



这个关于成功的查询能够将时间上的第一个值分开，但是所有剩余的都是时间。所以对于外出时间我需要最后一个值

解决方案

将文件导入数据库（以便以后检查并获得原始文件）并触发立即重新解析到包含格式正确的数据的新表（这部分可以在.NET中完成，因为它更适合用于文本操作）。



您还可以创建文件监视器组件获取excel文件，因为它被复制到某个文件夹并解析它。这样你就可以使用已经准备好的列来正确格式化excel。



如果你有权访问excel，你可以将它作为宏命令（这很难实现自动化）。



最后，您可以在导入期间转换数据（也就是说，您打开文件，读取文件，转换数据，然后将其写入数据库）

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

解决方案

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)

这篇关于如何使用C＃从Excel列的单列拆分多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！