How to import Excel file into DataWindow

Question

I want to import the .xlsx file into PowerBuilder DataWindow, I know we can able to do it with CSV format, but the user wants to use the xlsx format, Please let me know if there are any options to import.

Powerbuiler 11.5 version we are currently using – user2393425 Jul 23 '13 at 07:16 — user2393425, Jul 23 '13 at 07:16

DARKinVADER · Answer 1 · 2014-06-25T11:05:03.200

you can import xls like this (this is our import function). Of course you have to know where to put each columns value from the excel to the datawindow. I think you do not need such a sophisticated solution, but there are the main guidelines:

int     li_rtn, li_i
string  ls_range
long    ll_excel_rows, ll_max_rows, ll_max_columns
long    ll_i, ll_j
string  lsa_column_names[], ls_mod
long    lla_column_pos[]
long    ll_row
int     li_rc
string  las_truncated_data[]
string ls_errorMessage

oleobject lole_excel, lole_workbook, lole_worksheet

TRY   
    lole_excel = create oleobject
    li_rtn = lole_excel.ConnectToNewObject("excel.application")
    if li_rtn <> 0 then
            ls_errorMessage = "Error running MS Excel api"
            li_rtn = -1
    else
        li_rtn = 1
        lole_excel.WorkBooks.Open(as_filepath) 

        lole_workbook = lole_excel.application.workbooks(1)

        int li_sheetnum
        IF IsNumber(as_sheet) THEN
            li_sheetnum = Integer(as_sheet)
        ELSE
            li_sheetnum = uof_pub_excel_get_sheet_byname(as_sheet, lole_workbook)
        END IF
        lole_worksheet = lole_workbook.worksheets(li_sheetnum)

        ll_max_rows     = lole_worksheet.UsedRange.Rows.Count
        ll_max_columns  = lole_worksheet.UsedRange.Columns.Count

        string lsa_tmp[]
        lsa_column_names = f_split(f_trim(as_imported_columns,";"), ";")
        FOR ll_i = 1 TO UpperBound(lsa_column_names)
            IF (pos(lsa_column_names[ll_i], ":")>0) THEN
                lsa_tmp = f_split(lsa_column_names[ll_i], ":")
                lla_column_pos[UpperBound(lla_column_pos)+1] = long(lsa_tmp[2])
                lsa_column_names[ll_i] = lsa_tmp[1]
            END IF
        NEXT

        string  ls_cellValue, ls_coltype, ls_value
        int         li_idx_col, li_statrRow
        boolean lb_copyData = false
        int         li_col_offset, li_vlen

        li_statrRow = ai_start_row

        IF (UpperBound(lla_column_pos)=0) THEN
            IF (UpperBound(lsa_column_names)<ll_max_columns) THEN ll_max_columns = UpperBound(lsa_column_names)
            FOR ll_j = li_statrRow TO ll_max_rows
                li_col_offset = 0
                ll_row = adw_dest.insertRow(0) // insert new row
                FOR ll_i = 1 TO (ll_max_columns)
                    ls_cellValue = String(lole_worksheet.cells(ll_j,ll_i).value)
                    IF (lsa_column_names[(ll_i)] = "") THEN 
                        li_col_offset++
                        continue
                    END IF
//                  li_rc = adw_dest.SetItem(ll_row, lsa_column_names[(ll_i)], lole_worksheet.cells(ll_j, (ll_i)).value)

                    ls_value = String(lole_worksheet.cells(ll_j, ll_i).value)
                    ls_coltype = adw_dest.describe(lsa_column_names[(ll_i)]+'.ColType')

                    // Checking length of string data
                    IF (pos(ls_coltype, "char")>0) THEN
                        li_vlen = integer(replace(left(ls_coltype, len(ls_coltype)-1), 1, 5, ""))
                        IF (li_vlen > 0 AND len(ls_value)>li_vlen) THEN
                            li_rtn = -2
                            ls_value = left(ls_value, li_vlen)
                            IF (f_array_indexof(las_truncated_data, lsa_column_names[ll_i])<1) THEN
                                las_truncated_data[UpperBound(las_truncated_data)+1] = lsa_column_names[ll_i]
                            END IF
                        END IF
                    END IF

                    li_rc = guo_common_utilities.uof_pub_set_dw_value(adw_dest, ll_row,  lsa_column_names[(ll_i)], ls_value,  ls_coltype)

                NEXT
            NEXT

        ELSE
            FOR ll_j = li_statrRow TO ll_max_rows
                ll_row = adw_dest.insertRow(0) // insert new row
                FOR ll_i = 1 TO UpperBound(lla_column_pos)
                    ls_cellValue = String(lole_worksheet.cells(ll_j,lla_column_pos[ll_i]).value)
                    adw_dest.SetItem(ll_row, lsa_column_names[ll_i], ls_cellValue)
                NEXT
            NEXT
        END IF
    end if

CATCH ( runtimeerror  lo_rte)
    li_rtn = -1
    ls_errorMessage = "MS Excel api runtime error"
FINALLY   
    // Quit
    IF (IsValid(lole_excel)) THEN 
        lole_excel.application.quit()
        lole_excel.DisconnectObject()
    END IF

    destroy lole_Excel
    destroy lole_workbook
    destroy lole_worksheet 
END TRY

uo_nv_response luo_return 
luo_return = create uo_nv_response 

luo_return.ii_errorCode = li_rtn
IF (UpperBound(las_truncated_data)>0) THEN
    luo_return.is_errorMessage = "Zeichenkette von "
    FOR li_i = 1 TO UpperBound(las_truncated_data)
        luo_return.is_errorMessage += las_truncated_data[li_i] + ", "
    NEXT
    luo_return.is_errorMessage = Left(luo_return.is_errorMessage, Len(luo_return.is_errorMessage)-2)
    luo_return.is_errorMessage += " wurde abgeschnitten"
ELSE
    luo_return.is_errorMessage = ls_errorMessage
END IF

return luo_return

Here are some missing functions:

global type f_array_indexof from function_object end type forward prototypes global function integer f_array_indexof (any aa_array[], any aa_element) end prototypes global function integer f_array_indexof (any aa_array[], any aa_element);int li_count FOR li_count = 1 TO UpperBound(aa_array) IF (lower(aa_array[li_count]) = lower(aa_element)) THEN return li_count END IF NEXT return -1 end function

f_split:

global type f_split from function_object end type

forward prototypes global function boolean f_split (string as_str) global function any f_split (string as_str, string as_delimiter) end prototypes

global function any f_split (string as_str, string as_delimiter);long ll_pos1, ll_pos2, i String lsa_split[]

ll_pos1 = Pos(as_str, as_delimiter) ll_pos2 = 0 i = 1

if (ll_pos1 = 0 and Len(as_str)>0) then lsa_split[1] = as_str

do while ll_pos1 > 0 lsa_split[i] = '' if( i = 1 )then lsa_split[i] = mid (as_str, 1, ll_pos1 - 1) ll_pos2 = ll_pos1 else ll_pos2 = Pos (as_str, as_delimiter, ll_pos1 + 1) if( ll_pos2 = 0 )then // it's the end :) lsa_split[i] = mid (as_str, ll_pos1 + 1, Len(as_str) - ll_pos1 ) else lsa_split[i] = mid (as_str, ll_pos1 + 1, ll_pos2 - ll_pos1 - 1) end if end if ll_pos1 = ll_pos2
i = i + 1 loop //lsa_split[i] = Right( as_str, Len(as_str) - ll_pos1 )

return lsa_split end function

f_trim:

global type f_trim from function_object end type

forward prototypes global function string f_trim (string as_string, character ac_remove) end prototypes

global function string f_trim (string as_string, character ac_remove);if (Isnull(as_string) or Isnull(ac_remove)) then return as_string

do while (left(as_string, 1) = ac_remove) as_string = right(as_string, Len(as_string)-1) loop

do while (right(as_string, 1) = ac_remove) as_string = left(as_string, Len(as_string)-1) loop

return as_string end function

public function integer uof_pub_excel_get_sheet_byname (string as_sheetname, oleobject aole_workbook);int li_sheets_count, li_i

li_sheets_count = aole_workbook.worksheets.count FOR li_i = 1 TO li_sheets_count IF (aole_workbook.worksheets(li_i).name = as_sheetname) THEN return li_i NEXT

return 0 end function

score 2 · Answer 2 · edited Aug 05 '14 at 14:10

2

If the spreadsheet is defined correctly (header row contains the column name, rows 2-n contain the data), then you can define an ODBC connection to it, and treat it just like a database.

You paint the datawindow directly against the spreadsheet - no coding at all!! (Other than setting SQLCA and calling dw.Retrieve(), of course)

edited Aug 05 '14 at 14:10

Bill the Lizard

398,270
210
566
880

answered Jul 23 '13 at 20:49

NoazDad

608
3
9

score 0 · Answer 3 · answered Jul 23 '13 at 18:40

0

I would open the workbook with OLE, save it as format 8 (DBase3) with a dbf extension, then import it in the DataWindow.

answered Jul 23 '13 at 18:40

Hugh Brackett

2,706
14
21

score 0 · Answer 4 · answered Jun 29 '18 at 19:32

Save it to a text file using OLE and then import it. Below piece of code does this

li_return = GetFileOpenName('Select a spreadsheet to upload - XLS or XLSX formats only', ls_path, ls_file, 'xl*', "All Excel Files (*.xl*),*.xl*", "%userprofile%\desktop")

//Save the file as Text using OLE and then import
MyExcelFile = Create OLEObject
If MyExcelFile.ConnectToObject(ls_path) < 0 then
    MessageBox('Import Error', 'Unable to load spreadsheet. Please check if the file is open')
    Return
End If
ls_txt_path = Mid(ls_path, 1, Pos(ls_path, '.xl') - 1) + '.txt'
FileDelete(ls_txt_path)
MyExcelFile.Application.WorkBooks(1).SaveAs(ls_txt_path, 21);
MyExcelFile.Application.DisplayAlerts = False
MyExcelFile.Application.WorkBooks(1).Close();
Destroy MyExcelFile;

Now import the file with the name ls_txt_path into the datawindow

How to import Excel file into DataWindow

4 Answers4

Linked