Download As400 Data To Excel [EXCLUSIVE]

So I accomplished to make the connection and I am able to access schema, table, views from Excel using data connection. But the problem is that some tables have multiple members in it and the excel's pulling data from what I am guessing as the first member that excel sees when all the important data are in other member files...

you can access the data in multi-member files via FTP. For example /QSYS.LIB/your_library.LIB/your_file.FILE will be considered a directly by FTP. Depending on the data structure you may be able to do it directly from Excel.

Download As400 Data To Excel

Download 🔥 https://bltlly.com/2y2G9j 🔥

This whole thing boils down to the following: Is it even possible to run the query from Excel essentially changing the data it contains and not just outputting it to excel? If this is possible, is it then possible to pass a parameter to the AS400 system and use it to create situation A, B or C?

Say i have different customers A and B. I can open the AS400 program and run a query in which i have specified that I only want data on customer A. I can then open Excel and use filters (as Hambone described) on the query to determine which records I want to output. However, if I want to work with data from customer B, I have to open the AS400 again and run the query with different parameters. I would like to be able to "change" my dataset from customer A to B from Excel, without having to include both in my recordset and then filter out one of them.

I imagined this is doable if you could pass a parameter to the AS400. The AS400 then runs the query using this parameter as the criteria for which records should be stored in the query. This means that if the parameter is Customer B, then there is no way to acces data from customer A, without running the query through AS400 again.

From here, any time you want to refresh the query, you can simply right-click the data table in Excel and select "refresh." Alternatively you can go to the data tab on the ribbon and select "Refresh."

I used to do a lot of macros in TEs. You have to understand that originally users on systems like AS400 didn't use desktop computers. They had devices called a "terminal" that was just a display and keyboard usually connected to the server via RS232 serial port. What we used to do, and this was 2 decades ago, was to use the serial port on a desktop computer and employ a terminal emulation software. Of course there are many different ways this can happen and I think the first thing you have to consider is what TE software you're using. I think As/400 uses TN5250 but I'm not sure. That's the data stream protocol. And then which emulator is interpreting it and displaying it for you. It could be any one of dozens. And some are even web based. So I doubt anyone here can tell you how to address this problem until we can establish what program MEP is interacting with. It's like asking me how to change the spark plugs on "Engine". Well what kind of engine? What kind of car? So you should probably look into that first.

But I can guess that you're probably running into the way the characters are being echoed to the screen. Like I know in some systems when you see a flashing background cursor the system is often switching back and forth the characters. Of the way the data is displayed is optional. For instance you might be able to turn off the flashing cursor. I think your solution might lie in these settings. But again we don't know what application it is and it's highly unlikely that anyone here knows this application. You might try getting some support from the program vendor or a user group for it.

Now when I had issues like this I'd actually collect the entire screen to the clipboard and then scrape the data out of it. That might work for you also. But really I'm just guessing. Again hard to give advice when we have no idea what program we're dealing with.

I don't recall having your specific problem copying fields. However it often was easier to use Cory's technique of grabbing the whole screen, then extracting the needed data. Using other methods, like tabbing around the screen, would screw up cursor positioning and upset the user.

If you can see the field(s) you want, then you can figure out how to pick them up with a macro, probably by pasting the clipboard into a ME variable, searching for [the location of] the label field that precedes the needed data, and based on that location copy the data field to another ME variable. If you have a hex editor it might help you, because the copied screen may contain unprintable characters, but you probably won't have to go that far.

Danner: I would also add that if you do screen scrapes and find is difficult to isolate your text ask me to introduce you to something called Regular Expressions. It was born to extract text from stuff like this. I use it a lot in my web scraping. And I could make you a little tool to do it for you that you can drive from MEP. Basically it does much the same thing that RBerg was describing but it's an engine that was developped a million years ago to do exactly this. I have wasted hundreds of man hours searching and extracting data and then I found RegEx does is all and a bit more. IN fact I've been thinking about starting an addon for MEP for RegEx. Might be a good time to start it.

Not even! I didn't get into that Digital until 1986 in college. I too have dealt with several mainframes like this. ProComm had a scription part I used a lot in place of old Wyse terminals. One thing that was nice about ProComm was you could tap into the serial data stream and even echo it to a file. The trick in this case is to find something like this for Danner.

I am trying to use the "get external data" option in EXCEL. I've been

able to query the AS/400 from ACCESS without too much trouble (well, it

won't let me do any complicated queries, but it's working OK). Now, I'd

like to make a query in MSQuery that runs w/in EXCEL so that my data is

directly pulled and placed into my spreadsheet. When I try to make the

query, it always writes the select statement like this:SELECT table.column1, table.column2 from *n.library.table table

^^^

My problem seems to be around the *n in front of the library. I've tried:systemname.library.table

systemname/library.table

systemname_library.table

systemname\library.table and even

library.tableNone of them work. Where am I going wrong?Thanks

also, How complex is complex? Sometimes you can do things in reports that

you can't do in queries. I can't seem to sort anything (AS/400 data) in a

query but I can build a report on the query results and sort the report. You

can also do a MAKE TABLE query and then do a second query against that table

to do the "complex" stuff.Chris Hagwood wrote in message ...

Well, the intended user of this spreadsheet doesn't have access installed,

so I'm trying to avoid access altogether.I don't mind using the "analyze with excel" option in access, but there

are others who don't have access, or aren't knowledgable enough to do

that each time. I'm hoping to save a query that they can run from excel

w/out any additional steps.Thanks for the suggestion.

I can't help you, but I wanted to let you know that I have the same problem

you do. I seem to have know problems using access to access the AS/400

data. But I get an error when I try to use Excel's "Get external Data"

function.--

Charles Wilt

Miami Luken, Inc.

Springboro, OH. 45066

e-mail: charle...@worldnet.no.spam.att.net

--remove the .no.spam

The correct syntax is systemname.library.table, as shown in the following

that I copied from a working query that I have:SELECT PSTRUC.PINBR, PSTRUC.USRS1, PSTRUC.CINBR, PSTRUC.USRS2, PSTRUC.MDATE,

PSTRUC.QTYPR

FROM GNPBAT.AMFLIBB.PSTRUC PSTRUC

ORDER BY PSTRUC.PINBR, PSTRUC.USRS1, PSTRUC.CINBR, PSTRUC.USRS2If this syntax doesn't work, you need to check your ODBC driver setup - you

need to make sure that the system name is explicitly specified in it. I

think the *N could be coming from it being blank, which could be the case if

you're only connecting to one AS/400 (I have two, so I can't leave it

blank).Once you get past this hurdle, I think you'll find that MSQuery into Excel

works very well - I have much better luck with this than with Access. The

one drawback is that since it always redisplays the retrieved data to you as

you make changes to the query, it can be sluggish setting up the definition

if there's a lot of data involved. It can also make your /400 response drag

if you don't have it tuned to isolate this stuff. Good luck!--

Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -

Bubba Makes Wheels :)

The opinions expressed may not be my employer's unless I'm sufficiently

persuasive...

Hi Dave,You can get around the re-retrieve of data when you make changes by turning

off the automatic query option from either the toolbar or under the records

option. It always does a full retrieve the first time you go into MSQuery

with a query though, which is a real pain.HTH

Allan

Sorry to pop here but, I am using the Excel to import data too and it

works well in the majority of files but I'm getting some problems in

files over more or less 800 lines. It starts inserting blank lines and

ASCII code after that number of lines.I have CA400 V3R1M2 SP SF46059.Thank's for any help.--

Manuel AmaroCIN, S.A. / I.T.Department

Portugal

Mailto:am...@cin.mai.mailpac.pt

>Hi Dave,

>Sorry to pop here but, I am using the Excel to import data too and it

>works well in the majority of files but I'm getting some problems in

>files over more or less 800 lines. It starts inserting blank lines and

>ASCII code after that number of lines.

>I have CA400 V3R1M2 SP SF46059.

Strange. The SQL that I posted downloads 29,757 records without any

problems. I'm also on CA400 V3R1M2 SP SF46059. What version of Excel are

you running? I'm using Excel 97 on Windows 95B, running on a Pentium MMX

233 with 32 MB of memory. My AS/400 is running V3R7, with the C7343370 cume

installed. I've also installed all the relevant PTF's mentioned in the

CA/400 informational APAR, II10303, as of about a month ago. There were

some database PTF's in there, perhaps one of them has some bearing. II10303

lists the correct PTF's for each OS/400 release - if you haven't reviewed it

against the PTF's installed on your /400, you should. It's supposed to be

out on the Web somewhere, but I normally just use SNDPTFORD to download it

to the /400 when I I want to.Oh, it probably doesn't matter, but I'm connecting over Ethernet through a