Highlighted
jcullen
New Member.
729 views

Read from Excel File and execute DB Update

Hi Guys,

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!

Thanks

Labels (1)
Tags (1)
0 Likes
2 Replies
Micro Focus Expert
Micro Focus Expert

Re: Read from Excel File and execute DB Update

Hi,

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,

Vlad

0 Likes
jcullen
New Member.

Re: Read from Excel File and execute DB Update

Hi Vlad,

Thanks for your response!

I'll have a look at the mentioned solutions.

Jeremy


0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.