Hi everyone,
I am charged with the responsibility of creating a database that imports data from text files. One of the files is longer than 65,536 rows but if I open it in notepad, I can see all the rows.
Is it possible to write a vba procedure to automatically open the text file in notepad, and then populate an access table with this data?
I am very grateful for any help with this project.

Do you have to use notepad? Could you use Word?

Thanks for the post.
It seems that I do need to use Notepad as some of the rows are extremely long and Word mashes them together in an unusable way. Notepad lines them out properly.

So you are saving each line in notepad as a row on a table? Are you doing this to be able to recreate the notepad document?

Kevin

Yes, I want to recreate the document as a table and then perform the filtering with that table.

The reason I'm asking is you can save the whole document, as an OLE Object, which is a single row on a table. The object can then be edited by notepad.

Kevin

Kevin,
Thanks again. However, I am confused by the suggestion.
I cannot see how the text file can be saved as an OLE object. I've tried using "Save As" but it doesn't give that option. Even if I could, I don't want to edit the document on Notepad, as it appears more-or-less fine (there is another problem at that point but I need to take one-step-at-a-time, as I am new to Access).
Essentially, I seek to have a button on a form that, when clicked, automatically launches Notepad, opens the large file (85,000 rows) and then imports it into Access.

Thanks

Tim,
What type of data is in these rows? I'm trying to understand why you would want to have a Notepad text file, then save it line by line into an Access table.

Kevin

Kevin,
The entire project captures 13 daily files describing "Corporate Actions". The files are produced from an out-of-state mainframe and sent to us in Excel (they seem to be text-type, although the "file properties" calls them the current date!).
The actual data consists mostly of words and some numbers. Some of the fields are long (100 charcaters), others are very short (1 character). The vast majority of the rows consist of 4 columns. The others range from 20 columns to 82 columns.
Management wants to capture this daily info in an Access database.
If I attempt to import the data of the largest file (85000 rows) into its particular Access table, only the first 65,536 rows are opened. If I open the file with Notepad, the entire 85,000 rows are opened. It seems that I need to open the file in Notepad (from Access) and then import the file to its particular Access table.
This is the process that I want to automate. I do not want to "save" the rows "line-by-line" into Notepad: I just want to open it with notepad and then save it to Access.
Thanks Kevin

Tim,

Here's how you do it. This is the On_Click for your form's button.


Dim TextLine
Dim strquote As String
strquote = """"

Open "C:\TESTFILE.txt" For Input As #1 ' Open file.
DoCmd.SetWarnings False
Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    SQL = "INSERT INTO [Text Table] ( [text line] ) " _
          & "SELECT " & strquote & TextLine & strquote & " AS Expr1;"
    DoCmd.RunSQL SQL
Loop
Close #1 ' Close file.

---------------
C:\Testfile is the name of your file.
Since you have 13 of these you can replicate this. It doesn't use notepad, it opens the file directly.

Try one and see if it works.

any trouble, email me directly @ kevinpreilly at aol

kevin