Import Large .txt File; List in Rows

[ryguy7272]

Excuse me, does anyone have a macro lying around that will import a large
..txt file into Excel 2002? I have more than 255 email addresses in this
file, all separated by “;†and I would like to list these in rows.

I thought I had some code lying around that did just this, but can’t seem it
find it anywhere. Please help.

Thanks,
Ryan--

 

[Chip Pearson]

I have code at http://www.cpearson.com/excel/ImpText.aspx for
importing and exporting text to and from Excel. The code on that page
works as long as you don't exceed the number of rows in Excel (64K, or
1M in 2007). If you need more rows, see
http://www.cpearson.com/excel/ImportBigFiles.aspx . This will import a
text file of any size with any number of records, spreading them
across as many worksheets as required.

I wouldn't consider 255 records a "big" text file at all.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jacob Skaria]

Hi Ryan

Try out the below macro...I assume the text file contains one single line of
email addresses seperated by ;

Sub Macro()
Dim intFile As Integer
intFile = FreeFile
Open "c:\21.txt" For Input As #intFile
Line Input #intFile, strData
arrdata = Split(strData, ";")
Range("A1").Resize(UBound(arrdata) + 1) = _
WorksheetFunction.Transpose(arrdata)
Close #intFile
End Sub

If this post helps click Yes

 

[Dave Peterson]

If I had to do this, I would open the .txt file in a new text editor (I'd use
UltraEdit) and change those semicolons to carriage return, line feeds.

Save to a new name (just in case!) and open that new file in excel.

If you don't have a text editor that has this kind of feature, you can use VBA
to do the conversion:

Option Explicit
Sub UpDateTxtFile()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

myInFileName = "C:\test.txt"
myOutFileName = "C:\testout.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = ";"
myContents = .Replace(myContents, vbCrLf)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

 

[ryguy7272]

Problem solved. Thanks guys!
Ryan---

 

[ryguy7272]

Nice one!!!
Thanks,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

If I had to do this, I would open the .txt file in a new text editor (I'd use
UltraEdit) and change those semicolons to carriage return, line feeds.

Save to a new name (just in case!) and open that new file in excel.

If you don't have a text editor that has this kind of feature, you can use VBA
to do the conversion:

Option Explicit
Sub UpDateTxtFile()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

myInFileName = "C:\test.txt"
myOutFileName = "C:\testout.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = ";"
myContents = .Replace(myContents, vbCrLf)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub