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?
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.

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
William ElliottSr 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

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
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
.NET Programming

From novice to tech pro — start learning today.