To convert an Excel file to CSV and then move that data from CSV to oracle database.

[jobs123]

Hello All,

I have an excel sheet which I want it to be converted to CSV and move that data from CSv to oracle databse. I have a Vbscript that converts excel to CSV but the way in which my excel sheet is formatted it is not coming up properly. There are some cells which I dont require and want them to be skipped and only the headings and their respective data should be coming up onto the CSV file. I am attaching the excel sheet as .txt extension please change it to .xls as the editor is not allowing me to upload a xls file and also the screenshot of that excel sheet showing the cells and the text needed to be skipped (highlighted in a red box). Please help me with this to have the CSV file by skipping these data and then load that data to oracle database in two tables where in one table will have data from some rows in CSV and another table will have a row of data from that CSV file.

Here is the VBscript that I have been using.

WorkingDir = "C:\Users\vijaywp\Desktop"
Extension = ".XLS"

Dim fso, myFolder, fileColl, aFile, FileName, SaveName
Dim objExcel,objWorkbook

Set fso = CreateObject("Scripting.FilesystemObject")
Set myFolder = fso.GetFolder(WorkingDir)
Set fileColl = myFolder.Files

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False
objExcel.DisplayAlerts= False

For Each aFile In fileColl
ext = Right(aFile.Name,4)
If UCase(ext) = UCase(extension) Then
'open excel
FileName = Left(aFile,InStrRev(aFile,"."))
Set objWorkbook = objExcel.Workbooks.Open(aFile)
SaveName = FileName & "csv"
objWorkbook.SaveAs SaveName, 23
objWorkbook.Close 
End If  
Next

Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set myFolder = Nothing
Set fileColl = Nothing

I am attaching the generated CSV file (please change the extension from txt to csv) with this script.

Please help me with this.

Thanks in advance.