Import problem

[khawkins]

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.

 

[Ernie]

-----Original Message-----
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.

.

What is appearing as a box in Access is actually an end of
record marker. In most dbms' this character would remain
hidden, in access it shows up.

See if the function: CLEAN(text) is available and try
that. It is supposed to remove all unprintable characters
from (text).

If it is not available you should be able to use something
like Left(text, len(text) -1) unless text is a fixed
length field holding a variable length record (i.e. len
(text) is always the same regardless of how many actual
chars there are). If that still doesn't work you will need
to find out what ascii character is being placed there and
use a replace function to rid yourself of it.

HTH (advice from an access newbie)

 

[Arvin Meyer]

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.

What you're seeing is the Line Feed character (Chr(10) which is the line
terminator in all Unix text files. In DOS/Windows, there are 2 characters:

Chr(13) & Chr(10)

Carriage return and Line feed which terminate all lines. Try the following
code to run through all the text and replace it with the dual characters:

Public Function UNIX2DOS(ByVal str As String) As String
UNIX2DOS = Replace(str, Chr(10), Chr(13) & Chr(10))
End Function

After replacing Chr(10) with Chr(13) & Chr(10), everything should display
nicely.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Carolyn]

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.

What you're seeing is the Line Feed character (Chr(10) which is the line
terminator in all Unix text files. In DOS/Windows, there are 2 characters:

Chr(13) & Chr(10)

Carriage return and Line feed which terminate all lines. Try the following
code to run through all the text and replace it with the dual characters:

Public Function UNIX2DOS(ByVal str As String) As String
UNIX2DOS = Replace(str, Chr(10), Chr(13) & Chr(10))
End Function

After replacing Chr(10) with Chr(13) & Chr(10), everything should display
nicely.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Carolyn]

Arvin,

Thanks so much for your UNIX2DOS function. That took care of the "funny
little squares" in my memo fields. But I also have another character that I
can't quite identify but need to get rid of. It looks like a bolded vertical
line. Of course, if I try to type it in here, it just takes me to the next
line. I was hoping that it was Chr(13) so I tried a function like the one
you show below...except that I was trying to replace Chr(13) instead of
Chr(10). But that didn't work. Any ideas? Thanks!