Hi, I am trying to import some records from a delimited text file and link them to a record in another table.
My situation is that I need to make transmittal documents of drawings I am sending in the office. Our drawing program can spit out the text file with all the info I need, however it is too complex to make it actual csv text files, so it’s actually fixed width but with a delimiter (access seems to remove the extra spaces anyway, so all good). In my database there is a table of transmittal documents, and then another table that stores the contents (drawing info) of the transmittals. They are linked via the transmittals id. In the old days, I used to add the transmittal number to the text file, because the transmittal number was also the primary key. Then I could just import directly into the content table with docmd.transfertext.
But now the number is no longer the key, so I added a separate id field. I do not want to write the transmittal id into my text file anymore either, because it allows mistakes (for example , trying to import into transmittal 15, but the file accidently says 14). I have form that opens the transmittal for editing. On this form is the import button, so when the file is imported I want it to link to whatever transmittal is ‘open’. Using the transfertext method doesn’t seem the best way to do this as;
- If I import directly into the content table, they wont have a transmittal number. I could then use an sql to change all the records with blank transmittals to the one open, but this may also link other records that may be in there with a transmittal number.
- I could import to a new temporary table, then read each record in the table and add it to the contents table with the link added. This seems a bit tedious.
I had a quick search on google and discovered an Open ???.txt For ??? As #? command, but from my quick look it appears it reads through the text file line by line. This would result in the ‘tedious’ problem as above. Are there any other ways I could achieve my goal?
-I could import to a new temporary table, then read each record in the table and add it to the contents table with the link added. This seems a bit tedious.
I had a quick search on google and discovered an Open ???.txt For ??? As #? command, but from my quick look it appears it reads through the text file line by line. This would result in the ‘tedious’ problem as above.
What do you mean by tedious? if the process is automatic it can run unattended, and except if you have millions of line to import each hour, I don't see where the problem could be.
Importing a text file is always a sequential process, whatever the method you use can be. I guess that you could import the whole file into memory in one operation but then the tedious process would be to parse the imported buffer, so nothing would be gained (and the bytes would still be read sequentially from the hard disk anyway).
Could you please clarify what you are looking for and what you indend to do?