text parsing question and insert specific text fields into db

THIS IS MY LAST QUESTION ABOUT THIS AREA!  I PROMISE!

I can't seem to get this to work.  I have a different text file here that I am trying to get the fields:

MetID
LoggerSN
and all the data in the table below.  obviously in the database i want metid and loggersn to repeat all the way down.

I used the code as a template which an expert gave me on a similar request, but i am not getting something right.  

just please help me out with this last one.

Here is the code some great guy gave me on a similar project, i figured i could just manipulate it, but not working.  Please see the attached file.  Again, I am looking to get metid, loggersn, and the sensor, chanel, etc... data in the table below.  thanks.
 
Dim strDatafile
dim strResultsFile
dim strFilestoSearch
 
dim objFSO
dim objShell
dim objOutputFile
 
dim intRow
dim strValue
dim strCommand
 
 
Dim adoConnection
Dim adoRecordset
Dim strSQL
 
 
Const ForReading = 1
Const ForWriting = 2
 
strFileName = "C:\upcproject\4301.txt"
strnewFileName = "C:\upcproject\4301dump.txt"
 
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 Server};Server=MIKEMAIN\SQLEXPRESS;Database=UPCwind_dev;"
adoConnection.Open strConnect
 
strlogger = 	"-----Logger Infor"
strsite = 		"-----Site Informa"
strsensor = 	"-----Sensor Infor"
strdatetime = 	"Date & Time Stamp"
strsensor = "Sensor"
 
 
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 = ""
				'objFile2.WriteLine "Channel #, Type, Description, Details, " & _
						"Serial Number, Height, Scale Factor, Offset, Units,"
			case strdatetime
				strtoggle = 4
				strnewtext = ""
				'objfile2.writeline "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 strsensor
				strtoggle = 5
				strnewtext = ""
				objFile2.writeline "sensor, chanel, type,Slope, Offset, Unit, Avg, SD, Max, Min"
				
				
			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) & "'"
					'objFile2.WriteLine 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 & "'"
					'objFile2.WriteLine  "'" & strnewText & "'"
					strnewtext = ""
			end if
		case 5
			if instr(strtext, strsensor) = 0 then
				strNewText = Replace(strText, vbtab, "', '")
					sqlconnect strtoggle, "'" & strnewText & "'"
					'objFile2.WriteLine  "'" & strnewText & "'"
					strnewtext = ""
			end if
		case else
	end select
 
 
	end if
loop
objFile.Close
objFile2.Close
wscript.echo "done"
 
 
function sqlconnect(strtoggle, strval)
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 5
	strSQL = "insert into sensorconfig (sensor, channel, Chtype,Slope, Offset, Unit, Avg, SD, Max, Min) & _
						"VALUES (" & strval & ")"
	strSQl = Replace(strSQL, "''", "NULL")
 
	case else
	
end select
objFile2.WriteLine strsql
'msgbox strsql
adoConnection.execute strSQL
end function

Open in new window

4301configexample.txt
chem_kidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

William ElliottSr Tech GuruCommented:
things that could be helpful if you want to get this into SQL
provide the following table  names and columns(fields in each table)

i can getup something to parse the information, but without that information i don't know 'how' to parse it.

also the above script i provided for the other text file won't work on this one.. they are formatted completly different.

look here see before and after example, this way we wouldn't have to make it work one way and then it not work for the way you have planned.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21377318.html


that is why i created the sql code last time.. to make sure that it entered exactly as planned.
0
chem_kidAuthor Commented:
one table: called tmpSensorDump
contains:
metID varchar
loggerSN varchar
Sensor varchar
Chanel      varchar
Type      varchar
Slope      number
Offset      number
Unit          varchar
(I don't need it to contain the avg, sd, max, min.....if it is easier to do that way then fine.  I'd rather not though.  It would be nice to know how I can do it if I did want to include them or not. they would be datatype of number)

no no lol....I'm not that bad!!!!  i know the other script won't work...haha
I just can't figure out what to replace to make it work.  


0
chem_kidAuthor Commented:
see something like this would work if i didn't have these damn tables to deal with at the bottom.


Imports System.
Imports System.Xml

' Open an XML file.
Dim textValue as String
Dim localName as String

Dim textReader As XmlTextReader = New
XmlTextReader (My.applicatin.Info.DirectoryPath & "\xmltest.xml").

textReader.Read()
If textReader.HasValue Then
                        ' Move to fist element
textReader.MoveToElement()

localName=textReader.LocalName
textValue=textReader.Value.ToString()
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

William ElliottSr Tech GuruCommented:
try this,. i haven't tested it yet,.
Const ForReading = 1
Const ForWriting = 2
 
strFileName = "C:\upcproject\4301.txt"
strnewFileName = "C:\upcproject\4301dump.txt"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
Set r_File = objFSO.OpenTextFile(strFileName, ForReading)
Set w_File = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
 
tog_sens = False
Do Until r_File.AtEndOfStream
	strText = r_File.Readline
	if tog_sens = true then
		myarray = split(strText, vbtab)
		strSensor = myarray(0)		
		strChanel = myarray(1)
		strType = myarray(2)
		strSlope = myarray(3)
		strOffset = myarray(4)
		strUnit = myarray(5)        
		w_File.writeline strmetID & ", " & _
						 strloggerSN & ", " & _
						 strSensor & ", " & _
						 strChanel & ", " & _
						 strType & ", " & _
						 strSlope & ", " & _
						 strOffset & ", " & _
						 strUnit
	else 
		select case left(strText,6)
			case "MetID:"
				met_ary = split(strText, vbtab)
				strmetID = met_ary(1)
			case "Logger"
				log_ary = split(strText, vbtab)
				strloggerSN = log_ary(1)
			case "Sensor"
				tog_sens = true
			case else
		end select
	end if
loop
 
r_File.close
w_File.close

Open in new window

0
chem_kidAuthor Commented:
it says error line 17: subscript out of range.  i can't figure out why.
0
chem_kidAuthor Commented:
is there a place you can refer me to that shows these functions and methods?  

also, to then put this in the sql database, i just use the sqlfunction in the last example you gave me?
0
chem_kidAuthor Commented:
if i dim and redim the myarray i get a type mismatch.  so i am stuck here.
0
William ElliottSr Tech GuruCommented:
k,, had to make sure to ignore empty lines
Const ForReading = 1
Const ForWriting = 2
 
strFileName = "C:\Documents and Settings\Administrator\Desktop\4301configexample.txt"
strnewFileName = "C:\Documents and Settings\Administrator\Desktop\4301configexample_d.txt"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
Set r_File = objFSO.OpenTextFile(strFileName, ForReading)
Set w_File = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
 
tog_sens = False
Do Until r_File.AtEndOfStream
	strText = r_File.Readline
	if strtext <> "" then
		if tog_sens = true then
			myarray = split(strText, vbtab)
			strSensor = myarray(0)		
			strChanel = myarray(1)
			strType = myarray(2)
			strSlope = myarray(3)
			strOffset = myarray(4)
			strUnit = myarray(5)        
			w_File.writeline strmetID & ", " & _
							 strloggerSN & ", " & _
							 strSensor & ", " & _
							 strChanel & ", " & _
							 strType & ", " & _
							 strSlope & ", " & _
							 strOffset & ", " & _
							 strUnit
		else 
			select case left(strText,6)
				case "MetID:"
					met_ary = split(strText, vbtab)
					strmetID = met_ary(2)
				case "Logger"
					log_ary = split(strText, vbtab)
					strloggerSN = log_ary(1)
				case "Sensor"
					tog_sens = true
				case else
			end select
		end if
	end if
loop
 
r_File.close
w_File.close
msgbox "done"

Open in new window

0
chem_kidAuthor Commented:
one last thing....and I think i should understand this....

can you add a function in for the sql table data dump?

assume table name is tmpSensorDump

i don't know what to pass in the function.
i have the table made obviously...don't waste your time worrying about the structure of that...i'll just modify the code later.


0
chem_kidAuthor Commented:
i see in the last example you did for me that you passed a value to the function for toggle, which in this case does not apply since we are only writing one type of output.  I also see that you passed newstringtext.  but where is that value here?

0
William ElliottSr Tech GuruCommented:
maybe this will explain better what i'm doing
'*******
'*******	Declare stuff
'*******
 
Const ForReading = 1
Const ForWriting = 2
strFileName = "C:\4301configexample.txt"
strnewFileName = "C:\4301configexample_d.txt"
'*******
'*******	Create fso for making files
'******* 
Set objFSO = CreateObject("Scripting.FileSystemObject")
'*******
'*******	create and open text files for reading and writing
'*******
Set r_File = objFSO.OpenTextFile(strFileName, ForReading)
Set w_File = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
 
'*******
'*******	Disable toggle_sens
'*******
tog_sens = False
'*******
'*******	Start reading text file
'*******
Do Until r_File.AtEndOfStream
'*******
'*******	read each line one by one into the strtext variable
'*******
	strText = r_File.Readline
'*******
'*******	if line is empty ignore it
'*******
	if strtext <> "" then
'*******
'*******	If Tog_Sens is true, thendo the top portion, else do the bottom
'*******
		if tog_sens = true then
'*******
'*******	if tog_sens is true, then split the text into an tabbed array
'*******	then read the array into variables
'*******
			myarray = split(strText, vbtab)
			strSensor = myarray(0)		
			strChanel = myarray(1)
			strType = myarray(2)
			strSlope = myarray(3)
			strOffset = myarray(4)
			strUnit = myarray(5)        
'*******	
'*******	now write those variables in the write file
'*******
 
			w_File.writeline strmetID & ", " & _
							 strloggerSN & ", " & _
							 strSensor & ", " & _
							 strChanel & ", " & _
							 strType & ", " & _
							 strSlope & ", " & _
							 strOffset & ", " & _
							 strUnit
		else 
'*******	
'*******	If Tog_Sens is false, then 
'*******	check the 6 characters to the left of the strtext variable
'*******
			select case left(strText,6)
'*******
'*******	if left 6 characters are MetID: then 
'*******	split the text and get the information after the 2nd tab and
'*******	write it to the variable
'*******
				case "MetID:"
					met_ary = split(strText, vbtab)
					strmetID = met_ary(2)
					
'*******	if left 6 characters are logger then 
'*******	split the text and get the information after the 1st tab and
'*******	write it to the variable
				case "Logger"
					log_ary = split(strText, vbtab)
					strloggerSN = log_ary(1)
					
'*******	if left 6 characters are sensor then 
'*******	set tog_sens = true
'*******	this will cause the script to ignore this second part of the if statement
'*******	we do this because after the point the word 'sensor' in in the 'readout file'
'*******	the format of the script need to changethe way it reads the file. 
'*******	
			
				case "Sensor"
					tog_sens = true
				case else
			end select
		end if
	end if
loop
'*******	
'*******	close all the documents and send a 'done' message
'*******	
r_File.close
w_File.close
msgbox "done"

Open in new window

0
William ElliottSr Tech GuruCommented:
here is a simple sql connection with some simple examples of how to use it

 
msgbox sqlcon("insert into mytable(col1, col2) values(val1,val2)")
msgbox sqlcon("select * from mytable")
 
function sqlcon(strSQL)
Set adoConnection = CreateObject("ADODB.Connection")
strconnect = "DRIVER={SQL Server};Server=MIKEMAIN\SQLEXPRESS;Database=UPCwind_dev;"
adoConnection.Open strConnect
adoConnection.execute strSQL
adoConnection.close
end function

Open in new window

0
chem_kidAuthor Commented:
Can you just answer this and I'll close this?

and to fill the values going into the sql table they would be:

values(strMetID, strLoggerSN, strSensor, etc.)?

if I use those as the values then the table should populate?

I cannot tell you how much this has helped me.  After you commented everything out I totally understand the parser/ extractor now.



0
William ElliottSr Tech GuruCommented:
here is the script to write to sql.
'*******
'*******	Declare stuff
'*******
 
Const ForReading = 1
Const ForWriting = 2
strFileName = "C:\4301configexample.txt"
strnewFileName = "C:\4301configexample_d.txt"
'*******
'*******	Create fso for making files
'******* 
Set objFSO = CreateObject("Scripting.FileSystemObject")
'*******
'*******	create and open text files for reading and writing
'*******
Set r_File = objFSO.OpenTextFile(strFileName, ForReading)
Set w_File = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
 
'*******
'*******	Disable toggle_sens
'*******
tog_sens = False
'*******
'*******	Start reading text file
'*******
Do Until r_File.AtEndOfStream
'*******
'*******	read each line one by one into the strtext variable
'*******
	strText = r_File.Readline
'*******
'*******	if line is empty ignore it
'*******
	if strtext <> "" then
'*******
'*******	If Tog_Sens is true, thendo the top portion, else do the bottom
'*******
		if tog_sens = true then
'*******
'*******	if tog_sens is true, then split the text into an tabbed array
'*******	then read the array into variables
'*******
			myarray = split(strText, vbtab)
			strSensor = myarray(0)		
			strChanel = myarray(1)
			strType = myarray(2)
			strSlope = myarray(3)
			strOffset = myarray(4)
			strUnit = myarray(5)        
'*******	
'*******	now write those variables in the write file
'*******
 
			w_File.writeline strmetID & ", " & _
							 strloggerSN & ", " & _
							 strSensor & ", " & _
							 strChanel & ", " & _
							 strType & ", " & _
							 strSlope & ", " & _
							 strOffset & ", " & _
							 strUnit
'*******	
'*******	Now that you have your values, you can write them to SQL
'*******	
'*******	Assuming you have these fields (metID, loggerSN, Sensor, Chanel, Type, Slope, Offset, Unit)
'*******	and this database name (tmpSensorDump)
'*******	
'*******	sqlcon is the function name
'*******	everything between the quotes is the connection string.
'*******	
							 
			sqlcon( _
				"insert into tmpSensorDump (metID, loggerSN, Sensor, Chanel, Type, Slope, Offset, Unit)" & _
				"values (" & strmetID & ", " & strloggerSN & ", " & strSensor & ", " & strChanel & ", " & _
				strType & ", " & strSlope & ", " & strOffset & ", " & strUnit & ")" _
				)
		else 
 
'*******	
'*******	If Tog_Sens is false, then 
'*******	check the 6 characters to the left of the strtext variable
'*******
			select case left(strText,6)
'*******
'*******	if left 6 characters are MetID: then 
'*******	split the text and get the information after the 2nd tab and
'*******	write it to the variable
'*******
				case "MetID:"
					met_ary = split(strText, vbtab)
					strmetID = met_ary(2)
					
'*******	if left 6 characters are logger then 
'*******	split the text and get the information after the 1st tab and
'*******	write it to the variable
				case "Logger"
					log_ary = split(strText, vbtab)
					strloggerSN = log_ary(1)
					
'*******	if left 6 characters are sensor then 
'*******	set tog_sens = true
'*******	this will cause the script to ignore this second part of the if statement
'*******	we do this because after the point the word 'sensor' in in the 'readout file'
'*******	the format of the script need to changethe way it reads the file. 
'*******	
			
				case "Sensor"
					tog_sens = true
				case else
			end select
		end if
	end if
loop
'*******	
'*******	close all the documents and send a 'done' message
'*******	
r_File.close
w_File.close
msgbox "done"
 
 
 
function sqlcon(strSQL)
Set adoConnection = CreateObject("ADODB.Connection")
strconnect = "DRIVER={SQL Server};Server=MIKEMAIN\SQLEXPRESS;Database=UPCwind_dev;"
adoConnection.Open strConnect
adoConnection.execute strSQL
adoConnection.close
end function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chem_kidAuthor Commented:
Sorry I took so long to close this.  Your help has been invaluable.  Now I understand finally.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.