Re: XL -> 400

One method I often use that's not been covered.

Using Access,
--create a linked table to the Excel worksheet
--create a linked tabled to the System i table.
--(optional) create a query that reads & reformats as required the
data from the Excel
--create an append query to add the data to the System i table.

Here's an example of a step #3 query:
SELECT F36_months_Reconvert.Loc AS LOCNBR, CInt(nz([contract],0)) AS CTRNBR,
F36_months_Reconvert.Cust AS CUSNBR, F36_months_Reconvert.Catg AS ITMCTG,
F36_months_Reconvert.dept AS DPTCDE, CInt(nz([Service Month],0)) AS TGTSVM,
F36_months_Reconvert.[Equip Status] AS EQSTAT
FROM F36_months_Reconvert;

Here's the corresponding step #4 query:
INSERT INTO CINTASDTA_F36_MONTHS_TEST ( LOCNBR, CTRNBR, CUSNBR, ITMCTG,
DPTCDE, TGTSVM, EQSTAT )
SELECT F36_Months_Reconvert_Query.LOCNBR, F36_Months_Reconvert_Query.CTRNBR,
F36_Months_Reconvert_Query.CUSNBR, F36_Months_Reconvert_Query.ITMCTG,
F36_Months_Reconvert_Query.DPTCDE, F36_Months_Reconvert_Query.TGTSVM,
F36_Months_Reconvert_Query.EQSTAT
FROM F36_Months_Reconvert_Query;

I prefer this method over the others if the Excel data is not clean,
ie. numbers store as text or vice versa.

Plus this way I've got direct control over the SQL statements being used.

HTH,
Charles Wilt




On Fri, Oct 10, 2008 at 12:28 PM, Al Mac Wheel <macwheel99@xxxxxxxxxx> wrote:

We're on AS/400 Model 170 with V5R1, due to BPCS cost-prohibitive to
upgrade, and I'm limited to native/400 & maybe shareware if I can get it
uploaded.
I got an Excel from another company.
We want to import some columns to a PF/400, to eventually match similar data.

What alternate ways are available to accomplish that?
FTP CA IFS
Ops Nav in reverse
Can I SEU/PDM a cut & paste into a PF?
Where's those ways how-to documented?

You could point me to a recent thread I did not pay attention to at the time.

I would also like a technique that is easy for use by non-technical
co-workers & safe.
I don't want a co-worker inputting such a file to overlay good stuff.
-
Al Macintyre


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.