importing list

S

sloth

I have a text file list of over 500 thousand one-field records. What
the best way to import this list into Excel? I'm running up against a
constraint on the # of rows allowed. Ideally, I'd like the records
fill-in to the next column should the row limit in the current column
be reached. My intention is to try to match values from one sheet to
this database of records.
 
D

Dave O

I think the only way to do it is with code: try this on a blank
sprdsht. You'll need to provide the full directory path and filename
on the "Open" line.

Sub Import_BD_Textfile()
Dim Lyne As String 'variable to hold entry from file
Dim K As Long 'line counter

Application.Calculation = xlCalculationManual

Range("a1").Select 'start in cell A1

Open "c:\full path\filename.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, Lyne 'read a line from the source file into memory
Lyne = Trim(Lyne) 'remove any leading or trailing blanks
ActiveCell.Offset(K, 0).Value = Lyne 'write the entry to the file
K = K + 1
If K = 65500 Then 'start a new column when row 65500 is filled
K = 0
ActiveCell.Offset(0, 1).Select
End If
Loop

Close #1
Application.Calculation = xlCalculationAutomatic
End Sub
 
S

sloth

Thanks for the code. I'm not familiar with VBA. Could you describe
how to implement your code, i.e. where to put it and how to run it?
 
S

sloth

Please disregard the previous email. I figured out how to run the
code. Thanks it worked perfectly.
 
Top