Help with DW and Excel File

[Former Member]

Hello, I'm trying to export a datawindow to a excel file with 3 worksheets, because an excel file have a limit in rows (version 2007 have a limit of 1048576 rows), my code is ok (in theory) but when I try to export mor than 510000 rows, powerbuilder is close and view a error message.

oleobject myoleobject

integer rc, i, count, j, colcount, rep

string mydata, colname, colnames, ls_select, ls_dwsyntax, ls_err, el

rep = truncate(dw_2.rowcount()/1048575,0)

if mod(dw_2.rowcount(),1048575) > 0 then rep = rep +1

myoleobject = CREATE oleobject

rc = myoleobject.ConnectToNewObject("Excel.Application")

IF rc <> 0 THEN

MessageBox ( "Error", String ( rc ) )

Return

END IF

ls_select = string(dw_2.object.datawindow.table.select)

ls_dwsyntax = SQLCA.SyntaxFromSQL( ls_select, "Style(Type=tabular)", ls_err)

dw_3.Create (ls_dwsyntax, ls_err)

IF ls_err <> '' THEN

MessageBox ("error - Sintaxis", ls_err)

else

dw_3.SetTransObject (SQLCA)

end if

try

myoleobject.Application.Visible = false

myoleobject.Workbooks.Add()

for i = 1 to rep

  myoleobject.activeworkbook.sheets(i).name = as_tabla+string(i)//"reporte"+string(i)

  dw_3.reset()

  dw_2.RowsCopy(((1048574 * (i - 1)) + i), (1048575 * i), Primary!, dw_3, 1, Primary!)

  colcount = integer(dw_3.object.DataWindow.column.count)

  for j = 1 to colcount

   colname = dw_3.describe("#"+string(j)+".Name")

   choose case j

    case 1

     colnames = colname

    case else

     colnames = colnames + '~t' + colname

   end choose

  next

  colnames = colnames + "~r~n"

  mydata = dw_3.object.datawindow.data

  clipboard(colnames + mydata)

  myoleobject.activeworkbook.sheets(i).paste()

next

  //////

for i = 1 to myoleobject.activeworkbook.sheets.count()

  el = myoleobject.activeworkbook.sheets(i).name

  if  el <> as_tabla+string(i) then exit

next

if i = 2 then

  myoleobject.activeworkbook.sheets(2).Delete

  myoleobject.activeworkbook.sheets(2).Delete

elseif i = 3 then

  myoleobject.activeworkbook.sheets(3).Delete

end if

  //////

myoleobject.activeworkbook.saveas(as_destino)

myoleobject.activeworkbook.close()

myoleobject.Application.quit

myoleobject.DisconnectObject()

catch ( exception e )

MessageBox ( "Error", e.GetMessage() )

finally

Destroy myoleobject

end try

please explain me the error, sorry for my english.

Thanks,

Frank M. Páucar Cáceres

[Former Member]

dw_2.SaveAs ( 'myexcel.xls' , XLS! )

[Former Member]

My answer is "don't do that"...

You honestly have a use case that requires a half million rows in a spreadsheet?  Why??

Did you know that Excel can query your database directly?  You don't need to retrieve all that data into a datawindow first...

-Paul-

[Former Member]

Hello Paul Horan, and thanks for the answer, honestly I want to export the data of a DW External in a Access File, but I only found that I need export into excel file and before with a DLL in VB6 export the excel file into access file, if you know a solution to my problem and theahc me then I will be very grateful.

Thanks,

Frank P.

[Former Member]

Hi,

Why don't you use a connection to MS Acess database from powerBuilder and write in directly.

Regards.

Abdallah.

[Former Member]

Hello Abdallah, do you explain me how to do this, when every user put the name and the path of the access file, and the access file only can the data of the report in the DW External actually.

Thanks,

Frank P.

[Former Member]

-  What is the original source of the data?  If it's an external datawindow, then it's probably not another database (or you would have written it as a query against that database, not external) 

-  Why does it have to end up in Access?  Can't you use a real database?  I wouldn't build an enterprise system against a single-user file-based system like MS Access...

Ultimately, this is a data integration problem, not a PowerBuilder coding error.  The real problem is how to get your data from point A to point B.  PB has an object called a Pipeline, which does exactly that task - move data from one data source to another.

-Paul-

[Former Member]

Hi Paul,

About Access: a single-file database is really convenient sometimes.

For example we use Access files to export a fragment of a bigger database, with the same structure than the real one but just a little part of the data, it greatly facilitates data exchange.

I would prefer Sql Server CE or SQLite but it's not possible with Pb classic (I think).

Regards

[Former Member]

well, I'll explain my problem from the beginning:

- We have a Store Procedure in the DB (MS SQL SERVER 2005).

- We habe been create a DW with the SP, and I designed the DW (some columns, computed headers, groups, subtotal, etc.)

- We only needs the select of the SP, that's why I created a DW external, to move the all data, all columns and the computed headers.

- Finally, we need to export the data of the DW External into Acces file, for analysis of the directive.

- I don't find a direct solution, that's because I export into excel file, and through a DLL in VB6 I import the data of the excel file into a new access file.

- If you can help me, please explain me.

Regards.

[Former Member]

I found this:

Question:

Hi!
Anybody knows how can I save the contents of a Datawindow (using saveas) as 
a Microsoft Access file using PowerBuiler 8.0.4?
Thanks!
Sergi

Answer:

Not from a DataWindow.  But you export data from your database to an Access file
using a Data Pipeline.
I just uploaded an example of doing this to CodeXchange:
http://powerbuilder.codexchange.sybase.com/files/documents/67/1939/createdatabas
e.zip
A couple of notes.  The file demonstrates:
1.  How to dynamically create an Access data file, even if Access isn't
installed on the machine.
2.  How to connect to that new database using a DSN-less connection, so you
don't have to mess with creating ODBC registry entries to talk to it.
Actually, come to think of it you could do this with a DataWindow as well.
Simply connect to your new database, do a SetTransObject on the DataWindow with
that new connection, loop through the rows in the DataWindow making sure their
item status is set to NewModified!, and then do an Update and Commit.
Bruce Armstrong

Now, can you explain me if this solution is for my case (I need to export a access file of the diferents reports) and how can I do this?

Regards.

[Former Member]

Hi Frank,

For what I understand, it doesn't make any sense to add an excel step in this data move.

Is the data source of your datawindow a stored procedure ?

If yes, I won't call it an external datawindow.

If not, it should.

You will need an ODBC transaction for Access, something like that :

Transaction lt_Access

lt_Access = CREATE Transaction
lt_Access.DBMS = "ODBC"
lt_Access.DBParm = "ConnectString='DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Data\MyDataBase.mdb',CommitOnDisconnect='No',DelimitIdentifier='No',PBNoCatalog='Yes',DisableBind=1"

I don't know pipelines but it seems to exactly fit your need, have a look at it (new>file>database>data pipeline)

Good luck.

Guillaume

[Former Member]

I would also recommend a Pipeline object for this task, not the current architecture.  That will result in having three separate copies of all the data in the workstation memory:

1) The initial dw.retrieve - which invokes the stored procedure;

2) The external dw - that has all the summary data and headers too

3) The Excel spreadsheet

You might be able to free up some memory by doing a dw_1.Reset() and destroying it after doing the RowsCopy into the external dw.  But a pipeline is still a better option.

-Paul-

[Former Member]

Why use PB at all?  You can go straight from SQLServer to Access, using either a 'Linked Server' or 'Opendatasource'.

http://stackoverflow.com/questions/9116465/move-data-from-sql-server-to-ms-access-mdb

[Former Member]

Hi Paul, I have a question, the Data Pipeline can be created dinamically?, because my reports are many differents...

[Former Member]

Yes - absolutely. 

[Former Member]

Hi, I'm trying set a new syntax in the pipeline, but PB show me a error message (Syntax error)

l_pipe = CREATE pipeline

l_pipe.DataObject = "p_prueba"

messagebox('',l_pipe.syntax)

l_pipe.syntax = 'PIPELINE(source_connect=prueba,destination_connect=access,type=create,commit=100,errors=100) SOURCE(name="gen_tabla",COLUMN(type=varchar,name="empresa",dbtype="varchar(20)",nulls_allowed=no)) RETRIEVE(statement="select empresa from gen_tabla where empresa = '005' and cod_tabla = 'gen_privilegio'") DESTINATION(name="reporte",COLUMN(type=varchar,name="empresa",dbtype="VARCHAR(20)",nulls_allowed=no,initial_value="spaces"))'