Here's where I think my problem might be.  This is a very old system.  And part of the programming was done in S36 and runs an emulator (from what I am told) on the AS400 to make it work.

So, when I go into this particular file using DSPPFM the file is junk.  But when I go into other files I can read the data.

I think that's where my problem is.

When I use the iSeries Transfer on the file I can read on DSPPFM i get a clean .csv.  When i try on the other file.  I get junk again.

Old S/36 style files are "flat files" - basically database tables with a single field containing an entire row.  Programs read these files and move the data into program-described fields.  

If the file looks meaningless in DSPPFM, it is probably because it just contains numeric data.

If I could see the file, I probably tell you in a minute what it contains and how to go about extracting it.

Does the file contain anything confidential?  If not, download a binary copy using file transfer function (no conversion) and post it here.

DSPPFM -can- be used to read packed data, you just have to use F10 to put it into hex mode - and you have to know how to read packed numerics.  Packed isn't that complicated.  Decimal point is implied, so you have to know how the field is defined in a program or in the database in order to know where to put the decimal.  Positive numbers have a hex "F" as the last nibble, and negative numbers have a hex "D".

123.45 = x12345F  
-123.45 = x12345D

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c0925083170.htm

The file does have social security numbers, etc.  So I can't send it.

Here are a couple screen shots of a file that opens and the one that won't if that will help.

I didn't see an F10 option to put it in hex mode.
ScreenConnect-CC-TEST-2015-07-02-2.png
ScreenConnect-CC-TEST-2015-07-02-1.png

Yeah, WTHIST looks like it contains mostly packed numeric data.  Packed numeric fields don't display meaningful information on DSPPFM.

I didn't see an F10 option to put it in hex mode.

And yet that's exactly what F10 does :-).  

But I do understand why you'd want to confirm the function you're executing before hitting buttons blindly- that's just good sense.

Take a look at F24.  Or put the cursor on the function key row and press F1 for help.

Be aware that if this file contains any confidential info, and you post the hex display, many of us are going o be able to read it and understand it.

The file isn't really "bad" - it just contains numeric data.

Do a


and post the results.  If you see more than a single field listed in the DSPFFD, do a


as suggested by Thomas above and see if that gives you meaningful results.

Bad news for you is that if this -is- S/36 style flat-file data, the conversion process can be significantly more complicated than if it is DB2 data.  As Tom mentions above, one file can contain multiple different record types, and each record type can have a different layout.  Even your more "legible" files can still contain a packed field or two.

When dealing with DB2 files, we've got plenty of tools - like the IBM i Access for Windows File Transfer Function and the CPYTOIMPD command for example - to help get data extracted.  

With S/36 data, there is typically an intermediate step or two, or the need to write some custom data conversion programs.  Most of the time when I'm dealing with S/36 data, I'm converting it into IBM i DB2 tables / physical files, and I have 3rd-party tools I use to simplify that process.

I'm NOT a S/36 expert.  I've done some work on the S/36, the Advanced/36, and the IBM i S/36 Environment, but most of it has been conversion work.  I know that during conversions, we typically get file format information from one of two places:  

IDDU or
RPG program I and O specs.

When you do a S/36 to AS/400 (iSeries / IBM i) migration, there is a step where any IDDU (Interactive Data Definition Utility) data dictionaries are migrated (SAVEFLRD on S/36, RSTS36FLR on IBM i).  Data dictionaries define fields, records formats, and files.  Assuming we're dealing with migrated S/36 data, you can check and see if you have any migrated data dictionaries on your system using:

WRKOBJ *ALL/*ALL *DTADCT

If not, then you have to harvest layout info directly from RPG program F and I specs.

As mentioned above, DspPfm is really not the way to look at your data (since it doesn't work very well with some data-types). Stick with SQL or do:

runqry *none wtdataox/wtmemo

Either way, you know the table exists, and it's in the correct library, so using the  correct "naming convention" and library-list in your ODBC should allow you to find it.

HTH,
DaveSlash

When I display the data using this command

RUNQRY QRY(*NONE)  QRYFILE((WTDATAOX/WTHIST M070625))

The data is at least separated into columns and then I get at the bottom of the screen.

Cannot show this data at the display station.

This is the only machine I can work with remotely, is there a way to add this workstation/device to the file? Easily?

In the original question, you said that your main problem was "files ... are not showing up" when exporting into Access. Now, you're talking about some kind of bad data or data-translation issue on the green-screen.

So, what's the real issue?

i'm thinking it is a permissions issue now.  

When I run

RUNQRY QRY(*NONE)  QRYFILE((WTDATAOX/WTHIST M070625))

I get that I can't access the data at this station which could explain the whole problem.

Good point. If you don't have authority to view the data in the table, then NOTHING will work, will it?

Check the object-authorities:

DSPOBJAUT OBJ(WTDATAOX/WTHIST)  OBJTYPE(*FILE)    

Based on the results of that, I suppose the next step would be to get the object-owner to grant you authority.

HTH,
DaveSlash

I still get that "Cannot show this data at the display station".

Is it possible the workstation/device ID doesn't have access to the file?  I know the developer had limitations so that certain terminals could only do certain tasks.

> Is it possible the workstation/device ID doesn't have access to the file?

Yes. The creator of the file might have limited its accessibility. Confer with them on granting you access.

That's the problem.  They aren't helping.

I have the SECOFFR account and the account used by the developer that I can try.

"Cannot show this data at the display station" (QRY1041 - google it) message has nothing to do with security.  Query throws this message when you attempt to display a character field that contains binary (packed decimal, for example...) data - it thinks you are trying to display graphical data on a non-graphical workstation.

Put your cursor on the error message and press F1 to see the extended help text for the error message.  If you did that, you'd see something like this:

                         Display Formatted Message Text                        
                                                             System:   D001    
 Message ID . . . . . . . . . :   QRY1041                                      
 Message file . . . . . . . . :   QQRYMSG                                      
   Library  . . . . . . . . . :     QSYS                                        
                                                                               
 Message . . . . :   Cannot show this data at the display station.              
 Cause . . . . . :   Some output from your query cannot be shown at your        
   display station because it contains characters not supported by the device.  
   This may have occurred for one of the following reasons:                    
   -- Data in a field does not match the data type defined in the field        
     definitions (binary or packed decimal data may be stored in character      
     fields).                                                                  
   -- The record format allows data to be used in the wrong format.            
     A dup character (an asterisk with a dash above it) is shown in each        
   position that would have contained the character unable to be shown.        
 Recovery  . . . :   Do one of the following:                                  
   -- Ensure the data in each field matches the data type defined for the      
     field.                                                                    
  -- Change the query or the record format definition to omit those records or
    fields from the query output.                                              
  -- Run the query output to the printer and view the spooled or printed      
    output.                                                                    
Technical description . . . . . . . . :   To avoid the dump produced, use the  
  Work with Message Descriptions (WRKMSGD) command to change message CPF5192  
  to not dump data on this exception.                                          
    Performance of displayed reports that issue this message will be degraded  
  because each screen of data that causes this error will be presented twice.  
  The first attempt fails with the message CPF5192. Then the results are      
  displayed again after most of the data with an EBCDIC hexadecimal value less
  than X'40' is translated to be displayed as a X'1C'. To improve the          
  performance of the displayed report, the bad data should be corrected or    
   removed from the report.      

If you'd please just post the output from


like I asked earlier, it would end the mystery.  I think this is a program-described file.  If so, getting the data in usable format is liable to be little tricky, and will require access to the source code of one or more of the programs (most likely RPG) that access this file.  If there were IDDU definitions, I think RUNQRY would have picked them up.

I have a screen shot of that file on DSPPFD on an earlier post

I know the commands looks similar, but you posted a DSPPFM (Display Physical File Member).  I asked for a DSPFFD (Display File Field Description).

Sorry about that, here you go on the DSPFFD.
ScreenConnect-CC-TEST-2015-07-07-1.png
ScreenConnect-CC-TEST-2015-07-07-2.png

OK, this is a program-described file.  Might have been copied over from S/36 or Advanced/36 using DDM based on the field names - that's one place I can recall seeing this field naming pattern before.  

It contains 3 fields:

F00001 - 1 byte character field, probably a record type indicator

K00001 - 5 byte character field, originally used as a key field

F00002 - a 1294-byte character field.  This is most likely a variable format field that is program-described.  That means that you have to look at the programs that use it to determine how it is sub-divided in the program.  It is just described to DB2 as a 1294 byte chunk of data, and when it gets read into a program, the program looks at that first byte and uses that to determine how to break up this field.  Made-up example:

H00001FIELD1FIELD2FIELD3
D00001FIELD1..........FIELD2...FIELD3
D00001FIELD1..........FIELD2..........FIELD3
S00001FIELD1.........

"H" is a header row with a key of "00001" and F00002 divided into 3 6-byte sub-fields.
"D" are detail rows with a key of "00001" and F00002 divided into 16, 10, and 6 byte sub-fields.
"S" is a summary row with a key "00001" with a single 15 byte sub-field

Together, this imaginary example might represent a purchase order, for example, with a header, detail rows, and an summary row containing the total for the order.  No idea what "WT" represents in the file name, but that may give a clue to the type of data it contains.  If I could see a hex dump of the file, I might be able to give you an educated guess about how the data is subdivided.  Without that (and maybe even with that), I'd need to see the source code for one or more of the programs that use this file to determine how F00002 is sub-divided.

From the DSPPFM screen shot, I see two F00001 values:  a blank and an "A".  Maybe there are more, you'd have to inspect the rest of the file.  I see K00001 values that look like packed numerics, and I see F0002 subfields that look like packed numerics before and after column 43, and a possible character column 43 subfield containing and "A" or an "I".  Of course F00002 is 1294 bytes long, so there is a lot of data off the right side of the screen that we can't see.

Generally, to convert a file like this into a relational database, I would create one table for each record type based on the record format found in the associated RPG (or COBOL, or whatever) programs.  Then I'd copy all the H records to a new HEADER table, D records to a DETAIL table, and "S" records to a SUMMARY table.

Maybe another expert has a better solution.

Okay, this is way over my head.  I may just have to try to get the programmer to write something to dump the data out of the table if it's not something that be described how to do on the forum here.

Not a simple task.  If you have access to a programmer familiar with the system, that could certainly save a lot of time and frustration - if nothing else they might be able to provide you with layout information.

Okay, one last thought.  

I have attached what I get when I try to do a file transfer of the file through iseries.  Is that anything that can be worked with?
ss.JPG

Worked with... yes and no. Not "worked with" in a sense that many would like to go through the effort. It'd be much easier to do on IBM i before a transfer.

It's somewhat frustrating not to be able to do for you what could be done. I'm sure a number of us could make (relatively) short work of it if we had the system. It's just not easy doing the needed background knowledge transfer.

E.g., we might simply write an "old style" RPG program using a "program described" input file. It would directly convert the input into however many output database tables are needed to handle the different formats that are coming in. Or we might use multiple instances of the CPYF command to do the same separating into tables, by making use of the selection capabilities of the command along with its ability to overlay record format images onto database formats. Or... well, it's just frustrating.

It's not easy coming up with a good method in modern languages of handling files with mixed formats. So much of today's technology expects structured database access for database records.

Row 1:

F00001 - 1 byte character field = Blank

K00001 - 9-digit packed numeric field (decimal positions unknown, but since it is a key field it is probably 9,0 - "F" in the last nibble means it is a positive number) = 010150001

F00002 = Visible portion is a structure of packed numbers, all stuffed into a single column.  The "F" is the last position of each number, so what we see is probably a list of numeric zeroes:

0000000
0000000
0000000
0000000
0000000
000000...

But without looking at a program that uses this data, we still have no idea what it means.  Also, I recall that the F00002 column was 1294 bytes long, so there is no telling if the rest of it is laid out in a easy to decipher a fashion as this part is.  I remember from something you posted above that it isn't just a straight list of packed numbers.

In the time we've spent going back and forth over what is clearly a program-described file, a programmer familiar with program-described files and with access to source code would have already been able to extract it into something meaningful.

Assuming just a couple of simple record formats, and access to an RPG program that contains the record formats, it would take me on the order of an hour to do a basic extract on a file like this.

Since it sounds like you've got access to a programmer, I think you'll save yourself a lot of headache if you just ask for a little assistance.

You can use iSeries Data Extractor Free. It will export data from AS400 to Microsoft SQL Server, Oracle, MySql, or PostgreSQL directly. It is free software but has several limitations. You can get it through this : http://degipanju.blogspot.co.id/2016/10/export-data-from-iseries-to-rdbms.html