mike s,

Here is my code:
************************************************************
ls_filename = 'somefile.xls'
ll_sheet = 1

TRY
xlApp = Create OLEObject
ll_ret = xlApp.ConnectToNewObject( "Excel.Sheet" )
If ll_ret < 0 then
      MessageBox("Connect to Excel Failed !", String(ll_ret))
      Return
End If

xlApp.Application.Workbooks.Open(ls_filename)

xlsub = xlapp.Application.ActiveWorkbook.Worksheets[ll_sheet]

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

ll_cnt = 2

SetPointer(HourGlass!)
For ll_cnt = 2 to ll_max_rows
       ll_row = tab_1.tabpage_1.dw_1.InsertRow(0)
       // DO FOR ALL COLUMNS, SHOWN ONLY FOR ONE COLUMN
       tab_1.tabpage_1.dw_1.SetItem(ll_row, 'field1', Mid(Trim(String(xlsub.cells[ll_cnt, 1].value)),1,25))
Next
XlApp.Application.Workbooks.close()
SetPointer(Arrow!)

CATCH ( runtimeerror lo_rte)
          MessageBox('Elifa', "MS Excel api runtime error")
FINALLY
       If (IsValid(xlapp)) THEN
           xlapp.application.quit()
           xlapp.DisconnectObject()
           Destroy xlsub
           Destroy xlapp
      End If
END TRY

*******************************************************************

HTH

I have a generic file import feature in my application that imports from various formats including excel.

One of the keys to this working for my application is that i added the ability to provide mapping features since the data that is sent typically does not have the values that my application uses.  So i can convert 'ABC' to '321' as needed on a per import basis.  Also date formats must be specified and number formats (decimals, decimals implied, etc).

I require that the user setup an import spec and define the import fields to match the import file.  Then they map those  file filed to column/fields in my system.   From this import spec, i build an external datawindow at runtime and use that to load the data into.

I use file read instead of importfile since you can run into problems with import file when the data is bad in the file. This is slower, but i have much better control.  Plus i can read in any format; i'm not limited to tab or csv

There is this

li_rc = dw_1.importfile(CSV!, ls_file_path, 2)

However to get this to work you need a matching datawindow object.

One thing we have discovered which helps with any datawindow performance problems is to turn off redisplay and particularly any autosize height columns. You also need to turn off all the windows handles in the pb.ini  OLE is a different story however, as PB OLE automation is dynamically bound and super slow.

Perhaps the importfile function combined with the technique of for first inspecting the source with OLE beforehand and creating the datawindow syntax might be a best first step.

This requirement for importing spreadsheet seems so prevalent in business apps, I am surprised there is not already some code floating around to do this.

DW is too lazy to import few thousands of records.  We tried with 76000 rows and 16 columns.  We lost our patience and imported the same with SQL Server Import / Export tool.   

Something like Datapipeline which could read data from any source (ODBC, ADO, Native Drivers etc) and update to destination fast without using DS or DW will be good.

IMHO.

While your technique would work for creating the initial datawindow definition - and the creating the sql syntax from SQL a helpful direction  We already have a lot of experience with OLE in terms of populating spreadsheets, but our experience with it is that it is way too slow for large amounts of data.  I would expect it could be the same in terms of ingesting the data from 100's of columns and tens of thousands of rows.

Hi,

Use OLE to read the information you need from the excel sheet. Create a string with all the column names, something like

"SELECT ' ' as col1, ' ' as col2 FROM DUMMY" // I believe the DUMMY table exists in sqlanywhere ?

Use SyntaxFromSQL() to generate the syntax for the creation of the dw. 

This would give you a grid datawindow with text objects "col1_t" and "col2_t" in the header and the columns. You can then use Modify() for any changes you want to do.

A lot of work, but it can be done.

The only thing you have to be careful with is that the amount of columns a datwindow can hold, is limited.  I believe it's about maximum 1000 columns.  Hopefully you don't need that many columns on a dw.

string ERRORS, sql_syntax
string presentation_str, dwsyntax_str
 
sql_syntax = "SELECT ' ' as col1, ' ' as col2 FROM dummy "
 
presentation_str = "style(type=grid)"
 
dwsyntax_str = SQLCA.SyntaxFromSQL(sql_syntax, &
   presentation_str, ERRORS)
 
IF Len(ERRORS) > 0 THEN
   MessageBox("Caution", &
   "SyntaxFromSQL caused these errors: " + ERRORS)
   RETURN
END IF
 
dw_1.Create( dwsyntax_str, ERRORS)