ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> System Manager >> Export Data Source
http://www.istechforum.com/YaBB.pl?num=1184349224

Message started by nicknails on 07/13/07 at 10:53:44

Title: Export Data Source
Post by nicknails on 07/13/07 at 10:53:44

I am using DBA Classic 2002.4. I'm trying to export the data that appears on the Shipping Schedule in SO-O-E. The information that I need is Customer Name, Item Number, Description, Ship Quantity, and Estimated Ship Date. I have found the estimated ship date in BKARINVL. However, I can't seem to find the field that determines if the sales order is open or closed. When I export, it exports all sales order, including closed ones.

My intention is to use the exported data in MS Project, or some other calendar program, to make a visual representation of when items are due.

Title: Re: Export Data Source
Post by Kelloggs on 07/13/07 at 11:16:36

The column that you are looking for: BKAR_INV_INVCD is on table BKARINV.
You need a sql query like this:

SELECT
BKARINV.BKAR_INV_INVCD,
BKARINV.BKAR_INV_SONUM,
BKARINV.BKAR_INV_CUSNME,
BKARINV.BKAR_INV_ORDDTE,
BKARINV.BKAR_INV_DESC,
BKARINV.BKAR_INV_CUSORD,
BKARINV.BKAR_INV_ENTBY,
BKARINVL.BKAR_INVL_CNTR,
BKARINVL.BKAR_INVL_PCODE,
BKARINVL.BKAR_INVL_PDESC,
BKARINVL.BKAR_INVL_PQTY,
BKARINVL.BKAR_INVL_PPRCE,
BKARINVL.BKAR_INVL_ESD
FROM BKARINV INNER JOIN BKARINVL ON BKARINV.BKAR_INV_SONUM = BKARINVL.BKAR_INVL_INVNM
WHERE BKARINV.BKAR_INV_INVCD <> 'Y' AND BKARINV.BKAR_INV_INVCD <> 'X'
AND LENGTH(BKARINVL.BKAR_INVL_PCODE) <> 0
ORDER BY BKARINV.BKAR_INV_SONUM

:P

Regards,

Kelloggs

Title: Re: Export Data Source
Post by nicknails on 07/13/07 at 11:42:26

Unfortunately, we don't have the ability to do sql queries. I was just going to export to a delimited text file and then import to excel. Is there any way to export from multiple files at the same time and add them into one text file?

Title: Re: Export Data Source
Post by Kelloggs on 07/13/07 at 11:50:02

You can use sql queries. Use Pervasive Control Center. Select the Database then rigth click on it
All Task, then "Export Data". Also you can call "C:\PVSW\Bin\ExpWizrd.exe"

Enter the File Name ( Location), then paste the query :P

Also, check with Lynn about the Sales Order Status, as far as I know if it is diferent than "Y" or "X" the SO is open.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by Kelloggs on 07/13/07 at 12:02:30

Here is nice VBA Code to import data from a text file. Already test works great.

:P

Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
If rngTargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;"
On Error GoTo 0
If cn.State <> adStateOpen Then Exit Sub
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
On Error GoTo 0
If rs.State <> adStateOpen Then
cn.Close
Set cn = Nothing
Exit Sub
End If
' the field headings
For f = 0 To rs.Fields.Count - 1
rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
Next f
rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Function getdata()
Application.ScreenUpdating = False
GetTextFileData "SELECT * FROM DBA.txt", "D:\", Range("A3")
' GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
"C:\FolderName", Range("A3")
Columns("A:IV").AutoFit
ActiveWorkbook.Saved = True
End Function

Title: Re: Export Data Source
Post by nicknails on 07/13/07 at 12:14:42

Ok, I sort of got it working. At least now I'm on the right track. I have some errors with the sql, something about not being able to open bkarinv. It might be because people are logged in. Is there any way to do this from a remote terminal? I tried on my station and the export functions are grayed out. I'll be leaving shortly, so I'll have to play around with it monday. Thank for your help Kelloggs.

Title: Re: Export Data Source
Post by Kelloggs on 07/13/07 at 12:29:28

We use terminal server. Same thing.

SQL Queries work a little bit diferent. I dont think that because other user are using DBA the table is locked!. There must be something else.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by nicknails on 07/16/07 at 07:10:35

So I've started to play around with this and I'm still having issues. It seems to me that the database that we use isn't registered with pervasive. In the pervasive control center, the only database listed is DEMODATA, which I have verified it not ours. Now I am more confused than before. What would happen if I were to create a new database that's in the same directory as our current database? Would it use pre-existing files? I'm more confused now..... :-/

Title: Re: Export Data Source
Post by Kelloggs on 07/16/07 at 07:46:24

Login into DBA
goto System Manager
Run UT-J
Open UT-A
Type/Run ODBCDDF.RUN

Go back to your Pervasive Control Center

select your server
select databases
rigth click New Database
Name it and include the path

it should work now.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by nicknails on 07/16/07 at 13:58:40

I got the database configured in the pervasive control panel. The SQL script doesn't do anything. I checked the tables and everything has either an X or a Y in the INVCD field. Are you sure that's the field that determines an open sales order?

Title: Re: Export Data Source
Post by Kelloggs on 07/16/07 at 14:09:23

Kelloggs wrote:

Also, check with Lynn about the Sales Order Status, as far as I know if it is diferent than "Y" or "X" the SO is open.

Regards,

Kelloggs

Actually send a email to NovaZyg, He will be the best person to confirm the "XX/XY" dilemma

:P

Title: Re: Export Data Source
Post by NovaZyg on 07/17/07 at 06:10:00

INVCD can be "Y" for invoiced
"V" for Void
" " for just entered and open
"X" for edited but still open

Hope this helps.

Title: Re: Export Data Source
Post by nicknails on 07/17/07 at 06:47:17

Excellent, I will give that a try. The code originally had both X and Y as closed work orders. I'll let you know how it goes.

Title: Re: Export Data Source
Post by nicknails on 07/17/07 at 08:18:05

I got the data into the text file. I have to do numerous edits in Excel, but I got the data that I need. Now I just have to do the calendar part.

Do you know off-hand if I have to keep redoing the DDF files? Or are those just constant?

Title: Re: Export Data Source
Post by Kelloggs on 07/17/07 at 08:28:06

nicknails wrote:

Excellent, I will give that a try. The code originally had both X and Y as closed work orders. I'll let you know how it goes.

Work Orders?

Anyways, You dont need to redo the DDF Files.

I am working on a Report similar to yours. I will send you my email so that we can exchange ideas.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by nicknails on 07/17/07 at 08:40:21

Oops, meant to type sales orders. It's a busy day. I'm having trouble making a visual representation of the data in Excel. I'm not sure what the easiest solution would be.

In the data, I have a bunch of columns that are all zero's. Is there a way to exclude these in the SQL script? I assumed that the fields under SELECT would be the only ones it exported. I'll play around with it for a while.

Title: Re: Export Data Source
Post by nicknails on 07/17/07 at 10:55:41

I managed to fine-tune the SQL script to get just the pertinent data. Unfortunately, there are still some zero columns, but nothing that a macro can't fix. However, I am getting frustrated trying to make a calendar-style type of thing. I'll give it a rest and come back to it tomorrow.

Title: Re: Export Data Source
Post by Lynn_Pantic on 07/18/07 at 06:39:32

Evo-ERP already has a Shipment Calendar - QU-B on the Queries menu. It has estimated dates for Shipments, PO Receipts and Work Order Completions. You really need to update!

Title: Re: Export Data Source
Post by Kelloggs on 07/18/07 at 09:54:10

Importing data using Pervasive Control Center, imports "0". It's not going to work.

You need to create a MS Query in MS Excel.

SELECT
BKARINV.BKAR_INV_ORDDTE,
BKARINVL.BKAR_INVL_ESD,
BKARINV.BKAR_INV_SONUM,
BKARINV.BKAR_INV_CUSNME,
BKARINV.BKAR_INV_DESC,
BKARINV.BKAR_INV_CUSORD,
BKARINV.BKAR_INV_ENTBY,
BKARINVL.BKAR_INVL_CNTR,
BKARINVL.BKAR_INVL_PCODE,
BKARINVL.BKAR_INVL_PDESC,
BKARINVL.BKAR_INVL_PQTY,
BKARINVL.BKAR_INVL_PPRCE
FROM BKARINV INNER JOIN BKARINVL ON BKARINV.BKAR_INV_SONUM = BKARINVL.BKAR_INVL_INVNM
WHERE BKARINV.BKAR_INV_INVCD <> 'Y' AND BKARINV.BKAR_INV_INVCD <> 'V'
AND BKARINVL.BKAR_INVL_ESD IS NOT NULL
AND LENGTH(BKARINVL.BKAR_INVL_PCODE) <> 0
AND BKARINVL.BKAR_INVL_ESD >= (CURRENT_DATE ( ) - 1) AND BKARINVL.BKAR_INVL_ESD <= (CURRENT_DATE ( ) + 30)
ORDER BY BKAR_INV_ORDDTE ASC

This query will show you all SO Lines that need to be Shipped between yesterday and 30 days into the future.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by JNAPIER on 07/18/07 at 14:43:16

nicknails wrote:

I have a few reports created using Excel and its Query function. The real nice part is after you create/edit the Query, you can record a Macro to do all the formatting and editing. This way you can run a real time report at the click of a mouse.

John

Title: Re: Export Data Source
Post by shadowcaster on 07/30/07 at 07:25:13

I would love to be able to do this for open work oders by work center.
and bring that into MS project.

Title: Re: Export Data Source
Post by Charlie Klihr on 07/30/07 at 11:59:48

I have tried to get this to work for awhile. I get a Java error (Java.IO.IOException: Could not Connect) when trying to open a database or file.

Portion of the error log is below-

!SESSION Jul 30, 2007 12:09:58.921 ---------------------------------------------
eclipse.buildId=I200406251208
java.version=1.5.0
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US

!ENTRY com.pervasive.psql.utilities.core 4 0 Jul 30, 2007 12:09:58.921
!MESSAGE
!STACK 0
java.sql.SQLException: java.io.IOException: could not connect
at com.pervasive.jdbc.v2.Connection.<init>(Connection.java:107)
at com.pervasive.jdbc.v2.Driver.connect(Driver.java:57)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.pervasive.psql.utilities.core.dbaccess.jdbc.JDBCAccess.connectToDatabase(Unknown Source)
at com.pervasive.psql.utilities.core.dbaccess.jdbc.JDBCAccess.connect(Unknown Source)
at com.pervasive.psql.utilities.core.v9.Database.connect(Unknown Source)
at com.pervasive.psql.utilities.core.v9.ServerDatabase.querySecurityEnabled(Unknown Source)
at com.pervasive.psql.utilities.core.v9.ServerDatabase.isSecurityEnabled(Unknown Source)
at com.pervasive.psql.utilities.core.v9.Database.loadChildren(Unknown Source)
at com.pervasive.core.facets.FacetSet.internalLoadChildren(Unknown Source)
at com.pervasive.core.facets.FacetSet.getIterator(Unknown Source)
at com.pervasive.ui.views.navigator.NavigatorContentProvider.getChildren(Unknown Source)
at org.eclipse.jface.viewers.AbstractTreeViewer.getRawChildren(AbstractTreeViewer.java:708)
at org.eclipse.jface.viewers.StructuredViewer.getFilteredChildren(StructuredViewer.java:477)
at org.eclipse.jface.viewers.StructuredViewer.getSortedChildren(StructuredViewer.java:585)
at org.eclipse.jface.viewers.AbstractTreeViewer$1.run(AbstractTreeViewer.java:359)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)
at org.eclipse.jface.viewers.AbstractTreeViewer.createChildren(AbstractTreeViewer.java:342)
at org.eclipse.jface.viewers.AbstractTreeViewer.handleTreeExpand(AbstractTreeViewer.java:754)
at org.eclipse.jface.viewers.AbstractTreeViewer$4.treeExpanded(AbstractTreeViewer.java:765)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:179)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:796)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:820)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:805)
at org.eclipse.swt.widgets.Tree.wmNotifyChild(Tree.java:2074)
at org.eclipse.swt.widgets.Control.WM_NOTIFY(Control.java:4002)
at org.eclipse.swt.widgets.Composite.WM_NOTIFY(Composite.java:722)
at org.eclipse.swt.widgets.Control.windowProc(Control.java:3019)
at org.eclipse.swt.widgets.Display.windowProc(Display.java:3338)
at org.eclipse.swt.internal.win32.OS.CallWindowProcW(Native Method)
at org.eclipse.swt.internal.win32.OS.CallWindowProc(OS.java:1397)
at org.eclipse.swt.widgets.Tree.callWindowProc(Tree.java:170)
at org.eclipse.swt.widgets.Tree.WM_LBUTTONDOWN(Tree.java:1668)
at org.eclipse.swt.widgets.Control.windowProc(Control.java:3001)
at org.eclipse.swt.widgets.Display.windowProc(Display.java:3338)
at org.eclipse.swt.internal.win32.OS.DispatchMessageW(Native Method)
at org.eclipse.swt.internal.win32.OS.DispatchMessage(OS.java:1473)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2429)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1377)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1348)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:254)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:141)
at com.pervasive.ui.Application.run(Unknown Source)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:335)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:273)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.core.launcher.Main.basicRun(Main.java:185)
at org.eclipse.core.launcher.Main.run(Main.java:704)
at org.eclipse.core.launcher.Main.main(Main.java:688)

Kelloggs wrote:

You can use sql queries. Use Pervasive Control Center. Select the Database then rigth click on it
All Task, then "Export Data". Also you can call "C:\PVSW\Bin\ExpWizrd.exe"

I'm at a loss as to where to find a fix for this. Any ideas will be appreciated.

Title: Re: Export Data Source
Post by Kelloggs on 07/30/07 at 13:20:39

Are you using Pervasive Version 9?

Regards,

Kelloggs

Title: Re: Export Data Source
Post by Charlie Klihr on 07/30/07 at 13:39:37

Well, no. I'm using v9.5

Title: Re: Export Data Source
Post by Kelloggs on 07/30/07 at 13:52:19

the problem is your java installation ( I think ). Does it happen on all workstations?

I would post this problem on the comp.databases.btrieve newsgroup.

Regards,

Kelloggs

Title: Re: Export Data Source
Post by Charlie Klihr on 07/30/07 at 13:59:15

Thanks, I will try that. I haven't tried from another workstation. Good idea.