Lookup from text file - possible?

[VoG]

The background to this is that I want to convert UK post code data into Grid References (Eastings and Northings = X and Y co-ordinates). I have a huge (54 MB) text file that I think was exported from a GIS application that has, in tab-delimited format

Post Code ¦ Easting ¦ Northing ¦ Error

I want to be able to enter a post code and return the Easting and Northing. (I'm not interested in the Error column but I can live with or without it.)

I know how to do a VLOOKUP with an Excel file. However I have a few million rows that defeat even Excel 2007. I have tried importing this into Excel using Chip Pearson's code http://www.cpearson.com/excel/ImportBigFiles.aspx but this takes forever (hours) and eventually errors out.

Even if I could get that to work I would be faced with trying a VLOOKUP based upon several sheets which I don't think is possible.

So, dear reader - and thank you for your patience - is there any way that I can from Excel do a VLOOKUP or similar using the text file to search from. My gut feeling is that this is not possible but if it is then here is the place to ask...

I apologise in advance if I don't respond in a timely manner - I have to entertain Mrs VoG (to dinner!) as it is a special day.

Many thanks.

 

[stanleydgromjr]

VoG II,

For the later part of your post (once you have all the data in your workbook, in several worksheets):

Function VLOOKAllSheets:
http://www.ozgrid.com/VBA/VlookupAllSheets.htm


Have a great day,
Stan

 

[eeek18]

Import it into Access?

Then export as an Excel Workbook/sheet?

I had a project back in 2002 that was large and I was advised to do this back then, and it worked for me, but I cannot remember if it was as big as yours though.

 

[VoG]

Stan

This looks really useful - thanks. I'll have a go at writing something to import the data tomorrow and post back with how it goes.

eeek18 - I am an Access moron!

 

[John_w]

You can use ActiveX Database Objects (ADO) - see http://msdn2.microsoft.com/en-us/library/ms974559.aspx

For example here's some VBA code I wrote to retrieve data from a CSV text file containing event log records:

Option Explicit

'Must set reference to Microsoft ActiveX Data Objects Library (Tools - References)

Sub ADOReadCSVFile()
    Dim strPathtoTextFile As String, total As Single, iCols As Integer
    Dim objConnection As ADODB.Connection
    Dim objRecordset As ADODB.Recordset
    Dim ws As Worksheet
    Const CSV_FILE As String = "csv.txt"
    
    On Error Resume Next
    
    Set ws = Worksheets(1)
    
    Set objConnection = New ADODB.Connection
    Set objRecordset = New ADODB.Recordset
    
    strPathtoTextFile = ThisWorkbook.Path & "\"
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & strPathtoTextFile & ";" & _
              "Extended Properties=""text;HDR=No;FMT=CSVDelimited"""
    
    objRecordset.Open "SELECT * FROM " & CSV_FILE & " WHERE Date = '14/02/2008'", _
                objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    If Not objRecordset.EOF Then
        'add column headers to sheet
        For iCols = 0 To objRecordset.Fields.Count - 1
            ws.Cells(1, iCols + 1).Value = objRecordset.Fields(iCols).Name
        Next
        ws.Range(ws.Cells(1, 1), ws.Cells(1, objRecordset.Fields.Count)).Font.Bold = True

        ' Copy the recordset to the worksheet, starting in cell A2
        Worksheets(1).Cells(2, 1).CopyFromRecordset objRecordset
    
    End If
    
    ' Close ADO objects
    objRecordset.Close
    objConnection.Close
    
    Set objRecordset = Nothing
    Set objConnection = Nothing
End Sub

The csv.txt file contains comma delimited records saved from the Windows Application Event Log. This file is 23 MB in size. The code above requires a file called schema.ini containing the column names and data types corresponding to the columns in the csv.txt file.

My schema.ini contains:

[csv.txt]
ColNameHeader=False
Format=CSVDelimited
Col1=Date Text
Col2=Time Text
col3=Source Text
Col4=Type Text
col5=Category Text
Col6=Event Text
Col7=User Text
col8=Computer Text
Col9=Description Text

 

[eeek18]

Rings bells now - that's right I had to create that schema.ini thing as well...

But I do remember it was a whiz when it worked [note to self: write stuff down]

 

[VoG]

An update...

I ran this

Sub ImportPostCodes()
Dim iFreeFile As Integer, i As Long, path As String, InputLine
Dim ws As Worksheet, LineArray, t As Date
t = Now
Set ws = ActiveSheet
Reset
iFreeFile = FreeFile
path = ThisWorkbook.path
Open path & "\" & "PostCodeData.txt" For Input As #iFreeFile
Application.ScreenUpdating = False
i = 1
Do While Not EOF(iFreeFile)
    Line Input #iFreeFile, InputLine
    LineArray = Split(InputLine, vbTab)
    ws.Range(Cells(i, 1), Cells(i, 4)).Value = LineArray
    i = i + 1
    If i > 65536 Then
        ThisWorkbook.Save
        Sheets.Add after:=ws
        Set ws = ActiveSheet
        i = 1
    End If
Loop
ThisWorkbook.Save
Close #iFreeFile
Application.ScreenUpdating = True
MsgBox "Time elapsed" & vbTab & Format(Now - t, "hh:mm:ss")
End Sub

which never actually completed - it bombed out after about 2 hours with an 'out of memory' error once it had got to the 16th sheet and processed 1,047,520 records. I couldn't even get into the VBE ("Out of Memory") - even after saving the file and closing Excel and even restarting Windows.

I manually copied about 10 sheets into a new workbook and saved it. Then after restarting Excel I no longer had the error. I then ran this to grab the remaining records

Sub ImportPostCodes2()
Dim iFreeFile As Integer, i As Long, path As String, InputLine
Dim ws As Worksheet, LineArray, t As Date
t = Now
Set ws = ActiveSheet
Reset
iFreeFile = FreeFile
path = ThisWorkbook.path
Open path & "\" & "PostCodeData.txt" For Input As #iFreeFile
Application.ScreenUpdating = False
For i = 1 To 1047520
    Line Input #iFreeFile, InputLine
Next i
i = 64481
Do While Not EOF(iFreeFile)
    Line Input #iFreeFile, InputLine
    LineArray = Split(InputLine, vbTab)
    ws.Range(Cells(i, 1), Cells(i, 4)).Value = LineArray
    i = i + 1
    If i > 65536 Then
        ThisWorkbook.Save
        Sheets.Add after:=ws
        Set ws = ActiveSheet
        i = 1
    End If
Loop
ThisWorkbook.Save
Close #iFreeFile
Application.ScreenUpdating = True
MsgBox "Time elapsed" & vbTab & Format(Now - t, "hh:mm:ss")
End Sub

This completed, having imported a further 514,797 records, in a matter of minutes

So I now have 1,562,317 records (currently in two workbooks) ready to try the VLOOKUP. Combining the workbooks and trying the VLOOKUP will have to wait until Monday...

 

[VoG]

I got the VLOOKAllSheets function to work by modifying it to exclude the sheet with the list of post codes to be looked at. I had to do separate lookups with both of my files (see above) since trying to open both in order to consolidate them gave 'out of memory errors'.

Fortunately this was a one-off exercise - I hope

Thanks for all input.