How can I import a text file into excel with each field as a sepe.

G

gordon

I have a raw data file and a finished excel file which I did manually, can
anyone show me how I can import the data into excel in a few simple steps?
Any help will be greatly appreciated!! Thanks! (I can send the files if
anyone would like to help!)
 
J

Jim Rech

The simplest way is to open the text file in Excel using the Text Import
Wizard to parse the data properly. Then Copy and Paste the data to the
finished workbook.

--
Jim Rech
Excel MVP
"[email protected]"
|I have a raw data file and a finished excel file which I did manually, can
| anyone show me how I can import the data into excel in a few simple steps?
| Any help will be greatly appreciated!! Thanks! (I can send the files if
| anyone would like to help!)
 
E

Earl Kiosterud

Gordon,

You may be able to open it directly, using the Text Import Wizard, as Jim
suggested. Change the file extension to .txt for that. If you want more
help, it would be useful to open it in NotePad and paste the first few lines
into your post.
 
G

gordon

Thanks very much for your help, I"ve tried but the best I can do is seperated
my data into different rows but my ultimate goal is seperated by column,
below is a small chunk of my raw data:


4. Assta Label House Pty Ltd
Address: 16-18 Norman St, Peakhurst NSW 2210, Australia
Phone: (61) 2-95333644
Fax: (61) 2-95333755
E-mail: [email protected]
Website: www.assta.com.au
Contact: Mr Graham Staas, Managing Director

5. Australian Packaging Pty Limited
Address: PO Box 132, Caringbah NSW 1435, Australia
Phone: (61) 2-95402800
Fax: (61) 2-95401607
E-mail: [email protected]
Website: www.austpack.com.au
Contact: Mr Cranfield, Managing Director

6. Reader's Digest Services P/L
Address: 26-32 Waterloo Street, Surry Hills NSW 2010, Australia
Phone: (61) 2-96906111
Fax: (61) 2-96998165
Website: www.readersdigest.com.au
Contact: Mr Paul Heath, Managing Director

7. Southlands Medical Services
Address: Isalacia Mews Pty Ltd, 80 Pine Trees Gully Road, Willetton WA
6155, Australia
Phone: (61) 8-93328888
Fax: (61) 8-93105388
E-mail: [email protected]
Contact: Dr Andrew E. Ong, Partner

8. Hawker Brownlow Education P/L
Address: 1123A Nepean Highway, Highett VIC 3190, Australia
Phone: (61) 3-95551344
Fax: (61) 3-95534538
E-mail: [email protected]
Website: www.hbe.com.au
Contact: Mr David Brownlow, Managing Director

9. Microprint Asia Pacific
Address: 31 Sullivan St, Moorabbin VIC 3189, Australia
Phone: (61) 3-95557760
Fax: (61) 3-95555773
E-mail: [email protected]
Website: www.microprint.com.au
Contact: Mr Christopher Edwards, Manager

10. Ranguardpress Pty Ltd
Address: PO Box 50, Northbridge WA 6865, Australia
Phone: (61) 8-93281388
Fax: (61) 8-93287307
Website: www.ranguardpress.com.au
Contact: Mr Ted van Heemst, Managing Director


here's what I need:

Company address phone fax etc......
xxxxx xxxxxxxx xxxxxx xxxxxx


Is there a way of doing this?
 
G

gordon

Thanks very much for your help, I"ve tried but the best I can do is seperated
my data into different rows but my ultimate goal is seperated by column,
below is a small chunk of my raw data:


4. Assta Label House Pty Ltd
Address: 16-18 Norman St, Peakhurst NSW 2210, Australia
Phone: (61) 2-95333644
Fax: (61) 2-95333755
E-mail: [email protected]
Website: www.assta.com.au
Contact: Mr Graham Staas, Managing Director

5. Australian Packaging Pty Limited
Address: PO Box 132, Caringbah NSW 1435, Australia
Phone: (61) 2-95402800
Fax: (61) 2-95401607
E-mail: [email protected]
Website: www.austpack.com.au
Contact: Mr Cranfield, Managing Director

6. Reader's Digest Services P/L
Address: 26-32 Waterloo Street, Surry Hills NSW 2010, Australia
Phone: (61) 2-96906111
Fax: (61) 2-96998165
Website: www.readersdigest.com.au
Contact: Mr Paul Heath, Managing Director

7. Southlands Medical Services
Address: Isalacia Mews Pty Ltd, 80 Pine Trees Gully Road, Willetton WA
6155, Australia
Phone: (61) 8-93328888
Fax: (61) 8-93105388
E-mail: [email protected]
Contact: Dr Andrew E. Ong, Partner

8. Hawker Brownlow Education P/L
Address: 1123A Nepean Highway, Highett VIC 3190, Australia
Phone: (61) 3-95551344
Fax: (61) 3-95534538
E-mail: [email protected]
Website: www.hbe.com.au
Contact: Mr David Brownlow, Managing Director

9. Microprint Asia Pacific
Address: 31 Sullivan St, Moorabbin VIC 3189, Australia
Phone: (61) 3-95557760
Fax: (61) 3-95555773
E-mail: [email protected]
Website: www.microprint.com.au
Contact: Mr Christopher Edwards, Manager

10. Ranguardpress Pty Ltd
Address: PO Box 50, Northbridge WA 6865, Australia
Phone: (61) 8-93281388
Fax: (61) 8-93287307
Website: www.ranguardpress.com.au
Contact: Mr Ted van Heemst, Managing Director


here's what I need:

Company address phone fax etc......
xxxxx xxxxxxxx xxxxxx xxxxxx


Is there a way of doing this?
 
E

Earl Kiosterud

Gordon,

Oh, my. Your data isn't in table form at all. Excel won't rearrange the
data. And you do need it organized by columns, as you indicated, for most
Excel (and other programs too) to be able to work with it.

There have been solutions provided here and there for this kind of
situation; perhaps someone will come forward with one already prepared that
might work for your data. One problem with your data is that not all of the
"records" have all the fields (some don't have Email, for example). So the
fields can't just be distributed across the sheet as they occur in your
document. Can you put a macro in place and run it, if someone does have a
solution?

If not, a macro could be written for your layout It could use the empty
line to know when a new record starts. It could use the headings (e.g.:
Address:) to ensure that data is put in the proper column. It wouldn't be a
huge deal, but not trivial either.
 
D

Dave Peterson

How about a little macro:

Option Explicit
Option Base 1
Option Compare Text
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim iCtr As Long
Dim myRng As Range
Dim myCell As Range
Dim dotPos As Long
Dim spacePos As Long
Dim FoundAMatch As Boolean
Dim myStr As String

Dim myKeys As Variant

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

myKeys = Array("Address:", "Phone:", "FAX:", _
"E-Mail:", "Website:", "Contact:")

With newWks
.Range("a1").Resize(1, 7).Value _
= Array("Company", "Address", "Phone", _
"FAX", "eMail", "Website", "Contact")
End With

With curWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

oRow = 1
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'do nothing
Else
FoundAMatch = False
'look for Company name
'#####.(space)
spacePos = InStr(1, myCell.Value, ".")
If IsNumeric(Left(myCell.Value, spacePos)) Then
'I think we have it!
oRow = oRow + 1
newWks.Cells(oRow, "A").Value _
= Trim(Mid(myCell.Value, dotPos + 1))
FoundAMatch = True
Else
For iCtr = LBound(myKeys) To UBound(myKeys)
If Left(myCell.Value, Len(myKeys(iCtr))) = myKeys(iCtr) Then
'found the key
myStr = Trim(Mid(myCell.Value, Len(myKeys(iCtr)) + 1))
If myKeys(iCtr) = "e-mail:" Then
myStr = "=Hyperlink(""mailto:" & myStr & """)"
End If
newWks.Cells(oRow, "A").Offset(0, iCtr).Formula = myStr
FoundAMatch = True
Exit For 'stop looking
End If
Next iCtr
End If

If FoundAMatch = False Then
MsgBox "No Match for row #: " & myCell.Row & vbLf & _
"value: " & myCell.Value
End If
End If
Next myCell

newWks.UsedRange.Columns.AutoFit

End Sub

This macro looks for 6 keys:
myKeys = Array("Address:", "Phone:", "FAX:", _
"E-Mail:", "Website:", "Contact:")

And for the company name, it assumes that there's always a number followed by a
dot.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
G

gordon

Dave,

Thank you very much for your help. Your macro is amazing and it did exactly
what I wanted. It saves me a lot of time when I have about 30,000 records to
do. Again, thanks!
 
Top