读取文本文件特定列 [英] Read Text File Specific Columns
问题描述
美好的一天,
我正在编写一个新程序,我应该将新提取的原始出勤记录从生物识别时间记录器传输到基于Windows的计时软件
但是当我尝试使用下面的代码将其导入我的数据表/ datagrid时,
私有 Sub Button2_Click( ByVal sender As System。 Object , ByVal e As System.EventArgs)句柄 Button2.Click
Dim fileName 作为 字符串 = record.txt
Dim dirName As String = C:\ Users \ Biometric\Desktop \
Dim dt 作为 DataTable
使用 cn 作为 新 OleDbConnection( Provider = Microsoft.Jet.OleDb.4.0;& _
数据源=& dirName& ;& _
扩展属性=文字;)
< span class =code-comment>' 打开连接
cn.Open()
使用适配器作为 新 OleDbDataAdapter(_
SELECT * FROM& fileName,cn)
dt = 新 DataTable( record)
adapter.Fill(dt)
Dim ds As 新 DataSet
Dim joy As 新 BindingSource
joy.DataSource = dt
DataGridView1.DataSource = joy
结束 使用
结束 使用
结束 Sub
只需简单地将整行转移到一列。
1 2006-10- 12 14:04:47 1 0 1 0
1 2006-10-12 14:05:08 1 1 1 0
1 2006-10-12 14:06 :13 1 0 1 0
图例:
第1列用户ID =访问ID#
第2列日期&时间
第3栏设备编号
第4栏交易类型
0 - 办理登机手续
1 - 退房
5 - OT in(仅适用于iClock260)
4 - OT out(仅适用于iClock260)
3 - 中断(仅限可用)在U160,F4,iClock260)
2 - 爆发(仅限U160,F4,iClock260)
第5列0 - 使用密码
1 - 使用指纹
第6列工作代码(仅限U160)
我认为这是一个考勤设备的修正输出,我认为列有固定的宽度 - 字段与每个字段之间的空格对齐。
我需要做的是将三个主要数据传输到我的数据库表
第一列=员工ID
第二列(日期&时间)=出勤记录
第4列=交易类型,如果它是时间或结束。
这里有没有人有类似的经验吗?
首先,如果你的文件是空格分隔的,你需要创建schema.ini
file。
Schema.ini
文件应该放在record.txt <的同一目录中放置/ code>。
内容Schema.ini
:
[record.txt]
Format = Delimited()
ColNameHeader = False
MaxScanRows = 0
Col1 = AccessID Long
Col2 = AccessDate DateTime
Col3 = AccessTime文本宽度8
Col4 = DeviceNumber长
Col5 = TransactionType长
Col6 = UsePassword长
Col7 = WorkCode长
现在,创建新的Windows应用程序(不要忘记添加DataGridView)。然后将Form1类代码替换为:
公共 类 Form1
公共 Sub 新()
' Windows窗体需要此调用Designer。
InitializeComponent()
' 之后添加任何初始化InitializeComponent()调用。
Dim scon As String = String .Empty,scom As 字符串 = 字符串 .Empty
Dim con As OleDb.OleDbConnection = Nothing ,com As OleDb.OleDbCommand = Nothing ,rdr As OleDb.OleDbDataReader = Nothing
Dim sFileName 作为 字符串 = 字符串 .Empty
Dim dt As Data.DataTable = Nothing ,r As DataRow = 没有
尝试
sFileName = < span class =code-string> F:\Download\record.txt
scon = 临vider = Microsoft.Jet.OleDb.4.0;数据源=& IO.Path.GetDirectoryName(sFileName)& ;扩展属性='文本; HDR =是;架构= schema.ini;';
scom = SELECT * FROM& sFileName
con = 新 OleDb.OleDbConnection(scon)
con.Open()
com = 新 OleDb.OleDbCommand(scom,con)
rdr = com.ExecuteReader()
dt = 新数据。 DataTable
dt.Load(rdr)
Me .DataGridView1.DataSource = dt
Catch ex As 异常
MsgBox(ex.Message,MsgBoxStyle.Exclamation, 错误...)
最后
rdr.Close()
com.Dispose()
con.Close()
con.Dispose()
End 尝试
结束 Sub
结束 类
如您所见,连接字符串已更改;)
< blockquote>您可能需要考虑手动解析数据字段,而不是使用 OleDb
类。但另请参阅 http://www.connectionstrings.com/textfile [ ^ ]从文本文件中读取选项。
Good Day,
I am writing a new program where i should transfer freshly extracted raw attendance record from a biometrics time recorder to windows based time keeping software
However when i tried importing it to my datatable/datagrid using the code below,
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim fileName As String = "record.txt"
Dim dirName As String = "C:\Users\Biometric\Desktop\"
Dim dt As DataTable
Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
"Data Source=" & dirName & ";" & _
"Extended Properties=""Text;""")
' Open the connection
cn.Open()
Using adapter As New OleDbDataAdapter( _
"SELECT * FROM " & fileName, cn)
dt = New DataTable("record")
adapter.Fill(dt)
Dim ds As New DataSet
Dim joy As New BindingSource
joy.DataSource = dt
DataGridView1.DataSource = joy
End Using
End Using
End Sub
it just simple transfer whole row into one column.
1 2006-10-12 14:04:47 1 0 1 0
1 2006-10-12 14:05:08 1 1 1 0
1 2006-10-12 14:06:13 1 0 1 0
Legend:
1st column User ID = Access ID #
2nd column Date & Time
3rd column Device number
4th column Transaction type
0 - check in
1 - check out
5 – OT in (available only in iClock260)
4 – OT out (available only in iClock260)
3 - break in (available only in U160, F4, iClock260)
2 - break out (available only in U160, F4, iClock260)
5th column 0 – use password
1 – use fingerprint
6th column work code (available only in U160)
I think this is a fix output of a time attendance device where i think columns has a fixed width - fields are aligned with spaces between each field.
What i need to do is to transfer three primary data to my database table
1st Column = Employee ID
2nd Column (Date&Time) = Attendance Record
4th Column = Transaction type if its a Time In or Out.
Does anyone here had a similar experience with this?
First of all, if your file is space delimited, you need to createschema.ini
file.
Schema.ini
file should be placed in the same directory whererecord.txt
is placed.
Content ofSchema.ini
:
[record.txt] Format=Delimited( ) ColNameHeader=False MaxScanRows=0 Col1=AccessID Long Col2=AccessDate DateTime Col3=AccessTime Text Width 8 Col4=DeviceNumber Long Col5=TransactionType Long Col6=UsePassword Long Col7=WorkCode Long
Now, create new windows application (do not forget to add DataGridView). Then replace Form1 class code with:
Public Class Form1 Public Sub New() ' This call is required by the Windows Form Designer. InitializeComponent() ' Add any initialization after the InitializeComponent() call. Dim scon As String = String.Empty, scom As String = String.Empty Dim con As OleDb.OleDbConnection = Nothing, com As OleDb.OleDbCommand = Nothing, rdr As OleDb.OleDbDataReader = Nothing Dim sFileName As String = String.Empty Dim dt As Data.DataTable = Nothing, r As DataRow = Nothing Try sFileName = "F:\Download\record.txt" scon = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & IO.Path.GetDirectoryName(sFileName) & ";Extended Properties='Text;HDR=Yes;Schema=schema.ini;';" scom = "SELECT * FROM " & sFileName con = New OleDb.OleDbConnection(scon) con.Open() com = New OleDb.OleDbCommand(scom, con) rdr = com.ExecuteReader() dt = New Data.DataTable dt.Load(rdr) Me.DataGridView1.DataSource = dt Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...") Finally rdr.Close() com.Dispose() con.Close() con.Dispose() End Try End Sub End Class
As you see, connection string was changed ;)
You may need to consider manually parsing the data fields rather than using the OleDb
classes. But see also http://www.connectionstrings.com/textfile[^] for options when reading from text files.
这篇关于读取文本文件特定列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!