VBScript – Loop through Excel Sheets Export as Tab Delimited File

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

 

 

Leave a Reply