1. Glenn Barber
  2. PowerBuilder
  3. Thursday, 23 April 2020 02:06 AM UTC

We are looking for the best techniques - and perhaps sample code which would allow users to import spreadsheet data into a datawindow.

This would have to be somewhat dynamic in that the format of the spreadsheet could change from one spreadsheet to another - so we would expect to create the column names based on the spreadsheet header and all info would be initially stored and presented as varchars.

As the spreadsheet could be quite large (thousands of rows) cut and paste - as we have done before - would not be suitable.  Instead we would like the user to select the file name from a directory.

After import the user may want to re-label the columns and provide additional information, at which point we would assign the correct datatypes and insert into a database.

Any suggestions, sample code or alternative successful approaches to accomplish the same would be appreciated.

We are currently on 2017 LTR and sql anywhere in a windows environment.

Thanks

Glenn

 

 

 

Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 14 May 2020 13:14 PM UTC
  2. PowerBuilder
  3. # 1

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

 

Comment
  1. mike S
  2. Thursday, 14 May 2020 13:54 PM UTC
yes, that is very slow going cell by cell. save to tab file from excel, then importing is a lot faster. either way, the downside is that excel must be installed for this stuff to work.



With the new .net integration, i hope to look into opening and reading the file directly without excel/ole. The problem with that is the old .xls format is not easy to access; but other than PB and powerserver exporting to it, i don't know if anyone else still uses xls format.
  1. Helpful
  1. Sivaprakash BKR
  2. Friday, 15 May 2020 06:05 AM UTC
Those who ask us to incorporate import facilities, do feel excel is the best. I agree that many of them couldn't differentiate between xls & xlsx.



Not sure whether Appeon got any idea to incorporate data importing and exporting from & to Excel...
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Friday, 24 April 2020 18:38 PM UTC
  2. PowerBuilder
  3. # 2

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

 

 

 

 

Comment
  1. Sivaprakash BKR
  2. Wednesday, 13 May 2020 05:10 AM UTC
Thanks, Mike S.

What we do now is almost the same way with the variation instead of saving as CSV, we read and insert directly to a datawindow. Currently, performance is hit if the data is more than 5000 rows. I'll try to save as CSV and check for performance.
  1. Helpful
  1. mike S
  2. Wednesday, 13 May 2020 12:24 PM UTC
Sivaprakash,

how are you reading? Using ole, or are you opening it some other way such as oledb?
  1. Helpful
  1. Sivaprakash BKR
  2. Thursday, 14 May 2020 13:16 PM UTC
mike S

using OLE only. Given my code in separate comment.
  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Friday, 24 April 2020 16:50 PM UTC
  2. PowerBuilder
  3. # 3

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.

 

 

 

Comment
  1. mike S
  2. Friday, 24 April 2020 18:43 PM UTC
In my case, i got so bored with building yet another custom import that i ended up building a generic one so that users could setup their own imports.
  1. Helpful
  1. Glenn Barber
  2. Friday, 24 April 2020 18:52 PM UTC
Same here - I was hoping maybe someone had code they could share.for a generic import process.
  1. Helpful
  1. John Fauss
  2. Friday, 24 April 2020 19:21 PM UTC
Thank you, Glenn, for taking the time to explain. A completely different world than the ones I have had exposure to in my career.Fascinating!
  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 24 April 2020 11:23 AM UTC
  2. PowerBuilder
  3. # 4

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.

Comment
  1. mike S
  2. Friday, 24 April 2020 18:40 PM UTC
it comes down to volume. If you have huge files like that, then it may make sense to spend the time/money for a more direct solution that requires a DBA or developer to do the import.
  1. Helpful
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 23 April 2020 16:38 PM UTC
  2. PowerBuilder
  3. # 5

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.

Comment
  1. Miguel Leeuwe
  2. Thursday, 23 April 2020 19:23 PM UTC
Oh yes, you're right about it being slow. But that would be only for reading which headers and columns you'd need. I was thinking that for the data you then would export the excel sheet to a tab separated file or whichever format comes in handy. Of course, that would only work if you have excel sheets with a single header row and then below only data.
  1. Helpful
  1. Glenn Barber
  2. Friday, 24 April 2020 16:53 PM UTC
Yes - as I noted separately - perhaps using this with the importfile function might be a solution.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 23 April 2020 06:26 AM UTC
  2. PowerBuilder
  3. # 6

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)

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.