Import problem from AS400

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a import problem, I have to pull data from J. D Edwards As400 (DB2) files. My IT department has insisted that all access to the 400 is by writing a world writer report to a file. World Writer is a clunky report writer that comes with JDE. My problem is that I get a square box at the end of some of the fields, and not on every record. I have tried to use right() and trim() to get rid of the problem but access must be seeing this as a character so it will not trim it away. I have tried to find a key that is the same as this but have not been able to. I tried to paste a sample of what I am talking about but the little square box does not come through in the past. Any suggestions on how I can get rid of this will be great. The only way I have found so far is to manually delete it on each record. Not a feasible solution when I have 10 to 20 k records. IT has no clue on how to get rid of it either.
Thanks for any help ahead of time.
 
You have to convert from EBCDIC (as400 ) to ascii (windows).

This process sucks. You will want to throw yourself from a window rather
than ever have to face it. You will find yourself begging that someone drop
the as/400 on your head rather than have to think about it for one more
awful minute.
Still, I've heard that if you FTP the file from As400 to a windows machine,
the conversion takes place automatically.




khawkins said:
I have a import problem, I have to pull data from J. D Edwards As400 (DB2)
Click to expand...
files. My IT department has insisted that all access to the 400 is by
writing a world writer report to a file. World Writer is a clunky report
writer that comes with JDE. My problem is that I get a square box at the end
of some of the fields, and not on every record. I have tried to use right()
and trim() to get rid of the problem but access must be seeing this as a
character so it will not trim it away. I have tried to find a key that is
the same as this but have not been able to. I tried to paste a sample of
what I am talking about but the little square box does not come through in
the past. Any suggestions on how I can get rid of this will be great. The
only way I have found so far is to manually delete it on each record. Not a
feasible solution when I have 10 to 20 k records. IT has no clue on how to
get rid of it either.
 
Hi,

An update query like this will delete the last character from the field
unless it's a number, letter, number or full stop:

UPDATE MyTable
SET XXX = Left(XXX,Len(XXX)-1)
WHERE XXX Like "*[!.0-9A-Z]";

Quite likely the "boxes" are control characters - perhaps superfluous
linefeeds. Change the criterion to

WHERE Asc(Right(XXX, 1)) < 32

to catch these but allow all other characters.


I have a import problem, I have to pull data from J. D Edwards
Click to expand...
As400 (DB2) files. My IT department has insisted that all access to the
400 is by writing a world writer report to a file. World Writer is a
clunky report writer that comes with JDE. My problem is that I get a
square box at the end of some of the fields, and not on every record. I
have tried to use right() and trim() to get rid of the problem but
access must be seeing this as a character so it will not trim it away.
I have tried to find a key that is the same as this but have not been
able to. I tried to paste a sample of what I am talking about but the
little square box does not come through in the past. Any suggestions on
how I can get rid of this will be great. The only way I have found so
far is to manually delete it on each record. Not a feasible solution
when I have 10 to 20 k records. IT has no clue on how to get rid of it
either.
 
I do a lot of importing into Access 2000 from a commercial mortgage banking
application running on an AS/400. What works well for us is to have someone
write queries on the AS/400. Using the ODBC client access driver you can
get external data and link to each of these queries as if they were Access
tables. You can then append data from these linked tables into Access
tables using Access queries that you create, and you can then use additional
queries or VBA to clean up the data.

This seems a lot cleaner than having to deal with the report writer you
mentioned below.

Bill


khawkins said:
I have a import problem, I have to pull data from J. D Edwards As400 (DB2)
Click to expand...
files. My IT department has insisted that all access to the 400 is by
writing a world writer report to a file. World Writer is a clunky report
writer that comes with JDE. My problem is that I get a square box at the end
of some of the fields, and not on every record. I have tried to use right()
and trim() to get rid of the problem but access must be seeing this as a
character so it will not trim it away. I have tried to find a key that is
the same as this but have not been able to. I tried to paste a sample of
what I am talking about but the little square box does not come through in
the past. Any suggestions on how I can get rid of this will be great. The
only way I have found so far is to manually delete it on each record. Not a
feasible solution when I have 10 to 20 k records. IT has no clue on how to
get rid of it either.
 
I'll try again since my earlier posting failed to make it.

I do a lot of importing from a mortgage banking application running on an
AS400. The following steps work well for us:

1. Have someone write queries on the AS400.
2. In your Access application link to these queries using the ODBC client
access driver - in Access these linked tables look just like Access tables.
3. Write append queries in Access to import data from these linked tables
into your Access tables.
4. Clean up the imported data with additional Access queries or VBA.

This seems a lot cleaner than using the report writer you mentioned below.

Bill

khawkins said:
I have a import problem, I have to pull data from J. D Edwards As400 (DB2)
Click to expand...
files. My IT department has insisted that all access to the 400 is by
writing a world writer report to a file. World Writer is a clunky report
writer that comes with JDE. My problem is that I get a square box at the end
of some of the fields, and not on every record. I have tried to use right()
and trim() to get rid of the problem but access must be seeing this as a
character so it will not trim it away. I have tried to find a key that is
the same as this but have not been able to. I tried to paste a sample of
what I am talking about but the little square box does not come through in
the past. Any suggestions on how I can get rid of this will be great. The
only way I have found so far is to manually delete it on each record. Not a
feasible solution when I have 10 to 20 k records. IT has no clue on how to
get rid of it either.
 
Back
Top