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.

[westconn1]

as the editor is not allowing me to upload a xls file

zip first

as you only want to save specific ranges, not the entire sheet you need to write the data to a text (.csv) file as required, using file io
rather than using excel saveAs method
there are several methods to do this, the simplest being like

f = freefile
open "fullpath\somfile.csv" for output as f
' print required data in csv format here
close f

or you can use FSO if you prefer

[jobs123]

Hello westconn,

Thanks for replying. Thing is I am not having sufficient knowledge on file system in VBscript. How should I include this code you mentioned above in my code which I have posted.

I guess you might have checked the CSV file that I have atatched so what exactly needs to be given after the open statement in your code snippet.

Please let me know.

Thanks a lot in advance.

[westconn1]

based on your files, for numbers of rows and columns, try like

for rw = 1 to 55
  for col = 1 to 14
     write #f, cells(rw, col) & ",";
  next
  write #f, cells(rw, 15)
next

sorry, my mistake, i thought you were doing this within excel
in VBS, you will need to use FSO, as file io is not available
the above loop would be similar, but using FSO

[jobs123]

Hi westconn,

How can we have FSO object created within that loop. I am not having much idea on it. Just wanted to know where in my code I have to include this code snippet and use FSO.

Please help.

Thank you so much for your support.

[westconn1]

Just wanted to know where in my code I have to include this code snippet and use FSO.

in place of

objWorkbook.SaveAs SaveName, 23

[jobs123]

Westconn,

I am getting an error when I place your code in place of objWorkbook.SaveAs SaveName, 23. I guess it is because of not using FSO. How to do it using FSO? Here are the screenshots for the modified code and the error.





Please check and let me know what to do about it.

[jobs123]

I am still getting the same error can you please help.

[westconn1]

i do not generally use fso, but try like

Set f1 = fso.CreateTextFile(savename, True)
For rw = 1 To 55
  For col = 1 To 14
     f1.Write objWorkbook.Sheets(1).Cells(rw, col) & ","
  Next
  f1.Write objWorkbook.Sheets(1).Cells(rw, 15) & vbNewLine
Next
f1.Close

if the range in the csv is not correct, just change rw and col to suit
col first col to last col -1, then the last col in the second write line
if you need to quotes around your csv values, you will need to change to

f1.write """" & objWorkbook.Sheets(1).Cells(rw, col) & ""","

you will need to do this it your cells could contain commas
similar for the second write line

[jobs123]

Hi,

I have formatted the excel sheet in a more proper way. Please tell me how can I connect this to oracle database table and the load that data into it.

Please help.

[westconn1]

Please tell me how can I connect this to oracle database table

add a reference to ADO (microsoft axtivex data object)
go to connectionstrings.com to find the correct connection for oracle