need to import all these fields from text file into database.

Please help me on this one.

I need to import a bunch of field along with their values from a basically non-delimited text file into a ms sql database i have set up with the appropriate tables and fiiled.  The db is set up.  I need to write the code using vb.net to import that file in.

I am clueless here.  Please help me.  I have asked this question before, and got some good answers but I was not telling the whole story, so I included the entire text file here.

Note:  There are like 2 parts to it.  First is the part with the field names followed by the values corresponding to each on a separate line.  that is fairly simple, although I do still need the code for importing that please.  

The second part is the problem.  You will notice a huge table.  there is a datetimestamp field, and then a bunch of different fields that will fill different tables which hold channel data in relation to the datetime.  This is where I am clueless.  Please help.

Please be easy on me here.  I am not really a beginner.  I have 7 years of VB experience, but not much .net.  I haven't done anything quite like this though.

askaboutdata.txt
chem_kidAsked:
Who is Participating?
 
William ElliottConnect With a Mentor Sr Tech GuruCommented:
didn't i already do this?
her is an updated version
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
 
David ToddSenior DBACommented:
Hi,

The second part could be easier depending on where your comfort zone is.

Given that the data is tab separated, it should be relatively easy to import with the BULK INSERT statement.

Look up the BULK INSERT statement in SQL BOL.

Cheers
  David
0
 
chem_kidAuthor Commented:
could someone help me out with the first part at least?  
0
All Courses

From novice to tech pro — start learning today.