RE: Uploading CSV and/or numeric data

[Joe Giusto <jgiusto@xxxxxxxxxxxxxx>]

Since you are using Access, why not create an ODBC Linked table to the AS400
PF and use an append query in access to add the sucked in records to the
linked table.  


 -----Original Message-----
From:   James Newman [mailto:jabezinc@xxxxxxxxxxx] 
Sent:   Tuesday, July 01, 2003 11:23 AM
To:     Midrange L
Subject:        Uploading CSV and/or numeric data

9402-200 running V3R2 (stop laughing Al).  The PC running Windows ME
(everyone else, stop laughing) and CA 3.2 is attached via TCP/IP.  I have a
program which dumps a comma-delimited text file and I'm attempting to get it
onto the 400 and into a PF.  I've successfully tested creating a file on the
400, downloading that file, and using that FDF to upload data in the same
layout.  I know you can specify that your data to upload is comma delimited
but when I do that I get a message saying the FDF hasn't been specified.

So I set about to put the data into a format that would be easy to upload.
I suck the CSV into Access and use a query to append the data to a table
that has custom formats of "000000.0000" which will force the fields into
the correct lengths, thus creating "fixed width" fields.  But exporting the
data loses the leading zeros and the decimals aren't aligned.  I've tried
upload numeric fields where the decimals aren't aligned and the 400 puked on
the non-aligned decimals.  So I try exporting the data as text and get the
non-aligned decimals.  Then I notice the "Save Formatted" checkbox which
offers me the choice of saving in Windows, MSDOS, Unicode, or Unicode UTF8.
I try all 4 and while I now get decimal alinged fields, I also get a line of
dashes (--------) between each line of data.  I could write a VB program to
strip out the lines with dashes.

In a subsequent test I notice that one of the formats I can export the data
in is ODBC.  So I select this, attach to the 400, and the file
auto-magically appears on the 400.  But when I look at the data, the first 3
or 4 fields (which are text) look fine but the rest (which are numeric) are
gibberish.  Not hex - I know hex - this is gibberish.  Characters I've only
seen on "The Matrix".

Is there something really easy I'm missing about:
1)  uploading a CSV file to the 400,
2)  dumping the data out of Access in fixed width fields,
3)  sending numeric data via ODBC?

And yes, I know if I were on V5R1 this would all be a lot easier.


_______________________________________________
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.