|
How can I import a text file into excel with each field as a sepe.
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!) |
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 " m wrote in message ... |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!) |
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- " m wrote in message ... 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!) |
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: 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: 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: 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: 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: 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? "Jim Rech" wrote: 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 " m wrote in message ... |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!) |
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: 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: 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: 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: 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: 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? "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- " m wrote in message ... 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!) |
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- " m wrote in message ... 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: 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: 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: 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: 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: 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? "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- " m wrote in message ... 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!) |
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! "Dave Peterson" wrote: 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 wrote: 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: 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: 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: 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: 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: 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? "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- " m wrote in message ... 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!) -- Dave Peterson |
|
| All times are GMT +1. The time now is 08:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com