ISTech Support Forum - Export Data Source

nicknails

Ex Member

Export Data Source
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.

Back to top

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: Export Data Source
Reply #1 - 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

Tongue

Regards,

Kelloggs

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

nicknails Ex Member	Re: Export Data Source Reply #2 - 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?
Back to top	IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: Export Data Source
Reply #3 - 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 Tongue

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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: Export Data Source
Reply #4 - 07/13/07 at 12:02:30

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

Tongue

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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

nicknails

Ex Member

Re: Export Data Source
Reply #5 - 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.

Back to top

IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: Export Data Source Reply #6 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

nicknails

Ex Member

Re: Export Data Source
Reply #7 - 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..... undecided

Back to top

IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: Export Data Source Reply #8 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

nicknails Ex Member	Re: Export Data Source Reply #9 - 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?
Back to top	IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: Export Data Source Reply #10 - 07/16/07 at 14:09:23 Quote from Kelloggs on 07/13/07 at 11:50:02: 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

NovaZyg Administrator When all else fails.. Read the instructions. Posts: 789 Gender:	Re: Export Data Source Reply #11 - 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.
Back to top	Allen Landry Evo~ERP EvoERP and DBA Classic always Beta's of the Latest versions. IP Logged

nicknails Ex Member	Re: Export Data Source Reply #12 - 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.
Back to top	IP Logged

nicknails Ex Member	Re: Export Data Source Reply #13 - 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?
Back to top	IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: Export Data Source
Reply #14 - 07/17/07 at 08:28:06

Quote from 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.

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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

nicknails

Ex Member

Re: Export Data Source
Reply #15 - 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.

Back to top

IP Logged

nicknails Ex Member	Re: Export Data Source Reply #16 - 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.
Back to top	IP Logged

Lynn_Pantic Administrator evolution (n) - gradual change to a different form Posts: 5663	Re: Export Data Source Reply #17 - 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!
Back to top	Lynn Pantic IS Tech Support lynn@istechsupport.com IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 784
Gender: male

Re: Export Data Source
Reply #18 - 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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

JNAPIER

Active Member

Evil-Evo Bad Dog!

Posts: 533
Gender: male

Re: Export Data Source
Reply #19 - 07/18/07 at 14:43:16

Quote from 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?

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

Back to top

EVO-ERP, ISTS 3.27.08 , 2000I 10 user

IP Logged

shadowcaster Active Member I'm not here because I know what to do. Posts: 678 Gender:	Re: Export Data Source Reply #20 - 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.
Back to top	IP Logged

Charlie Klihr

Member

Custom Transformer
Manufacturing

Posts: 66
Gender: male

Re: Export Data Source
Reply #21 - 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(Unk
nown 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.j
ava:708)
at org.eclipse.jface.viewers.StructuredViewer.getFilteredChildren(StructuredViewer.
java:477)
at org.eclipse.jface.viewers.StructuredViewer.getSortedChildren(StructuredViewer.ja
va: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.j
ava:342)
at org.eclipse.jface.viewers.AbstractTreeViewer.handleTreeExpand(AbstractTreeViewer
.java:754)
at org.eclipse.jface.viewers.AbstractTreeViewer$4.treeExpanded(AbstractTreeViewer.j
ava: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)

Quote from 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"

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

Back to top

Charlie (I'm Too Old For This) Klihr
DBA Classic 2004.1, ISTS 12/28/13
25 Users
Evo~Erp 14 Users, Build- 01/02/14 T7-7iR6
PSQL v11
Crystal Reports v7.0
Windows Server 2008 R2

IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: Export Data Source Reply #22 - 07/30/07 at 13:20:39 Are you using Pervasive Version 9? Regards, Kelloggs
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

Charlie Klihr Member Custom Transformer Manufacturing Posts: 66 Gender:	Re: Export Data Source Reply #23 - 07/30/07 at 13:39:37 Well, no. I'm using v9.5
Back to top	Charlie (I'm Too Old For This) Klihr DBA Classic 2004.1, ISTS 12/28/13 25 Users Evo~Erp 14 Users, Build- 01/02/14 T7-7iR6 PSQL v11 Crystal Reports v7.0 Windows Server 2008 R2 IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 784 Gender:	Re: Export Data Source Reply #24 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

Charlie Klihr Member Custom Transformer Manufacturing Posts: 66 Gender:	Re: Export Data Source Reply #25 - 07/30/07 at 13:59:15 Thanks, I will try that. I haven't tried from another workstation. Good idea.
Back to top	Charlie (I'm Too Old For This) Klihr DBA Classic 2004.1, ISTS 12/28/13 25 Users Evo~Erp 14 Users, Build- 01/02/14 T7-7iR6 PSQL v11 Crystal Reports v7.0 Windows Server 2008 R2 IP Logged