John, you caught me. I'm often way too wordy, at least that's what I'm told by everybody else.
I attempted to say everything relevant to my situation and my questions, which basically are: Can it be done and where can I learn how to set it up? "It" meaning " to use SQL to import data from a PC .txt file into an IBM i data file", much the way Client Access Data transfer does, but with the convenience of SQL in selection of which columns and rows.
I've been using SQL for a number of years, mostly interactively on the IBM i and in the last few years within RPG as embedded SQL. I am a big fan. I am also a big fan of doing things simply, meaning the less steps the better. I figured (or hoped) that being able to use RDi's Data Studio to import data would be a "one step" solution to the multi-step solution of the Data Transfer method used for 30+ years on the IBM i.
The text files were just what I consider standard data files in text format which I obtained from a trusted web site. Basic data in column format with CR/LF at end of last column. Fortunately the two columns I needed were not numeric or date, both which would have made SQL the better choice for manipulation, but I digress.
While I considered putting the text files onto the IFS, my original thought still stands, why not see if SQL can read the PC .txt file and use the INSERT function along with a WHERE clause and Substring out the necessary columns. That's what I did after using Data Transfer to get the file into a DB2 database. The use of transferring the data to the IFS is still an extra step in some cases and yet wouldn't have been had I just downloaded the files directly to the IFS. Maybe next time I'll do that, but that isn't an answer to my questions.
RDi is my tool of choice for development. With the added benefit of creating SQL scripts, it's becoming a one-stop shop for me and I'd like to make the most use of it. There may be tools available somewhere that I haven't discovered or used yet (and I've probably only used a very small portion) that would make this task easier. But my first thought is to use tools that I know and are part of IBM's tool sets.
I'm familiar and have used CPYFRMIMPF, but again, not the answer I'm looking for, which is beginning to look like I'm not going to like the answer. And that's okay and why I worded my original email as I did. I really wanted to know if it was a common thing or not. It appears not to be common, which is a standard for me. I break ground on a lot of things within my small group of knowledgeable friends and co-workers.
I want to find the .txt file on my network drive and read it using SQL. If I can do that, I can possibly figure out how to insert the data I want into my IBM i file. It's really that simple and yet seems to be so complicated. Data Transfer has been doing PART of that for decades.
But, I vent.
Thanks for listening.
duane
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of John Yeung
Sent: Tuesday, August 30, 2016 12:47 PM
To: Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries <wdsci-l@xxxxxxxxxxxx>
Subject: Re: [WDSCI-L] Making the most, or over-thinking it - SQL for uploads
Duane, I am having trouble understanding your situation. You said:
importing data from PC to DB2 is done constantly
OK, that sounds completely normal and I'm following you so far.
using SQL is my FIRST thought over most of the tools that we have access to, simply because of its convenience. At least perceived convenience.
Things are already going off the rails here. Then you continue:
As to the method that I used to get the data this time, I used Access Client Solutions Data Transfer. But doing so forced me to load the entire record to a DB2 data file from which I used interactive SQL from an IBM i ACS emulation session to INSERT my selected fields.
What kind of text files are we talking about? And are these text files the "purest" source of this data, or are the text files just an intermediate format used for data transfer? For example, is the REAL data in another database, or an Excel file, or something else; and you just copied that into a text file because you figured that it's easier to get data from a text file into the i?
Until I get more details, the best my imagination can come up with is very much in-line with what Vern is thinking. Just get your text file into the IFS and take it from there.
I am not sure what is going on with your conversation with Buck. To me, it seems like Buck misunderstood your original post, and thus brought up lots of correct but irrelevant stuff, yet you seem to be good with his response. So I am really clueless there.
When you say things like:
I'm not familiar with setting up the PC to interact.
and
I really just figured that I could access text files similar to accessing them as done on IBM i.
These things make me wonder what RDi has to do with anything.
Finally,
I also figured that it was my own lack of experience and knowledge of setting up the PC that caused the inability to just find the file and open it up.
Um, "just find the file and open it up"? Find the file *from where* and open it up *with what*? It almost sounds like you want to be able to "find" the file from the i and open it up with whatever it is you use on the i to work with stream files (such as an RPG program or the EDTF command or CPYFRMSTMF or CPYFRMIMPF). There *are* ways to set up some disk space that is directly accessible from both the i and a PC (so you can just copy the data to that space from the PC side and pick it up from the i side).
I think we could all use some more information about your situation.
John Y.
--
This is the Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries (WDSCI-L) mailing list To post a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
________________________________
________________________________
CONFIDENTIALITY NOTICE: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact NALC Health Benefit Plan at 703-729-4677 and delete and destroy the original message and all copies.
midrange.com
