Solved: Import only first and last lines of an ascII file into access

[knifelong]

Hi!

I have a number of files in ascII format for which I would like to import the first and last line/record of each into access. I would like to automate this process as there could be up to a hundred files and as these are such large files 3-4 million lines of text I don't particularly want to import all records into access as a temp file as this would take forever to do a large number of files.

I would just like the first and last record of each file displayed in access. I guess this would be easier with another language but as I am trying to get familiar with VBA I wondered if it was possible in access.


Here is an example of the ascII text files I am using. The output doesn't have to be deliminated by field, one whole line can go into one field.


j9998 047c19610701 1038120060814 5200612182006081420061218
j9998 047x19740701 29220060817 2000000002006081720071031
j9998 048O19300701 2630220060825 2000000002006082520071031
j9998 048R19750701 14682220060825 2000000002006082520071031
j9998 048V19920201 18331220060825 2000000002006082520071031
j9998 048l19640701 16903220060904 2000000002006090420071031
j9998 049119730701 13927120060911 2000000002006091120071031

Thanks

[CreganTur]

The following code will pull the first and last line of a text file using an ADO connection.

It uses the MoveFirst and MoveLast methods of the Recordset object. This will explicitly find the very first line of the text file, and the very last line.

Here's the code:
Sub OpenTextFile()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set conn = New ADODB.Connection
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & "C:\" '<<<Put filepath to folder containing txt file here
Set rst = New ADODB.Recordset
'connect via SQL SELECT all statement- pulls everything in txt file
'place name of txt file you want to work with inside brackets
rst.Open "SELECT * FROM [FirstLastTest.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
rst.MoveFirst
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
rst.MoveLast
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
'Close and release connections
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub

Testing the Code:
Place this code into a module in your database. Then place your cursor anywhere inside the code and press F5- this will run the procedure.
-I've included the test .txt file used in the procedure as an attachment. Just download it straight to your C:\ drive and unzip to test.

Are you going to use this to evaluate all .txt files in a given folder, or will you need a way to pick the ones you want to evaluate? You may already have something setup to do this, but if not post back what you need and I'll try to help :thumb

[knifelong]

Hi!

Thanks very much for your reply.
Excuse my ignorance but I'm very new to VBA and learning as I go along. This is also the first time I have come across ADO connection. I was reading through the code, the best I could, and I can see the part to make an ADO connection, running a select statement and retrieving the first and last from the recordset. However, although the code runs from the module I can't figure out where the output goes, or how to see it. Could you tell me what I'm missing , probably something simple.

Also, in response to your question.. I am trying to take the first and last records from all files in the folder, not just selected ones. I know the extra bit of code to do this is fairly simple as I've seen it elsewhere, but my problem would be where to add it your code above. Ideally I would be trying to figure this out myself, but it would be helpful if you could give me a few pointers to see how its done and I can dissect this.

Regards

[CreganTur]

although the code runs from the module I can't figure out where the output goes, or how to see it.
I had the output write to the immediate window, but I rewrote it so that the output is populated to 2 variables.

Sub OpenTextFile()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim strFirstLine As String
Dim strLastLine As String
Set conn = New ADODB.Connection
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & "C:\" '<<<Put filepath to folder containing txt file here
Set rst = New ADODB.Recordset
'connect via SQL SELECT all statement- pulls everything in txt file
'place name of txt file you want to work with inside brackets
rst.Open "SELECT * FROM [FirstLastTest.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
rst.MoveFirst
For Each fld In rst.Fields
strFirstLine = fld.Value '<<<Assign first line value to variable
Next fld
rst.MoveLast
For Each fld In rst.Fields
strLastLine = fld.Value '<<<Assign last line value to variable
Next fld
'Close and release connections
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub


Ideally I would be trying to figure this out myself, but it would be helpful if you could give me a few pointers to see how its done and I can dissect this.
mdmackillop wrote an excellent kb article that deals with looping through all files in a folder- it's located here:http://www.vbaexpress.com/kb/getarticle.php?kb_id=245
He has a section clearly marked where you would insert a modified version of the code I wrote so that it will work as MD's program loops through all the files.

Good luck :thumb

[knifelong]

Thats great thanks.

Ha ! when I opened the immediate window there were 20 output records from all my attempts to run the procedure and wondering where the output was going. Still working on modifications.

regards


nb. this thread can be closed, not sure how though

[CreganTur]

this thread can be closed, not sure how though

Just go to Thread Tools at the top of the thread. Select 'Mark As Solved' and submit your choice- this will mark it as solved on the thread list for this forum.

Really glad I could help :thumb

[knifelong]

Hello.

I posted this to CreganTur who was kind enough to help last time. I haven't heard anything so maybe he's on holiday and someone else can help.




I am still trying to get your procedure to loop through all the files in the folder pulling all the first and and last from the txt files there and placing one after another in the immediate window, or elseware.
I did try to get it to work using mdmackillop's article but I couldn't quite figure it out. It's going to take a bit longer I think as I'm still too new to VBA. I tried a number of things which didn't quite work, I won't go through them but at least it's a learning process. I wonder if you knew how to do this.

On the plus side I did figure out how to batch change all the file extensions to .txt first which was necessary for your procedure.

The other thing was that I'm not sure what happens to the output when you changed the procedure slightly. "but I rewrote it so that the output is populated to 2 variables"


regards

[CreganTur]

so maybe he's on holiday

Yeah, I was off for my Birthday (playing Rock Band!):band:


I am still trying to get your procedure to loop through all the files in the folder pulling all the first and and last from the txt files there and placing one after another in the immediate window, or elseware.

Can you post your code? (be sure to wrap with VBA tags, please)


The other thing was that I'm not sure what happens to the output when you changed the procedure slightly.

I assign the values to the variables strFirstLine and strLastLine; I commented it in the code. You can then use those variables for whatever you want to do with this data.

[knifelong]

Hi! happy Birthday


"He has a section clearly marked where you would insert a modified version of the code I wrote so that it will work as MD's program loops through all the files."


I'm just using mdmackillop's looping procedure as you advised above which prints the file path and name to the immediate window. As you said he has an insertion point where you can insert your procedure for getting first and last files. However I have been unable to adapt your procedure to work with mdmackillop's. I know it must involve changing the part where you enter file path and file name and other tinkering but ....

probably I have not understood properly but cheers for having a look.




Sub OneType()
Const MyPath = "C:\test" ' Set the path.
Const FileType = "*.*" ' or "*.doc"
ProcessFiles MyPath, FileType
End Sub


Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer

'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop

'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'Do things with files here*****************
Debug.Print strFolder & "\" & strFileName


Sub OpenTextFile()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set conn = New ADODB.Connection
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & "C:\" '<<<Put filepath to folder containing txt file here
Set rst = New ADODB.Recordset
'connect via SQL SELECT all statement- pulls everything in txt file
'place name of txt file you want to work with inside brackets
rst.Open "SELECT * FROM [FirstLastTest.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
rst.MoveFirst
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
rst.MoveLast
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
'Close and release connections
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub



'*******************************************
strFileName = Dir$()
Loop

'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub

[CreganTur]

I rewrote it to fix a few big problems. I tested it, it should work for you:

Sub OneType()
Const MyPath = "C:\test" '<<<Path to folder you want to search
Const FileType = "*.txt" '<<<You only want to look at .txt files
ProcessFiles MyPath, FileType '<<<Call ProcessFiles Subroutine
End Sub

Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop

'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'open new connection to new .txt file
Set conn = New ADODB.Connection
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & strFolder '<<<Put filepath to folder containing txt file here
Set rst = New ADODB.Recordset '<<<Create new recordset to query
'connect via SQL SELECT all statement- pulls everything in txt file
'place name of txt file you want to work with inside brackets
rst.Open "SELECT * FROM [" & strFileName & "]", conn, adOpenStatic, _
adLockReadOnly, adCmdText
rst.MoveFirst '<<<Move to first record
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
rst.MoveLast '<<<Move to last record
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
'Close and release connections
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
strFileName = Dir$()
Loop

'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub


Now, as you're aware this only puts the data into the Immediate Window, which is not a good place to put data for any real process- I only did that in the example so you could see that the function works. It's not something you would want to do in a real work environment, as it only temporarily stores the data.