Read from Excel File and execute DB Update
Wondering if I can get some assistance with creating a new job in HP OO v10?
I would like the job to read an excel file and execute an SQL based on the information from each row.
Basically, the excel file will include multiple distinct rows of First Name, Last Name, Employee Number.
I would like to convert the above into variables and run the SQL update for each row.
The SQL update will take the First Name, Last Name, Employee Number and place them into the variables assigned to them.
Currently we are manually entering First Name, Last Name, Employee Number for the DB component, but would be preferrable if we can loop through each row of the provided spreadsheet.
Hope that makes sense!
In the base content pack there are excel operations and part of those there is Get Cell where you can get the contents of a cel by providing the row and column index. Using this you are then able to create small loops that get all the data of a row through careful indexing.
As an alternative there is always powershell, however in order to go for the powershell route excel needs to be installed on the host where the excel document is located. (an example of how to do it would be: http://stackoverflow.com/questions/16615364/read-excel-data-with-powershell-and-write-to-a-variable )
Hope this helps,