Writing a complex excel to a table using SAP Data Services

When I was using SAP Data Services 4.2.13 last year, I faced some challenges in loading some data from some “Unstable excel files”. These excel files had some strange formats and, it was required to fetch data from those files even when sometimes the columns were coming swapped or missing.

To write an excel to a table using SAP Data Services in cases where the excel is somehow “complex” you can use Python in a user-defined transform.

If you are familiar with some programming code (if you know Python better) this tutorial will help you with this task.

So let’s do it!

Pre-requirements

Remember that the Python you are using depends on what you have installed in your system. It might be Python 2 in the old versions or Python 3. Make sure you have Python 3 installed on the Data Services server otherwise you will have a lot of issues with text fields.

Be sure you have all the libraries needed. probably you will need to have a good bases to support you on this if you are not familiar with pip or if you don’t have the right authorizations to install them on the server.

Creating the Logic

The first step is to define your structure and flow.

Programing in your User-Defined transform

Keep all of your logic to read the excel and identify the columns you need.

Then use highlighted lines of code to write to output.

You will also need to define the output columns in the UDT editor by clicking on the “Launch Python Editor …” button.

Then on the left side click I/O Fields and Output Fields right click and hit “Insert…” menu item.

Do this for each of your output columns (in the example gave 6 of them)

Then in the Output tab of the main UDT editor screen, select the 6 columns and they will be mapped to Schema Out.

You will need to possibly call the AddRecord function in a loop, once for each row in the excel.

Code used

import csv
import xlrd
#import xlsxwriter
#from xlwt import Workbook
from os import sys

newRec = DataManager.NewDataRecord()
Collection.GetRecord(newRec, 1)
path = newRec.GetField(u’FILE_PATH’) + u’\\’
print path

workbook = xlrd.open_workbook(path + u’Master_Sheet.xlsx’)
worksheet = workbook.sheet_by_name(u’Learning Rooms’)

#wb = xlsxwriter.Workbook()
#wb = xlsxwriter.Workbook(path + u’new_Master_Sheet.xlsx’)
#sheet1 = wb.add_worksheet(‘first_sheet’)
for row_index in xrange(worksheet.nrows):
newRecord = DataManager.NewDataRecord(1)
for col_index in xrange(worksheet.ncols):

if worksheet.cell(0,col_index).value == u’Learning Room Name ‘:
newRecord.SetField(u’Learning Room Name’, worksheet.cell(row_index,col_index).value)

#sheet1.write(row_index,new_col_index,worksheet.cell(row_index,col_index).value

if worksheet.cell(0,col_index).value == u’Jam Group ID (required by KTE Reporting Services)’:
newRecord.SetField(u’Jam Group ID’, worksheet.cell(row_index,col_index).value)

if  worksheet.cell(0,col_index).value == u’Learning Room Type’:
newRecord.SetField(u’Learning Room Type’, worksheet.cell(row_index,col_index).value)

if  worksheet.cell(0,col_index).value == u’Learning Room Technical ID’:
newRecord.SetField(u’Learning Room Technical ID’, worksheet.cell(row_index,col_index).value)

if  worksheet.cell(0,col_index).value == u’Region Requester ‘:
newRecord.SetField(u’Region Requester’, worksheet.cell(row_index,col_index).value)

if  worksheet.cell(0,col_index).value == u’Status’:
newRecord.SetField(u’Status’, worksheet.cell(row_index,col_index).value)

#col_index = col_index +1
Collection.AddRecord(newRecord)
del newRecord
#print new_col_index

#wb.close()
#new_workbook = xlrd.open_workbook(path + u’new_Master_Sheet.xlsx’)
#new_worksheet = new_workbook.sheet_by_name(u’first_sheet’)

#with open(u'{}’.format(path + u’Master_Sheet.csv’), u’wb’) as csvfile:
#  writetocsv = csv.writer(csvfile, quoting = csv.QUOTE_ALL)

#  for rownum in xrange(new_worksheet.nrows):
#    writetocsv.writerow([unicode(x).encode(u’utf-8′) for x in new_worksheet.row_values(rownum)])

#    writetocsv.writerow(
#      list(x.encode(u’utf-8′) if type(x) == type(u”) else x for x in worksheet.row_values(rownum))
#    )

#csvfile.close()
del newRec
del path
del workbook
del worksheet

I hope this is useful to you guys. Let me know in the comments if you have any questions and your findings and what yet what kind of post you would like to see here.

Happy coding!