how do I import .txt file as rows not columns?

[Guest]

I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

(e-mail address removed); (e-mail address removed); (e-mail address removed) ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ (e-mail address removed)/ (e-mail address removed) /[email protected]/

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ (e-mail address removed) /
2/ (e-mail address removed) /
3/ (e-mail address removed) /

Thanks in anticipation,

Phil

 

[Pete_UK]

Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

 

[Steve Yandl]

Phil,

Try this subroutine. Just edit the line that defines the path to your
actual text file.

_______________________________________

Sub GetEmailList()
Const ForReading = 1

strTxtFilePath = "C:\Test\AddyList.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile(strTxtFilePath, ForReading)

strText = objFile.ReadAll
objFile.Close

strText = Replace(strText, vbCrLf, "")
arrText = Split(strText, ";")

For R = 0 To UBound(arrText)
Cells(R + 1, 1).Value = Trim(arrText(R))
Next R

Set FSO = Nothing
End Sub
_______________________________________

Steve

 

[Guest]

That sounds like it will do the trick Pete, but how do I enter 'manual line
break' into the Find & Replace dialogue box? If I just press Enter, teh
cursor skips out of the box, leaving it blank. Is there some sort of machine
code for Manual Line Break?

Cheers,

Phil

 

[Guest]

Thanks Steve,

Sorry to sound dim, but what DO I actually do with this program?

Do I paste it somewhere and click run, or do I need to go into DOS and type
it in, or what?

Thanks,

Phil

 

[Pete_UK]

In the Replace panel in Word click on "More", then click on "Special"
and then choose "Manual Line Break" from the list - you will get a
funny symbol in the Replace With box, looking like ^|. In the Find
What box you want to put your semicolon.

Click Replace All, then save the file back as a text file for use in
Excel.

Hope this helps.

Pete

 

[Guest]

That's it Pete, problem solved!

Thanks very much.

Phil

 

[Pete_UK]

You're welcome, Phil - thanks for feeding back.

Pete

 

[Steve Yandl]

Phil,

Copy what is between the lines so it's ready to paste.

Open a new Excel workbook.

Hold down 'Alt' and press the 'F11' key.

In the upper left of the Visual Basic Editor window, you will find a smaller
project window with a tree type representation of workbook and sheets. The
top level will probably read "VBAProject (Book1)". Right click the
'VBAProject (Book1)' and choose 'Insert > Module'. When you do so, a new
folder appears named 'Modules' along with 'Module 1' which you are now in.
The large empty window to the right is the code window. Paste what I
provided into that code window.

Edit the line that reads:
strTxtFilePath = "C:\Test\AddyList.txt"
so that it points to the actual text file where the semi-colon separated
email addresses reside or you can create a folder "C:\Test' on your system,
move a copy of your text file into that folder and rename it AddyList.txt if
you prefer.

Close the Microsoft Visual Basic window and you will be back in your empty
workbook. Now, press 'Alt' plus 'F8' and you will find your macros in a
list. If you have more macros than the one we just pasted, make sure that
"GetMailList()" is the one selected and then click the 'Run' button. Now,
just sit back and let the subroutine deliver.

Steve