Re: Removing an EOF marker in a text file for importation

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello John,
Will send a sample file via e-mail and will continue to explore my options
of producing the main frame file and getting rid of the EOF marker.
Karlene

"John Nurick" wrote:

> Karlene,
>
> 0D 0A at the end of each line is the Windows standard, and Access 2003
> isn't bothered by a 1A at the end of the file, so something else must be
> going on.
>
> In your place, the next thing I'd do is run some tests on the text file,
> checking for things such as the wrong number of fields in one or more
> records, missing delimiters/qualifiers, line breaks within the data,
> wrongly escapedd quote marks, etc. (Though if as you say the file can be
> imported after you have opened it and saved it in Notepad, that doesn't
> sound plausible either.)
>
> If the file isn't confidential and isn't too large, I'd be happy to run
> some tests on it if you zip it up and email it (omitting the bogus
> middle name from my email address).
>
>
> On Thu, 12 May 2005 04:55:21 -0700, "berybery"
> <berybery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >Good morning John,
> >The record separator is identified by both the "OD and OA" characters. At
> >the end of the file we have the OD, OA and 1A characters.
> >
> >I am interested though, in your last suggestion, running the file through a
> >filter or using some VB code.
> >Thank you,
> >Karlene
> >
> >"John Nurick" wrote:
> >
> >> Karlene,
> >>
> >> Now I'm mystified. Ctrl-Z (hex 1A, ASCII 26) is Windows's ancestral EOF
> >> marker and, as I said in my first message, it doesn't normally cause any
> >> problems and certainly doesn't screw up the preview in the text import
> >> wizard. So I'm now fairly confident that your problem has nothing to do
> >> with the EOF marker.
> >>
> >> Instead, I think it's the record separators (line breaks). As the file
> >> has come from a non-Windows system they may well be something other than
> >> the Windows-standard hex 0D 0A (CR LF). Access versions up to 97
> >> accepted plain CR or plain LF, but more recent versions require the two
> >> characters CR LF.
> >>
> >> What's more, when you open such a file in Notepad, edit it and save it,
> >> Notepad writes it back to disk with CR LF pairs, after which Access can
> >> handle it OK.
> >>
> >> Please check with Hexedit what record separator the text file uses. If
> >> it's just CR or just LF, you need to get it changed to CR LF. The best
> >> way to do this (if possible) would be to get the mainframe people to
> >> modify the script that creates the file.
> >>
> >> If that's not possible, post back here: you'll need either to run the
> >> file through a filter before importing it, or to write custom VBA code
> >> to read and import the non-standard file.
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Wed, 11 May 2005 12:14:04 -0700, "berybery"
> >> <berybery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >>
> >> >Hello John,
> >> >Opened the file in Hexedit and it is a "Control-Z" character with a "/A" hex
> >> >value and an ASCII value of 26.
> >> >
> >> >I am going to explore the option of the mainframe throwing out the file
> >> >minus the EOF marker.
> >> >
> >> >What I discovered though, is that if I open and save the file as a notepad
> >> >file, then the file goes into ACCESS nicely with the end of file marker. This
> >> >is one way of working around the problem, but I'm still not too happy about
> >> >having to open the file with "live" data to do that.
> >> >
> >> >Karlene
> >> >
> >> >"John Nurick" wrote:
> >> >
> >> >> Karlene,
> >> >>
> >> >> Recent versions of Access are fussier about the text files they'll
> >> >> import, though the problems I've seen to date involve end-of-line
> >> >> markers rather than end-of-file ones.
> >> >>
> >> >> But I can't imagine any single character at the *end* of a file mucking
> >> >> up the preview in the text import wizard, and I've tested the EOF
> >> >> characters I know of without provoking the trouble. So unless you tell
> >> >> me which actual character is used as EOF in your system, I can't
> >> >> reproduce the problem. (One way of finding out is to open the text file
> >> >> with a hex editor such as Hexedit.)
> >> >>
> >> >> I don't know a neat way of removing the last character from a file using
> >> >> VBA. Usually it's necessary to write all but the last character to a new
> >> >> file, and I'd probably do this by filtering it through a Perl one-liner,
> >> >> e.g.
> >> >> perl -0 -pe"chop" old.txt > new.txt
> >> >> if the file is small enough to fit in memory. Maybe you could get the
> >> >> mainframe people to do something like this as part of generating the
> >> >> ASCII file for you.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> On Tue, 10 May 2005 06:28:02 -0700, "berybery"
> >> >> <berybery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >> >>
> >> >> >Hello John,
> >> >> >The file is coming from a VSE\ESA main frame environment, using the file
> >> >> >transfer feature, from an EBCIDEC to ASCII format with this EOF marker, that
> >> >> >looks like a little square.
> >> >> >
> >> >> >Believe it or not, Access 2.0 takes this file with the EOF marker with no
> >> >> >worries. I'm actually trying to upgrade an old Access 2.0 application to
> >> >> >Access 2003. The EOF marker is giving me problems. I do not want to manually
> >> >> >remove it. Any help will be appreciated. Thank you
> >> >> >Karlene
> >> >> >
> >> >> >"John Nurick" wrote:
> >> >> >
> >> >> >> Hi Karlene,
> >> >> >>
> >> >> >> Windows text files don't normally have or need special terminating
> >> >> >> symbols (their size is stored in the directory entry). Anyway, using
> >> >> >> Access 2003 I have no problems importing text files terminated with or
> >> >> >> including the CP/M-DOS EOF character (\x1A), and the Unix equivalent
> >> >> >> (\x04) seems to be treated as an ordinary character.
> >> >> >>
> >> >> >> So I'm not sure what you mean by "the EOF marker", or what sort of text
> >> >> >> files you are trying to import. Can you give some more details?
> >> >> >>
> >> >> >>
> >> >> >> On Mon, 9 May 2005 07:33:11 -0700, "berybery"
> >> >> >> <berybery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >> >> >>
> >> >> >> >Hello,
> >> >> >> >I'm using ACCESS 2003.
> >> >> >> >
> >> >> >> >Whenever I try to import a text file into ACCESS, the sample display in the
> >> >> >> >Import/Export wizard shows me garbage. If I remove the EOF marker in the text
> >> >> >> >file it comes into ACCESS nicely.
> >> >> >> >
> >> >> >> >Is there a way for ACCESS to remove the EOF marker for me, without me having
> >> >> >> >to do it manually?
> >> >> >> >Thank you,
> >> >> >> >Karlene
> >> >> >>
> >> >> >> --
> >> >> >> John Nurick [Microsoft Access MVP]
> >> >> >>
> >> >> >> Please respond in the newgroup and not by email.
> >> >> >>
> >> >>
> >> >> --
> >> >> John Nurick [Microsoft Access MVP]
> >> >>
> >> >> Please respond in the newgroup and not by email.
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
.

Relevant Pages

  • Re: Windows variables in macros
    ... With respect to Doug and John, ... you said you wanted to use it, but, in what way is somewhat confusing to ... Then when the user clicks the button, notepad will open in the users ...
    (microsoft.public.access.devtoolkits)
  • Re: drop down menu
    ... There were some tag errors. ... Also, copy and paste from here to notepad, and then from notepad to your page with the page in code ... "John" wrote in message ... >>Microsoft MVP FrontPage ...
    (microsoft.public.frontpage.client)
  • Re: drop down menu
    ... "John" wrote in message ... |>"Steve Easton" wrote in message ... |>> Also, copy and paste from here to notepad, and then ... |>> Microsoft MVP FrontPage ...
    (microsoft.public.frontpage.client)
  • Re: HOSTS file
    ... john r wrote: ... And you add items using Notepad in that form ... Alex Nichol MS MVP ...
    (microsoft.public.windowsxp.general)
  • Re: Removing an EOF marker in a text file for importation
    ... Good Morning John, ... > Hi Karlene, ... > Opening and saving in Notepad does this replacement without telling you. ... >> of producing the main frame file and getting rid of the EOF marker. ...
    (microsoft.public.access.externaldata)