shell script for converting a foxbase .DAT file to (comma delimited) .CSV using excel

Nanuk — Wed, 11 Jan 2017 07:20:37 GMT

Hello,

I'm kind of new to this, so please bear with me. I have been trying to make a shell script that can do the following:

convert a FoxBase v3.0 .DAT file into a comma delimited .csv file.

I decided to go with a VBScript and this the code that I have so far. So far what I've come up with is a corrupt .csv file. When I add it to Splunk! it reads as corrupted and not a (comma delimited) csv file. I was hoping to get some pointers.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("D:\_Work\_Splunk\Villarica\LC8.DAT")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True

objExcel.ActiveWorkbook.SaveAs "D:\_Work\_Splunk\Villarica\LC8.CSV"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Even if you don't have an answer thanks for taking a look.

Re: shell script for converting a foxbase .DAT file to (comma delimited) .CSV using excel

Nanuk — Fri, 13 Jan 2017 07:59:18 GMT

I was able to figure out my mistake I'll post the correct code below.

'Declaring Constants
Const ForReading = 1, ForWriting = 2, ForAppending = 3
'Declaring Variables
dim fso, strDirectory, strOutPut, fLocation, fNewName, objWorkbook, local
local = true

Set objExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = False

'Open Text Files for use
Set strDirectory    = fso.OpenTextFile("D:\_Work\_Splunk\_TestBed\DATFileLocations.txt", ForReading, TristateFalse)
Set strFileName     = fso.OpenTextFile("D:\_Work\_Splunk\_TestBed\csvFileNames.txt", ForReading, TristateFalse)

Do Until strDirectory.AtEndOfStream
  fLocation = strDirectory.ReadLine     'Get the location of the DAT file
  fNewName = strFileName.ReadLine       'Get the name of the target csv file
  Set objWorkbook = objExcel.Workbooks.Open(fLocation) 'Open the DAT file
  objExcel.ActiveWorkbook.SaveAs fNewName, 6    'Save the DAT file as a csv
  objExcel.ActiveWorkbook.Close
Loop

'Close the text files'
strDirectory.Close
strFileName.Close
'Set strDirectory = Nothing
'Set strFileName = Nothing

topic shell script for converting a foxbase .DAT file to (comma delimited) .CSV using excel in Getting Data In

shell script for converting a foxbase .DAT file to (comma delimited) .CSV using excel

Re: shell script for converting a foxbase .DAT file to (comma delimited) .CSV using excel