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)
For i = 0 To rst.Fields.Count - 1
rst.Fields(i) = FVals(i)
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.
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.
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.
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 txt=fs.opentextfile(<path and name>, 1) ' ForReading
do while not txt.atendofstream
s = txt.readline
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.