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



## geek123 (Dec 8, 2011)

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 and also the screenshots 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.


```
[COLOR=#2B91AF]WorkingDir[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"C:\Users\vijaywp\Desktop"[/COLOR][COLOR=#2B91AF]Extension[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]".XLS"[/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#000000] fso[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myFolder[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] fileColl[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] aFile[/COLOR][COLOR=#000000],[/COLOR][COLOR=#2B91AF]FileName[/COLOR][COLOR=#000000],[/COLOR][COLOR=#2B91AF]SaveName[/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#000000] objExcel[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000]objWorkbook  [/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] fso [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#2B91AF]CreateObject[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Scripting.FilesystemObject"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] myFolder [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] fso[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]GetFolder[/COLOR][COLOR=#000000]([/COLOR][COLOR=#2B91AF]WorkingDir[/COLOR][COLOR=#000000])[/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] fileColl [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] myFolder[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Files[/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] objExcel [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#2B91AF]CreateObject[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Excel.Application"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]  objExcel[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Visible[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]False[/COLOR][COLOR=#000000] objExcel[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]DisplayAlerts[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]False[/COLOR][COLOR=#2B91AF]For[/COLOR][COLOR=#2B91AF]Each[/COLOR][COLOR=#000000] aFile [/COLOR][COLOR=#2B91AF]In[/COLOR][COLOR=#000000] fileColl ext [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#2B91AF]Right[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]aFile[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Name[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000])[/COLOR][COLOR=#2B91AF]If[/COLOR][COLOR=#2B91AF]UCase[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]ext[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#2B91AF]UCase[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]extension[/COLOR][COLOR=#000000])[/COLOR][COLOR=#2B91AF]Then[/COLOR][COLOR=#800000]'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[/COLOR]
```
I am also attaching the CSV file being generated (change the extension from txt to csv) with this script.

Please Help.

Thanks in advance.


----------



## draceplace (Jun 8, 2001)

The code below allows you to drag and drop an excel file on it. Then it searches for the specific worksheet and saves it as a csv in a specific location. I tested with the "Sample Sub Categories" worksheet and it works fine.


```
'---------------------------------------------------------------
'-- Dan McCracken 09/03/2010----------------------------------------
'--This script finds a specific tab and saves xls to csv '
'--simply drag and drop the files onto this vbs to use.
'--This finds a specific tab and save .csv for next process
'--strFile MUST = the folder you want your file to land in.
' Added oSheet.Cells.ClearFormats to get rid of commas and $
' ---------------------------------------------------------------
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1

    FullName = objArgs(I)
    FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )

    Set oExcel = CreateObject("Excel.application")
    set oSheet = oExcel.Workbooks.Open(FullName)
    'MUST be slash and the end of strFiles
 
strFiles = "H:\A1\"
'strFiles = "\\serverName\dco_reporting\"
  
       oExcel.application.visible=false
    oExcel.application.displayalerts=false
             
        For Each oSheet In oExcel.Worksheets        
          wscript.echo osheet.name
        
            If osheet.name = "Sample Sub Categories" Then
   	          oSheet.Cells.ClearFormats
	          oSheet.SaveAs strFiles & oSheet.Name & _
            	"." & "csv", 6
           		      wscript.echo oSheet.Name
 	
            End If
        Next      
   
    oExcel.Application.Quit
    oExcel.Quit   
'Clean up your mess  
    Set oExcel = Nothing
    set oSheet = Nothing

Next
```


----------



## geek123 (Dec 8, 2011)

Hello Dan,

Thank you so much for the script. But is there a way that I can remove the null cells. I mean if you notice in that excel sheet there are some spaces in between some cells and those are also coming up onto the CSV file separated by the commas. Please check the attached screenshot of the CSV file when I open it in a Text editor(like ultraedit). So how can we remove the blank spaces? 

Also as I mentioned in previously, I need to have this CSV file data imported to tables in Oracle database. Can you please let me know about that as well?

Please let me know what to do.

Thank you so much for your help.


----------



## draceplace (Jun 8, 2001)

The blank cells is what is your data is. Removing them will destroy any table structure you might accomplish importing to a Oracle, SQL or whatever database. Converting this to CSV isn't going to buy you much in the hopes of making a meaningfull table in a database. This is a classic example of garbage in garbarge out.

To accomplish whatever it is your trying to do (an example of the desired output might clarify things) you've got two choices. I can't offer much help on the actuall importing of data to Oracle but I do know that any database table has to have a predetermined structure which your input file seems to be dramatically lacking (as presented).
1. Try to figure out how to programatically massage this data to be something to import into table.
2. Come up with a better input format to work with.

If your stuck using this then you'll need to open the desired tab and read it line by line and customize the code to handle the massaging of the output.


----------



## geek123 (Dec 8, 2011)

Hello Dan,

Thanks for the info. I will try and get the input file in a proper format to work with. Then can you please let me know how can we go about inserting that into a table of oracle database.


----------



## draceplace (Jun 8, 2001)

I can't help much with Oracle. Execpt to say it will want a correctly formatted input. I'm not saying the current format is unworkable just saying there a lot of work to get it there and the example you provided wasn't a good test file.


----------



## geek123 (Dec 8, 2011)

Hello Dan,

I have this excel sheet now formatted in a proper way. Can you please look at it and tell me if you have any idea on connecting this to databse tables.

Thanks in advance.


----------



## draceplace (Jun 8, 2001)

I was off work yesterday and will spend today catching up. Maybe look at it Friday. Others may help if they so desire!


----------



## Rollin_Again (Sep 4, 2003)

You can download and install the Oracle client on your PC and then either use the free SQL Loader utility to add the data or you can use an Excel macro to loop through the original worksheet and add the records to the database one at a time by executing a SQL statement for each row of data. 

You can also use a 3rd party database tool such as TOAD, PL/SQL Developer, or SQL Navigator to do the import but they are licensed software and can be very expensive. How many total records are we talking about adding to the table?

Rollin


----------



## geek123 (Dec 8, 2011)

Hello,

I already checked SQL loader utility but due to some restrictions in my organisation, I am unable to use it. I get an error like SQL command not found. Can you please tell me how can we achieve it using an excel macro as I am not having sufficient knowledge on it. 

And regarding the total records , it may depend because for every program we get a different excel sheet which might have less rows or more number of rows. But maximum it would not exceed 200 or so.

Please help.

Thanks in advance.


----------



## draceplace (Jun 8, 2001)

All this is going to require support from your oganization. I've never done this from Excel but I assume Excel is going to to want to execute sql as well. Who ever controls the databases and security for those should be working with you or providing some support.

Looking at the data, I don't understand what your trying to load into this database. Maxtrix, intructions, categories? None of which looks to be data for a table.


----------



## Rollin_Again (Sep 4, 2003)

I do this almost daily from Excel using macros and as Drace indicated you will need some support from your organization to make this happen. At minimum you'll need the following:

1) A login to the database with modify permissions on the table

2) Oracle Client installed on the machine you will connect from (installing the client will require local admin rights on the client machine) 

3) You will need the database definition info (Database hostname or IP address, and port number)


Rollin


----------



## geek123 (Dec 8, 2011)

Hello,

I can login to database and have the modify permissions on the table and all the required database definition info. How can I proceed writing a VBA in excel.

And for the data to be inserted into the database, I am attaching the same excel sheet which I attached before but now with highlighting the columns and their respective data. The column headings are highlighted in yellow and their respective data is in red. The first two columns Type and Date will be inserted only once into te table, I mean this two values will be constant for all the other values being inserted into the other columns. Please have a look at it and tell me how can this functionality be achieved. Please help.

Thanks again.


----------



## geek123 (Dec 8, 2011)

Can someone please help.

Thanks.


----------



## Rollin_Again (Sep 4, 2003)

Have you installed the Oracle client yet? This is the first step.

Also do you have the database IP Address and port number?


Rollin


----------



## geek123 (Dec 8, 2011)

Yes


----------



## Rollin_Again (Sep 4, 2003)

The first thing you want to do is to try to open the database connection using the code below. The parts highlighted in red are the items you need to change. XXXX will be replaced with the database name (as it is defined in your Oracle tnsnames.ora file) The username and password is obviously what you would log into the database with. Once you get the connection part sorted we'll add the code to do the table updates. If you need more assistance it might be easier to try to do a web conference where you can share you desktop so I can see the file paths and make sure everything is configured correctly.


```
Dim oraSession  As Object
Dim oraDatabase As Object

    Set oraSession = CreateObject("OracleInProcServer.XOraSession")
    Set oraDatabase = oraSession.DBOpenDatabase("[COLOR="Red"]XXXX[/COLOR]", "[COLOR="Red"]USER/PASSWORD[/COLOR]", 0&)
    
     
    Set m_oraDatabase = Nothing
    Set m_oraSession = Nothing
```
Rollin


----------



## geek123 (Dec 8, 2011)

Thanks Rollin for the code snippet. Can you please tell me about the code for the table upadtes now so that we can proceed.

Thank you so much.


----------



## Rollin_Again (Sep 4, 2003)

Have you tested the database connection portion yet? 

I'm still a bit unclear on what exactly needs to be updated or added in the database. Are you familiar enough with SQL statements that you can provide the exact SQL statements that need to be run or do need help writing the insert/update statements?

Rollin


----------



## geek123 (Dec 8, 2011)

Hello Rollin,

I am familiar with the insert/update statements. We can go ahead for the rest of the part of the code.
Thank You so much.


----------



## Rollin_Again (Sep 4, 2003)

You just need to add two more lines of code. The first line will be the actual Insert/Update statement and the second line is where your database object executes the SQL

Here is a simple example showing both an update and insert. When updating or inserting into a column that is type varchar (string) you need to enclose the value to update/insert in single quotes. You can do this by either enclosing each single quote between it's own set of double quotes or just wrap the value to insert with *&chr(39)* in your SQL like I've done in my example below.



> vSQL = "UPDATE myTable set myColumn = " & chr(39) & "TEST" & chr(39) & " where myRecordNumber = " & 5
> oraDatabase.ExecuteSQL (vSQL)
> 
> vSQL2 = "INSERT into myTable (column1, column2) values (" & chr(39) & "TEST1" & chr(39) & "," & chr(39) & "TEST2" & chr(39) & ")"
> oraDatabase.ExecuteSQL (vSQL2)


If you are inserting values directly from the Excel worksheet you simply need to replace the hard-coded values below (TEST, TEST1, TEST2) with the cell references.



> vSQL = "UPDATE myTable set myColumn = " & chr(39) & Sheet1.Range("A1").Value & chr(39) & " where myRecordNumber = " & 5
> oraDatabase.ExecuteSQL (vSQL)
> 
> vSQL2 = "INSERT into myTable (column1, column2) values (" & chr(39) & Sheet1.Range("A1").Value & chr(39) & "," & chr(39) & Sheet1.Range("A2").Value & chr(39) & ")"
> oraDatabase.ExecuteSQL (vSQL2)


Rollin


----------



## geek123 (Dec 8, 2011)

Thanks Rollin.

I will check this and let you know if it works.

Thanks a lot.


----------

