Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: reading Unix files from Access

    I'm new to Access programming, and need to read in some data from a (largish) Unix file.
    My code needs help with Unix delimiters.
    The input file is Tab-delimited.

    Delim = Chr(9)
    Open InputFile For Input As #1
    Set db = CurrentDb
    Set rst = db.OpenRecordset(TargetTable)
    Do Until EOF(1)
    Line Input #1, InpString
    FVals = Split(InpString, Delim)
    rst.AddNew
    For i = 0 To rst.Fields.Count - 1
    rst.Fields(i) = FVals(i)
    Next
    rst.Update
    Loop
    rst.Close


    Thanks in advance...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem is going to be how you instruct the application to interpret the end of line character

    rather than using readln perhaps you should look at read and examine each character to see if its a UNIX end of line delimiter

    have a look at the streamreadenum & streamread function

    I think you can specify what the end of line character is in the stream function - never tried it myself, but I seem to rememeber that it will handle these sort of problems.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Before you go and write your own import, I would take a look at the Import Wizard that comes with Access. It will handle a tab delimited file. Essentially you link to the file and then use it like a table. Or if you would rather, you can import the entire file into a new table. To get started, go to File/Get External Data and choose Import or Link Tables. Then the Import Wizard comes up. You can hit the Advanced button and name each field and tell Access the type of data in each field. Then save the File Specification and use it later in code. To use it in code look into the TransferText method of the DoCmd object.

  4. #4
    Join Date
    Feb 2005
    Posts
    3
    Sadly the Import way does not work for me, as the file concerned has approx 60 lines of Header banff, preventing Access doing that ol' delimiter magic.
    Also, I need to do a whole lot of other stuff as well, which Macros do not handle....
    Also, the file is ~36000 records long, and I'm concerned with the runtime of Char by Char reads.

    Is there a way to set a mnemonic eg UNIX_EOF?

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I was forced to do a character by character function to read a line of a file because the data had commas in it. Apparently the getting a line from a file terminates after a comma or a CRLF. I don't really know how it will work with 36,000 records, but a few thousand was pretty quick. It's hard to tell because I also had to check for many other situations that slowed the process. However, in general file manipulation in Access is fairly quick. I routinely create forms that will open a file, count the number of records in the file and put the quantity in a list box for the user to see. And I do it with files that are as large or larger that what you are working with and it returns a count in a few seconds.

    Just a suggestion, you might also want to look into the FreeFile function to get your file reference.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Have you tried using filesystemobject in stead of this method?

    dim fs as object ' or scripting.filesystemobject
    dim txt as object ' or scripting.textstream
    set fs=createobject("scripting.filesystemobject")
    set txt=fs.opentextfile(<path and name>, 1) ' ForReading
    do while not txt.atendofstream
    s = txt.readline
    ' process
    loop
    txt.close
    set txt=nothing
    set fs=nothing


    Library, Microsoft Scripting Runtime

    For speed, often reading the whole file into memory, and then processing may be faster than lots of IO. Textstream object has a .ReadAll method.
    Roy-Vidar

  7. #7
    Join Date
    Feb 2005
    Posts
    3
    Thanks, all for your assistance.
    Doing the Char by Char thing worked, - found I needed to do this anyway to handle the header lines (delimiters & arrays)

    Is there a resource/site that publishes values for Constants such as EOF, EOR ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •