Solved

Insert EOF Marker

Posted on 2000-05-12
5
736 Views
Last Modified: 2012-05-04
I have a database that is linking to text files.  The files will be changed frequently and the problem I have run into is that the files are created on a Unix machine that is not leaving a recognizable EOF marker.  I can manually open the file, put the curser to the right of the last character, hit the delete key, close the file and save changes. This corrects the problem.
Does anyone have an idea how I can automate this procedure from Access?
I'm posting the question at 75 points, but will double it if someone gives me a working solution by 9:00 AM ET Saturday.
0
Comment
Question by:Mach1pro
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
mberumen earned 150 total points
Comment Utility
MachPro.

I faced the same problem and to solve it I created a small Visual Basic Program that takes the file, opens it and places the right end of line character.
If you are unable to create this code by yourself, I could do it for you but I won't be available until monday

Have a good weekend!

the information is at

http://support.microsoft.com/support/kb/articles/Q149/9/46.asp

Certain text files are created with only a Chr(10) (linefeed character) at the end of each line to indicate a new record. The Import Wizard expects both a Chr(13) and a Chr(10) to recognize the end of a record. Furthermore, the character codes must be in this order: Chr(13) + Chr(10). Because neither Microsoft Access 7.0 nor 97 see this combination, they interpret the imported data as one record. Microsoft Word and Microsoft Access 2.0 on the other hand, recognize Chr(10) to indicate the end of a record.

NOTE: Unix text files use the line feed character at the end of the line. OS/2 and MS-DOS text files use the line feed and the carriage return together at the end of the line.



STATUS
This behavior is by design.



RESOLUTION
Use one of the following three methods to work around this behavior.

Method 1
Use a text editor (such as NotePad) to insert a Chr(13) after each record.
Method 2
Use Microsoft Word 6.0 or later to search for paragraph marks and replace them with carriage returns/line feeds.

For more information about finding and replacing paragraph marks in Microsoft Word, search the Microsoft Word Help Index for "find and replace," and then "Examples of special characters and document elements you can find and replace."
Method 3
Use the following steps to create a Visual Basic for Applications function that checks to see if a file contains only a CHR(10) at the end of each record and, if so, replaces it with a CHR(13) + CHR(10) so that Microsoft Access will be able to successfully import the records.

This part of the article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
Open any database and create a new module. Type the following line in the Declarations section if it is not already there:


Option Explicit
Type the following procedures:


      Function TestImportText(ImportTextFile As String)
        Dim x As String, y As String
        Dim NumberOfCarriageReturns As Long
        Dim NumberOfLineFeeds As Long

        NumberOfCarriageReturns = 0
        NumberOfLineFeeds = 0

        Open ImportTextFile For Input As #1
        Do Until EOF(1)
          Line Input #1, x
          If InStr(1, x, Chr(13)) > 0 Then
             NumberOfCarriageReturns = NumberOfCarriageReturns + 1
          End If
          If InStr(1, x, Chr(10)) > 0 Then
             NumberOfLineFeeds = NumberOfLineFeeds + 1
          End If
        Loop
        Close #1
      'If no Carriage returns found, run the next function to modify
      'the text file.

         If NumberOfCarriageReturns < NumberOfLineFeeds And _
            NumberOfLineFeeds > 0 Then
            Dim NameOfNewText As String
            NameOfNewText = InputBox( _
            "Enter The Name Of The New TextFile In Which To Save The _
                 Changes.")
            y = ImportText(ImportTextFile, NameOfNewText)
         End If
      End Function

      Function ImportText(OldText As String, NewText As String)
        Dim x As String, Endvalue As Integer
        Dim StartValue As Integer, OutputTxt As String

        Open OldText For Input As #1
        Open NewText For Output As #2
        Do Until EOF(1)
           Line Input #1, x
           Endvalue = InStr(1, x, Chr(10))
           StartValue = 1
           Do Until Endvalue = 0
              If Endvalue > 0 Then
                 OutputTxt = Mid(x, 1, (Endvalue - 1))
                 Print #2, OutputTxt
                 StartValue = Endvalue + 1
                 x = Mid(x, StartValue)
                 Endvalue = InStr(1, x, Chr(10))
              End If
           Loop
        Loop
        Close #1
        Close #2
        MsgBox NewText & " Successfully created."
      End Function
To run this function, type the following line in the Debug window, and then press ENTER
? TestImportText("<name of text file to import>")
where <name of text file to import> is the location of your file, for example:


? TestImportText("c:\my documents\testfile.txt")
If the text file does not need to show carriage returns, an input box will appear asking for the name of the new text file that is going to be created. This will prevent the original text file from being overwritten. Type in the name of the new text file and click OK. This will create a new text file that is in the proper format for Microsoft Access 7.0 or 97 to import.


0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Problem is the fact that Unix files have only Linefeed and Dos files Linefeed/CarriageReturn.
There are converters for translating the file to and fro Unix format.

In your case you could open the file in access binairy format and check for the chr(10) (linefeed) if so, write chr(13) & chr(10) after this conversion the file is "access-readable"
Normally however I would recommend a FTP to a DOS-machine and specify the LF => CRLF conversion.

Nico
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
BTW do you have to link to these files or could you use a VBA function to extract the data and insert it into your tables?
I assume conversion could take a while and when you are already processing the from VBA you could as well add the INSERT to it, so you don't have to write it anyway.

If you want a conversion utility UNIX2DOS then I can mail you one, but there must be lots of them on the web too.

Nico
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
Adjusted points from 75 to 150
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
mberubin
The part of the code that tests for the missing EOF  works fine. The correction part throws the program into an endless loop because because the Line Input command sees the entire file as one line. I have used the test part of your code with a few modifications and then created a form and routine to instruct the user how to fix the problem and opened up notepad to the file. Thanks for your help.  I didn't have time to search your link for additional info.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now