Our Articles

blogImage
VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

This VBS script is easy to use and read.  It loops through the tabs in the spreadsheet and saves each sheet as a “Tab Delimited File”.  The script also creates the corresponding BCP command to upload to your Sybase Server.  You can modify if needed to suit another database platform.

 

‘====================================
‘Date Created: 2012_08_11
‘Author: Lawrence Patrick | Email: [email protected]
‘Purpose: Automate the creation of bcp command and tab delimited file from any spreadsheet
‘Notes: Exports one sheet or multiple sheets by looping and grouping the results.
‘ Also Creates a BAT file with a command to run the BCP load
‘Usage:
‘ 1. Set the variables(Servername/Username/Password/Database) in the script.
‘ 2. Drag your spreadsheet on to the VBS Script, 2. Edit the BAT file (BCP command) that was generated.
‘ 3. Inspect the bcp command in the bat file – if all looks good, execute the batch file.
‘====================================

Server = “srv1″ ‘ Syabase Server
UserName = “jdoe” ‘ Database User
Password = “Password” ‘ Database User Password
Database = “db1″ ‘DB’

WScript.Echo “Starting Script…”
WScript.Echo “‘=====================================” & Chr(13) & Chr(10) & “‘=koverus.com ” & Chr(13) & Chr(10) & “‘=Date Created: 2012_08_11 ” & Chr(13) & Chr(10) & “‘=Author: Lawrence Patrick ‘ ” & Chr(13) & Chr(10) & “‘=Email: [email protected] ” & Chr(13) & Chr(10) & “‘=Notes: Creates a BAT file with a command to run the BCP load ” & Chr(13) & Chr(10) & “‘=Usage: ” & Chr(13) & Chr(10) & “’1. Set the variables(Servername/Username/Password/Database). ” & Chr(13) & Chr(10) & “’2. Drag your spreadsheet on to the VBS Script,” & Chr(13) & Chr(10) & “’3. Edit the BAT file (BCP command) that was generated. ” & Chr(13) & Chr(10) & “’4. Inspect the bcp command in the bat file & execute the batch file. ” & Chr(13) & Chr(10) & “‘=====================================”

Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count – 1 ‘ This loops through all the workbooks in the directory (check)

FullName = objArgs(I) ‘ Pick up the arguement (the Fully Qualified Name of the file being dragged onto the script)
FileName = Left(objArgs(I), InstrRev(objArgs(I), “.”) ) ‘Get the short name of the file

Set objExcel = CreateObject(“Excel.application”) ‘ create an excel object
set objExcelBook = objExcel.Workbooks.Open(FullName) ‘ pass the book to excell

objExcel.application.visible=false ‘ No need to launch the app
objExcel.application.displayalerts=false ‘ turn off those anoying security alerts

‘First File Open
Dim e ‘ counter variable
For e = 1 to objExcelBook.Worksheets.Count ‘ loop – through sheets
Dim sName : sName = objExcelBook.Worksheets( e ).Name
‘ Dim nPos : nPos = Instr( sName, sNeedle )
‘Dim bFound : bFound = 0 < nPos
‘WScript.Echo e, sName, CStr( bFound )

Dim objFS, objFile ‘ Generic objects (“FS” File System, “File” Text File)
Set objFS = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFS.CreateTextFile( FileName & “_” & sName & “_bcp_command.bat”)
‘can substitute with environment variables
objFile.WriteLine(” pause C:SYBASEOCS-15_0″ & “binbcp ” & Database & “..” & sName & ” IN ” & FileName & “xls” & ” -c -e ” & FileName & sName & “_.err -U ” & UserName & ” -P “& Password & ” -S ” & Server)

‘can easily update to interactive, process, including the loading of the data
‘right now, generating the individual files and commands is ok

Set objFS = Nothing ‘ disposing object
set objFile = Nothing ‘ disposing objet
Set objSheet = objExcelBook.Sheets( sName )
objSheet.Activate
‘ objExcelBookSheet = objExcelBook.Worksheets( e )
‘ Save the sheet as a tab delimited file
‘ WScript.Echo ” Created BCP command for” & sName
objExcelBook.SaveAs FileName & sName & “_tdf”, -4158 ‘tab delimited file
‘ WScript.Echo sName & ” Saved as Tab Delimited File”

Next
objExcel.Application.Quit
objExcel.Quit

Set objExcel = Nothing
set objExcelBook = Nothing
Next
WScript.Echo ” Script Complete. ”

 

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
VBScript - Loop through Excel Sheets Export as Tab Delimited File, 5.0 out of 5 based on 1 rating

Lawrence Patrick

www.lawrencepatrick.net

So, what do you think ?

You must be logged in to post a comment.