?
Solved

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

Posted on 2015-02-14
5
Medium Priority
?
67 Views
Last Modified: 2016-02-10
????


============ edited by aikimark
0
Comment
Question by:AletsPalas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 40610583
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.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 40610677
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"
		Wscript.Quit
	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


		else
		  'proceed with archive

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

		  
	  end if
		  
	Loop
	
	objFile.Close
	


Wscript.Echo "--done--"

wscript.quit


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
	UpdateOK=TRUE
	AdminArchivedSwitch=1
	DesignArchivedSwitch=1
	AdminNo = ArchiveNum
	DesignNo="ARC_DRV"

'--------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
	 else
		InfobaseArchive = false
	end if

End function

Open in new window


security is poor (passwords in clear text) as this is an internal script.
0
 

Author Comment

by:AletsPalas
ID: 40614530
It's great!.

It's useful for  me.

Thank you very much
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40615223
please accept answer and close question if it is solved.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question