AS/400 >> Uploading From A CSV File

by poddys » Sat, 29 Dec 2007 00:42:45 GMT

We have data files coming from the bank which we have to download in
CSV format. We then need to upload these into an AS400 file using
Client Access. Some of the fields appear in the CSV file as 1.2345E
+11 for example, which I know is that value times 10 to the power of
11. Is there an easy way to deal with this?

Some of these fields are actually references, not numeric, so we can't
just upload them into a numeric field on the AS400.

I know I can write a routine to determine if the field is in this
format and to multiply it out, but I thought there might be a standard
way to deal with it.

Thanks in advance.

AS/400 >> Uploading From A CSV File

by jacko » Sat, 29 Dec 2007 02:15:38 GMT

Build a file transfer and create an ISERIES file on the 400 from the
CSV, click the tools icon and look for Create Iseries Database file, I
am sure there are other alternatives to what your looking to do.

AS/400 >> Uploading From A CSV File

by poddys » Sat, 29 Dec 2007 02:26:24 GMT

Thanks Jacko,

I have done a lot of transfers up and down from the AS400 using CA,
but not usually uploading a CSV.
Problem is that the application that generated the CSV stored some
values as a number with decimals places and then 10 to the power of x.
If I open the CSV in Excel and then define the column as Numeric it
formats correctly, but I can't find a way for CA to do it, I am left
with an Alpha field with values of the format 1.2345E+10 (which should
be 12345000000).
I'd rather not have to write a routine to identify and translate these
if there is a simple way of doing it.

AS/400 >> Uploading From A CSV File

by Peter Kinsman » Sat, 29 Dec 2007 16:31:47 GMT

Can you not import the file into Excel and then save it as another csv file?


AS/400 >> Uploading From A CSV File

by gb » Sat, 29 Dec 2007 20:27:01 GMT

Are you sure that they are stored in the CSV file as 1.2345E+11, I ask
this because if you open a CSV in Excel, it will display very large
numbers in this format if the column isn't large enough. To be
absolutely sure, open the CSV in a text editor. I would be very
surprised if the CSV actually contained 1.2345E+11 - especially if its
not a "numeric" but intended to be a reference e.g. an account number or


AS/400 >> Uploading From A CSV File

by davesNotHere » Sun, 30 Dec 2007 02:03:54 GMT

In article <4d6eb83f-34ea-4ada-8182-c560462c2397>, XXXX@XXXXX.COM says...

Personally, when I have this type of data migration to do, I use
Microsoft Access as an intermediary. I import the data into Access then
export the data to the '400 via ODBC. It usually takes care of most data
transformation problems automatically (although I usually have to
manipulate it a wee bit more on the '400-side).

Check it out, and let me know how it works for you.

-- DaveSlash

Similar Threads

1. Export to CSV-File

2. Import of csv-File

Hello all,

I would like to import a comma separated values file to DB2 UDB 8.2.
This file also contains values of type DATE and TIMESTAMP having for example 
the following format 900925 033701.0000 (yyMMdd HHmmss.ffff).

For the import, I right-click on the table -> Import and then choose the 
file with file type "DEL" and confirm the mapping given in "columns".
But as result, I get that all rows are refused. Could I somewhere find more 
detailed information WHICH column causes the problem?
With trying out and leaving away different columns, I found out that the 
error comes from the DATE and TIMESTAMP column, but is there a possibility 
to find it out directly after getting the result that rows are refused?

I think that "my" DATE and TIMESTAMP columns cound not be inserted because 
of the format. How could I tell DB2 which format my date have? Is there 
anything like DTS for MS SQL Server 2000 also for IBM DB2?

Would be glad if anybody could help me in this topic.


3. export and dateformat to produce CSV file

4. CSV file with Headers

All -

I am trying to figure out how to get a comma delimited file from the
command line which includes the column headings as the first line.  I
know how to create a del file using export, but it doesn't put the
headings.  I also know I could redirect the output from a select, but a
select isn't comma delimited... Any ideas?

5. fyi load csv file from windows

6. getting csv file from IFS to DB2

Anyone have a tool to do this, I don't have iseries access license.

7. iseries csv file structure - AS/400

8. how do I import csv file

I have a land file downloaded from the property appraiser's web site as a 
.csv file, that I can open in excel.

I've tried exporting to text but nothing lines up.

I've tried importing from csv into access and sql server, but the file I get 
on my AS400 after I transfer it with client access has some type of tabs or 
something between fields, and the AS400 gives a message that data is invalid 
and it will not even read it.

How would you go about getting a file of names, addresses, parcel codes, 
etc. from my PC into an AS400?

Thank you in advance for your help,
Joe in Florida