• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1528
  • Last Modified:

need to extract data using vb.net from text file and put into database

Here is the example data:
-----Logger Information-----
Model #      3090
Serial #      5134
Hardware Rev.      009-009-014

-----Site Information-----
Site #      0004
Site Desc      Farm Land with Treed Edges - Rolling Hills
Project Code      Proton
Project Desc      Proton
Site Location      Proton
Site Elevation      514
Latitude      N 044° 03.314'
Longitude      W 080° 29.091'
Time offset (hrs)      -5

-----Sensor Information-----

Channel #      1
Type      1
Description      NRG #40 Anem. m/s
Details      Boom 270 Deg True
Serial Number      SN:
Height      50 m
Scale Factor      0.765
Offset      0.35
Units      m/s

let's say i need to extract the field names

how do i do that in the best way possible.  my method is not working right
0
chem_kid
Asked:
chem_kid
1 Solution
 
AUmidhCommented:
First of all you have to define a standard for you to define your data in text file. which differenciate your data and field name.. like the following
Model #      3090
Serial #      5134
Hardware Rev#      009-009-014
now # is working like a separator..or some thing like $@$ or whatever you like. then
read all file from start to end line by line and get your data and field name easily by string.Split methode.
0
 
icrCommented:
Attached is a quick and dirty method.
Dim Log As Dictionary(Of String, Dictionary(Of String, String)) = New Dictionary(Of String, Dictionary(Of String, String))
 
Using LogTextReader As TextReader = New StreamReader("Test.txt")
    Dim Header As String = Nothing
 
    ' Keep reading until there is nothing left to read.
    While LogTextReader.Peek() <> -1
        Dim CurLine As String = LogTextReader.ReadLine().Trim()
        ' Ignore empty lines.
        If CurLine <> "" Then
            ' If it is a header...
            If CurLine.StartsWith("----") And CurLine.EndsWith("----") Then
                Header = CurLine.Substring(5, CurLine.Length - 10)
                Log.Add(Header, New Dictionary(Of String, String))
            Else
                ' Items must come under a header.
                If Header = Nothing Then
                    Throw New Exception("Invalid file.")
                End If
    
                Dim Parts As String() = CurLine.Split(New String() {"      "}, StringSplitOptions.None)
                If Parts.Length <> 2 Then
                    Throw New Exception("Invalid line.")
                End If
                Log(Header).Add(Parts(0), Parts(1))
            End If
        End If
    End While
End Using

Open in new window

0
 
chem_kidAuthor Commented:
that's good, but don't worry about the fields that look like "------[whatever]-----"

those actually I don't even need.

in essence it looks like I want to read evey line and take all the text until there is a double space and then the text to the right of that will be the field value.

so [field name]<spc><spc>[value]

the trick is I have to put those in a database which has tables corresponding to the field names there.

0
 
William ElliottSr Tech GuruCommented:
i figured i'd get in on the action
Dim strConnect
Dim adoConnection
Dim adoRecordset
Dim strSQL
 
save_as_cvs = true
send_to_sql = true
 
 
Const ForReading = 1
Const ForWriting = 2
 
strFileName = "C:\list1.txt"
strnewFileName = "C:\list1.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)
Set objFile2 = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
Set adoConnection = CreateObject("ADODB.Connection")
strconnect = "Driver={SQL Native Client};Server=shadowserver;Database=channels;Uid=sa;Pwd=sa;"
adoConnection.Open strConnect
 
strlogger = 	"-----Logger Infor"
strsite = 		"-----Site Informa"
strsensor = 	"-----Sensor Infor"
strdatetime = 	"Date & Time Stamp"
 
strnewText = ", "
Do Until objFile.AtEndOfStream
	strText = objFile.Readline
	if strtext <> "" then
		select case left(strText,17)
			case strlogger
				strtoggle = 1
				strnewtext = ""
			case strsite
				strtoggle = 2
				strnewtext = ""
			case strsensor
				strtoggle = 3
				strnewtext = ""
				writetocvs "Channel #, Type, Description, Details, " & _
						"Serial Number, Height, Scale Factor, Offset, Units,"
			case strdatetime
				strtoggle = 4
				strnewtext = ""
				writetocvs "Date & Time Stamp,	CH1Avg, CH1SD, CH1Max, " & _
						"CH1Min, CH2Avg, CH2SD, CH2Max, CH2Min, CH3Avg, CH3SD, CH3Max, " & _
						"CH3Min, CH4Avg, CH4SD, CH4Max, CH4Min, CH5Avg, CH5SD, CH5Max, " & _
						"CH5Min, CH6Avg, CH6SD, CH6Max, CH6Min, CH7Avg, CH7SD, CH7Max, " & _
						"CH7Min, CH8Avg, CH8SD, CH8Max, CH8Min, CH9Avg, CH9SD, CH9Max, " & _
						"CH9Min, CH10Avg, CH10SD, CH10Max, CH10Min, CH11Avg, CH11SD, " & _
						"CH11Max, CH11Min, CH12Avg, CH12SD, CH12Max, CH12Min"
			case else
		end select
 
	select case strtoggle
		case 1
		case 2
		case 3
			if left(strText,17) <> strsensor then
				strtab = instr(strtext, vbtab) + 1
				if strtab <> 0 then 
					strtrim = trim(mid(strtext,strtab))
				else
					strtrim = strtext
				end if
				if instr(strtext, "Units") then
					strnewtext = strnewtext & "', '" & strtrim
					sqlconnect strtoggle, mid(strnewText,4) & "'"
					writetocvs mid(strnewText,2)
					strnewtext = ""
				else
					strnewtext = strnewtext & "', '" & strtrim
				end if
				strText = ""
				strtab = ""
				strtrim = ""
			end if
		case 4
			if instr(strtext, strdatetime) = 0 then
				strNewText = Replace(strText, vbtab, "', '")
					sqlconnect strtoggle, "'" & strnewText & "'"
					writetocvs  "'" & strnewText & "'"
					strnewtext = ""
			end if
		case else
	end select
 
 
	end if
loop
objFile.Close
objFile2.Close
wscript.echo "done"
 
 
function sqlconnect(strtoggle, strval)
if send_to_sql = true then
	select case strtoggle
		case 1
		case 2
		case 3
		strSQL = "insert into chan (Chan_num,chan_Type,chan_Desc,chan_Details," & _
							"chan_SNum,chan_Height,chan_Scale,chan_Offset,chan_Units)" & _
							"VALUES (" & strval & ")"
	
		case 4
		strSQL = "insert into CH_calc (CH_timestamp,CH1Avg, CH1SD, CH1Max, " & _
								"CH1Min, CH2Avg, CH2SD, CH2Max, CH2Min, CH3Avg, CH3SD, CH3Max, " & _
								"CH3Min, CH4Avg, CH4SD, CH4Max, CH4Min, CH5Avg, CH5SD, CH5Max, " & _
								"CH5Min, CH6Avg, CH6SD, CH6Max, CH6Min, CH7Avg, CH7SD, CH7Max, " & _
								"CH7Min, CH8Avg, CH8SD, CH8Max, CH8Min, CH9Avg, CH9SD, CH9Max, " & _
								"CH9Min, CH10Avg, CH10SD, CH10Max, CH10Min, CH11Avg, CH11SD, " & _
								"CH11Max, CH11Min, CH12Avg, CH12SD, CH12Max, CH12Min) " & _
								"VALUES (" & strval & ")"
		strSQl = Replace(strSQL, "''", "NULL")
		case else
		
	end select
	writetocvs strsql
	adoConnection.execute strSQL
end if
end function
 
 
sub writetocvs(strtext)
	if save_as_cvs = true then
		objFile2.WriteLine strtext
	end if
end sub

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now