Getting Data In

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

Nanuk
Explorer

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.

0 Karma

Nanuk
Explorer

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
0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...