Import Problems

  • 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.
 
After you import the data, you could run an update query on the data to
remove the last character if it is not an accepted character (such as a
letter or a number or a punctuation mark). However, the easier thing to do
is to identify which character it is in ASCII value and then strip off the
last character via update query when it matches this ASCII value.

Grab a copy of the last character, go to the Immediate Window in Visual
Basic Editor, and type this in the window:

?Asc(x)

but replace x with the character. This will tell you the ASCII value (call
it 128 for simplicity right now)of the character, and you then can check if
the last character is that value (=Chr(128)) and if it is, remove it from
the text string via an update query.

--

Ken Snell
<MS ACCESS MVP>

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.
 
you could substring the text string to return all but the last byte - my assumption is that your report writer is returning a string terminator (such as a single line feed) that is not represented as an ascii character.

Using an update query, create the expression as follows:

Mid(Name,1,Len(Name)-1) AS NewColumn

where Name is the field you wish to truncate.

note: test the function by creating a new column (in this case NewColumn), and when it proves correct, use it in the update function.

Hope this helps...

Don
 
Back
Top