Importing Data from PICK using PICK Data and PICK Dictionary Files

[salman javed]

Hi,

I am very new to PICK Basic world and as I was doing research for my task I found this group and it seems to be a ray of hope for me to solve my problem :).

I am working on creating parser that will parse PICK dictinoary files, get index from that and access data in the data file as per the found index for specific attribute. but at the moment there are multiple items having differnet length of attributes, (some of them are repeated as well),  in dictionary file and I am unable to extract a generalized pattern for finding the indexes of data in associated data file.

My question is what is the optimal and generic way of finding indexes for data in PICK dict file to access the data in associated data file. 

Thanks,
Salman

[Wols Lists]

On 28/08/19 09:20, salman javed wrote:
>
> My question is what is the optimal and generic way of finding indexes
> for data in PICK dict file to access the data in associated data file.

There should be a LIST.INDEX command? Either at TCL (the colon/command
line) or as a BASIC subroutine.

Cheers,
Wol

[Ed Clark]

The items in a pick dictionary don’t necessarily define the data in the file. They might describe it, but most of the dictionary items probably exist to *display* the data in different formats on different reports. So there will be multiple items with names like “NAME”, “NAME20”,”NAME40”,”CustomerName” which will display on reports generated by LIST and SORT with different widths and different column titles. There will be multiple items for date and time fields with different conversions “D”,”D4/“,”DMA” to display dates in different formats.

Sometimes you can find items with names like A1,A2,A3… or D1,D2,D3… which might be considered the “primary” description of what is in the that attribute in the file.

Sometimes, more savy developers will put actual definitions in the item in non-standard locations, like starting at line 11 or later.

The title of your post says you are trying to import data from pick. Which pick platform are you importing from (d3/universe/jbase/etc..) and which database are you importing into? Knowing that, someone can probably point you to existing solutions using CSV files or ODBC or REST.

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/5aaa0628-7200-40d8-9f1f-a0f26c96ce87%40googlegroups.com.

[salman javed]

Thanks Wol for your response,

Actually what I have right now is pick dict file along with pick data file, and I am parsing the dict file using python to get indexes for corresponding data from data file. It;d be great if you can explain a bit further as I don't have much knowledge about PICK and related commands.

[Ed Clark]

By “index” it sounds like what you actually mean is what in pick we call “attribute number”—the position of a field in a record?

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/ddcf92ef-ebc5-4524-a939-f2726ccddaa6%40googlegroups.com.

[salman javed]

Yes Ed, you are absolutely right about the Index thing, and I am trying to import data from PICK BASIC to PostgreSQL.

On Wednesday, August 28, 2019 at 9:43:01 PM UTC+5, Ed Clark wrote:

By “index” it sounds like what you actually mean is what in pick we call “attribute number”—the position of a field in a record?

On Aug 28, 2019, at 05:15, salman javed <salmanj...@gmail.com> wrote:

Thanks Wol for your response,

Actually what I have right now is pick dict file along with pick data file, and I am parsing the dict file using python to get indexes for corresponding data from data file. It;d be great if you can explain a bit further as I don't have much knowledge about PICK and related commands.

On Wednesday, August 28, 2019 at 1:56:05 PM UTC+5, Wol wrote:

On 28/08/19 09:20, salman javed wrote:
>
> My question is what is the optimal and generic way of finding indexes
> for data in PICK dict file to access the data in associated data file.

There should be a LIST.INDEX command? Either at TCL (the colon/command
line) or as a BASIC subroutine.

Cheers,
Wol

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com

To unsubscribe, email to: mvd...@googlegroups.com

For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mvd...@googlegroups.com.

[Tony Gravagno]

@Salmon, you still need to tell us which MV platform you're using. Specifics for solutions will differ depending on the platform.

But we also need to get the terms correct. You say "Pick BASIC to PostgreSQL". You won't get BASIC to run in that RDBMS but I know you're talking about moving MVDBMS _data_ to the RDBMS, not the code.

There are tools and products for moving data between environments. I recently "pseudo-announced" one in another recent thread.

It would really help if you tell us Exactly what your project is and what you expect to do. Then we can help you to accomplish your goal. Right now you are trying to communicate what you are doing, and asking us to help you fix that. Your approach, not being a Pick guy, is probably not the right one. It would be better for all of us if we define and use the right tools for this job.

(Wink to Chandru)

I hope that helps.

T

[Wol's lists]

On 28/08/2019 18:30, salman javed wrote:
> Yes Ed, you are absolutely right about the Index thing, and I am trying
> to import data from PICK BASIC to PostgreSQL.
>

Okay. Take a look at the following web page
https://www.pickwiki.com/index.php/ODBC96

Whatever MV database you are using, they should have some way of
exporting data in response to SQL queries.

There is also a whole bunch of code on that site, which will enable you
to dump an MV file in a layout that you can import into SQL.

I might be able to find it - I wrote a routine that takes a Pick FILE
and dumped a script that creates the equivalent SQL table(s).

Just be aware that pretty much any of these tools assume that the
dictionary accurately DEscribes the data. Note that relational
dictionaries PREscribe the data - very different!

Cheers,
Wol

[Dawn Wolthuis]

I'll add just one more little tidbit, although there are many nuances to be sure -- the dictionary could have multiple descriptions of the same location (index, as you called it). There can be synonyms of varying types, one of which is simply to put multiple descriptions for the same location. There are many utilities, whether from DBMS vendors or third parties, that put MultiValue data into first normal form in a SQL DBMS. 

While there are still many reasons that someone might want to take non-first-normal form (NF2) data and normalize it to align with various products and processes, the industry is moving in the other direction for very good reasons. Best.   --Dawn

Dawn M. Wolthuis President, Tincat Group, Inc. 616-901-6293 | dw...@tincat-group.com Take and give some delight today

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com

To unsubscribe, email to: mvdbms+un...@googlegroups.com

For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/9057504d-ccd3-e7d8-8cf0-6b2b62f67a2d%40youngman.org.uk.

[Dawn Wolthuis]

As Tony indicated, it would be helpful to know which DBMS product (perhaps also using which flavor) you are using. 

When porting to PostgreSQL, another thing you might hit are null-handling issues. The different DBMS products (such as UniData vs UniVerse) have different approaches to null-handling. My recollection is that UniData has nice, clean boolean logic where nulls are "a value" whereas UniVerse introduced a SQL-like null for 3-valued logic. You will want to know whether the Pick null you are working with should become a SQL null or not. There could even be situations where you would want to map the null to 0. Switching from boolean to 3VL can introduce errors on the SQL side if the values or lack thereof are misconstrued. Most MV applications work from the standpoint that a equals b if both are null.

Also, do not use the length in the Pick dictionaries as a maxlen. Those are display lengths. The values wrap in output. Pick fields are all variable in length.

When doing joins on the PostgreSQL side to paste the data back together, heads up for the multiple 1-M (coming from multiple multivalues associated with an entity). I don't recall what the PostgreSQL implementation of a star join is like, but those are frequently useful. Also, take note of the arity of the multivalues, especially if the DICT metadata is not consistent enough in identifying "associations."

I would love to hear how the project goes and what the data will be used for on the PostgreSQL side -- are you migrating the entire app, the BI, setting up ecommerce or something else?

Thanks.   --Dawn

Dawn M. Wolthuis President, Tincat Group, Inc. 616-901-6293 | dw...@tincat-group.com Take and give some delight today

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com

To unsubscribe, email to: mvdbms+un...@googlegroups.com

For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/89edf5cc-9c61-4b17-896a-dfea5d0e1cc7%40googlegroups.com.