Datagridview into access database
Hello eveybody,
I have some code :
Code:
Imports System.Globalization
Imports System.IO
Public Class QcParser
Const highChar As Char = "H"c
Const lowChar As Char = "L"c
Const searchValueHigh As String = "|H||"
Const searchValueLow As String = "|L||"
Const separator As String = "|||"
Private Shared ReadOnly separators() As String = {separator}
Public Property FileName As String
Public Function ProcessFile(fileName As String) As DataTable
Dim lines = File.ReadAllLines(fileName).
SkipWhile(Function(line) Not line.StartsWith(separator)).ToList
Dim headers = lines.First().Split(separators, StringSplitOptions.None)
headers(0) = "DateTime"
Dim name = Path.GetFileName(fileName)
Dim table As New DataTable(name)
table.Columns.Add("Date & Time", GetType(DateTime))
headers.Skip(1).ToList.ForEach(Sub(header) table.Columns.Add(header, GetType(Double)))
Dim rows = lines.
Skip(1).
SkipWhile(Function(line) line.StartsWith(separator)).
Select(Function(source) ParseMeting(source)).
ToList()
rows.ForEach(Sub(rowValues) table.Rows.Add(rowValues))
table.AcceptChanges()
Return table
End Function
Function ParseMeting(line As String) As Object()
Return line.
Replace(searchValueHigh, separator + highChar).
Replace(searchValueLow, separator + lowChar).
Split(separators, StringSplitOptions.None).
Select(Function(value, index) ParseMetingWaarde(value, index)).
ToArray
End Function
Private Function ParseMetingWaarde(stringValue As String, index As Int32) As Object
Dim value As Object = DBNull.Value
If index = 0 Then
value = DateTime.Parse(stringValue, DateTimeFormatInfo.InvariantInfo)
ElseIf stringValue.Any AndAlso stringValue.First <> highChar AndAlso stringValue.First <> lowChar Then
value = Double.Parse(stringValue, NumberStyles.AllowDecimalPoint, NumberFormatInfo.InvariantInfo)
End If
Return value
End Function
End Class
This code will read trough a all file in the directory and publish this in a datagridview (so far so good)
But now I want to do 2 more things with it:
1. create a query that will calculate the average of eeach column
2. put this result in a *.mdb file
Can anybody help me with this code ?
Here is an example of the raw data of the file:
12/06/14 02:25 PM|||7.30|||4.25|||11.1|||31.9|||75.2|||26.3|||34.9|||16.3|||224|||7.0|||2.84|||34.2|||3.81|||66.3|| |17.7|||8.2|||1.6|||0.2|||6.1|H||8.27|||75.29|||51.89|||23.4|H||4.65
12/05/14 01:38 PM|||6.61|||4.20|||11.1|||31.5|||75.1|||26.5|||35.2|||16.2|||230|||6.6|||2.70|||34.5|||3.74|||62.6|| |18.7|||10.1|||2.0|||0.1|||6.5|||6.97|||73.57|||54.54|||23.4|||4.45
12/05/14 07:38 AM|||6.62|||4.21|L||10.9|||31.5|||74.8|||26.0|||34.7|||16.3|||228|||6.6|||2.70|||34.4|||3.94|||63.0| ||18.4|||9.3|||2.2|||0.2|||6.9|||6.46|||73.72|||54.26|||23.4|||4.44
12/04/14 02:10 PM|||6.48|||4.11|L||10.9|||30.7|||74.6|||26.5|||35.5|||17.4|H||264|H||9.5|||2.92|||34.8|||3.70|||63. 2|||18.5|||9.1|||2.0|||0.1|||7.0|||6.31|||74.58|||54.88|||23.3|||4.03
12/04/14 10:41 AM|||6.72|||4.15|||11.0|||30.6|||73.7|||26.4|||35.8|||17.1|||222|||6.7|||2.88|||34.7|||3.80|||62.0|| |18.8|||8.9|||2.1|||0.1|||8.0|||6.38|||74.44|||54.53|||23.2|||4.23
12/04/14 08:02 AM|||6.47|L||4.04|L||10.8|L||29.9|||74.0|||26.9|||36.3|||17.3|||228|||6.7|||2.74|||34.8|||3.90|||63. 7|||17.7|||8.0|||2.0|||0.1|||8.4|||6.22|||74.41|||52.58|||23.1|||4.13
12/03/14 01:30 PM|||6.67|L||4.10|L||10.9|||30.9|||75.5|||26.7|||35.3|||16.2|||220|||6.5|||2.65|||34.3|||4.09|||65.0 |||16.2|||8.3|||1.8|||0.1|||8.6|||6.57|||75.00|||53.51|||23.5|||4.24
12/03/14 07:37 AM|||6.62|L||4.10|||11.0|||30.8|||75.3|||26.9|||35.7|||16.0|||226|||6.5|||2.68|||34.2|||3.88|||63.4| ||18.0|||8.0|||1.8|||0.1|||8.7|||6.62|||75.07|||53.08|||23.5|||4.36
12/02/14 08:13 PM|||6.63|L||4.07|||11.0|||30.7|||75.5|||27.0|||35.8|||16.0|||212|||6.4|||2.73|||34.0|||3.89|||64.5| ||18.3|||8.4|||1.4|||0.1|||7.4|||6.86|||74.51|||52.84|||23.3|||4.28
12/02/14 06:18 PM|||6.82|||4.18|||11.2|||31.1|||74.3|||26.8|||36.1|||16.0|||223|||6.4|||2.72|||34.0|||3.80|||61.6|| |19.4|||9.9|||2.0|||0.2|||7.0|||6.59|||72.90|||52.99|||23.1|||4.33
Many thanks for helping out with this code !!
Kindly regards,
Koen
