Loading file with multiple long text fields

dataservices job design
1

Hi

I’m trying to load a large excel file to through Data Services. The file which has a lot of columns, 179, and about 5 long free text comment fields. From what I read I understand that DS only samples the first 100 or so rows for column length. Obviously this isn’t giving me the field size I require.

I suppose my question is more Excel/VBA related but is there a way to do this in VBA perhaps? I thought asking here would be better as more than likely someone has came across this issue with importing from a file. I can find max length of each column in Excel and wondered if somehow I could have put these values into a generated ATL or something similar?

Seems like there is no simple way to do this though.

Thanks

G

grifter (BOB member since 2013-01-09)

2

Heres some code to do this using Python XLRD. I’m sure I could do this in VB but python/xlrd is super fast to code. VB … not so much.
I’m assuming if you have unicode in there you could also run into problems loading multi-byte characters.(keep that in mind)

import xlrd

xl_workbook = xlrd.open_workbook(r'C:\Users\jblythe\Desktop\JDRF_DepositReport_sample.xls')

xl_sheet = xl_workbook.sheet_by_index(0)

num_cols = xl_sheet.ncols
num_rows = xl_sheet.nrows

dctLen = {}
for col_idx in range(0, num_cols):
    dctLen[col_idx] = 0


for row_idx in range(0, num_rows):
    for col_idx in range(0, num_cols):
        cell_obj = xl_sheet.cell(row_idx, col_idx).value
        if len(str(cell_obj)) > dctLen[col_idx]:
            dctLen[col_idx] = len(str(cell_obj))

print 'Column No'.ljust(10),'Max Length'.ljust(10)
for n in sorted(dctLen.keys()):
    print str(n+1).rjust(10),str(dctLen[n]).rjust(10)

jlynn73 :us: (BOB member since 2009-10-27)