How read text files with tab separate and insert into sqlserver from vba


============ edited by aikimark
Who is Participating?
Robberbaron (robr)Connect With a Mentor Commented:
ive done something similar before that may help though it doesn't have much doco

'Needs reference to FileSystemObject, ADODB

and all the database details have been changed to protect the guilty....

Option Explicit
Dim strScriptName 
Dim strScriptDir
Dim strXlsName
Dim strRemoveName

'Needs reference to FileSystemObject, ADODB
dim  arc_list ="myfile.txt"	

	Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
	if not objFSO.FileExists(arc_list) Then
		Wscript.Echo "Input file not found"
	end if
	Dim objFile: Set objFile = objFSO.OpenTextFile(arc_list)
	Dim RawString, arclistdata
	Do While Not objFile.AtEndofStream
	  'On Error Resume Next
	  RawString = objFile.ReadLine
	  'split at TAB characters
	  arclistdata = split(RawString,vbTab)
	  if ubound(arclistdata)<>6 then
		  'bad data line

		elseif NOT isnumeric(left(arclistdata(0),5)) or NOT isnumeric(arclistdata(1)) then

		  'proceed with archive

		  if cmdrun then wscript.echo "Archive ==" & arclistdata(0) & "==" & arclistdata(1) & "=="
		  ok = ArchiveX ( arclistdata)

	  end if

Wscript.Echo "--done--"


Function ArchiveX ( TabList )
    'open connection

	dim SQLquery , recA
	dim UpdateOK
	dim AdminArchivedSwitch , DesignArchivedSwitch
	dim AdminNo , DesignNo
	dim InfoBase_DataConnection
	dim Infobase_dataconnection_ConnectionString,	Infobase_dataconnection_ConnectionTimeout 
	dim Infobase_dataconnection_CommandTimeout ,	Infobase_dataconnection_CursorLocation 
	dim Infobase_dataconnection_RuntimeUserName ,	Infobase_dataconnection_RuntimePassword 
	InfobaseArchive = false    'set default return
	AdminNo = ArchiveNum

'--------from Infobase Global.ASA
	Infobase_dataconnection_ConnectionString =  "DRIVER=SQL Server;SERVER=BWSQL;APP=Arc_job;WSID=SQLSERVER;DATABASE=Test123;AnsiNPW=No;"
	Infobase_dataconnection_ConnectionTimeout = 15
	Infobase_dataconnection_CommandTimeout = 30
	Infobase_dataconnection_CursorLocation = 3
	Infobase_dataconnection_RuntimeUserName = "qwerty"
	Infobase_dataconnection_RuntimePassword = "qaz"
	'update the project archive details
	'v4.1  update all master and suffix projects with same data.
	SQLquery="UPDATE Project_Archiving SET " &_
			"AdminArchived=" & TabList(0) & ", " &_
			"AdminArchiveNo='" & TabList(1) & "', " &_
			"DesignArchived=" & TabList(2) & ", " &_
			"DesignArchiveNo='" & TabList(3) & "', " &_
			"ShedStorageNo='----' " &_
			"WHERE ProjectNo LIKE '" & TabList(6) & "%';"  
	Set InfoBase_DataConnection = CreateObject("ADODB.Connection")
	InfoBase_DataConnection.ConnectionTimeout = InfoBase_DataConnection_ConnectionTimeout
	InfoBase_DataConnection.CommandTimeout = InfoBase_DataConnection_CommandTimeout
	InfoBase_DataConnection.Open InfoBase_DataConnection_ConnectionString, InfoBase_DataConnection_RuntimeUserName, InfoBase_DataConnection_RuntimePassword

	Infobase_DataConnection.Execute SqlQuery, RecA

	if RecA > 0 then
		'one record updated as expected
		InfobaseArchive = RecA
		InfobaseArchive = false
	end if

End function

Open in new window

security is poor (passwords in clear text) as this is an internal script.
That's right! More description is required to limit our assumptions. What is your programming environment?
Include a demo text file and and the expected output in sql server.
AletsPalasAuthor Commented:
It's great!.

It's useful for  me.

Thank you very much
Robberbaron (robr)Commented:
please accept answer and close question if it is solved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.