Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Please Help - Data in Text File to be arranged in Excel in a specific

17 Dec 2011   #1
sids911

Windows 7 ultimate x64
 
 
Please Help - Data in Text File to be arranged in Excel in a specific

Hello Everyone, Its my first post here

I have a text file with values printed like these;

BSSID: 001E4ABXXXX
Last seen: 14/12/2011 - 16:06:12
Vendor: Cisco Systems
Signal: -91 dBm
SSID: NXXXX
Enc: OPEN
Mode: Infrastructure
Channel: 1 (2412000 Hz)
Rates (Mbps): 11, 12, 18, 24, 36, 48, 54,
Packets: 25
Unique WEP IVs: 0

BSSID: C03F0E3XXXXX
Last seen: 14/12/2011 - 15:57:27
Vendor: NETGEAR
Signal: -96 dBm
SSID: Misuss
Enc: WEP
Mode: Infrastructure
Channel: 1 (2412000 Hz)
Rates (Mbps): 6, 9, 12, 48, 18, 24, 36, 54,
Packets: 7
Unique WEP IVs: 0

BSSID: 001E4ABXXXXX
Last seen: 14/12/2011 - 15:56:49
Vendor: Cisco Systems
Signal: -95 dBm
SSID: Mobile
Enc: WPA2-CCMP-MGT
Mode: Infrastructure
Channel: 1 (2412000 Hz)
Rates (Mbps): 11, 12, 18, 24, 36, 48, 54,
Packets: 2
Unique WEP IVs: 0



I want to export this all to an excel spreadsheet with columns representing BSSID, Last Seen, vendor, Signal, SSID, Enc, Mode etc., and all the values belonging for an BSSID in a single row. Does it look doable? If yes, please let me know how to achieve this, and I will be highly obliged.

Much thanks!

Sid


My System SpecsSystem Spec
.
19 Dec 2011   #2
GEWB

Linux (Mint is primary) / XP, Win7 Home / Win7 Pro, Ultimate / Win8.1 / Win10 archived VM
 
 

First, two questions: how proficient are you with Word and Excel? Are you comfortable using the REPLACE function in both?

If reasonably proficient and comfortable (understand) with the REPLACE function, it isn't difficult and will take about 15 minutes to accomplish what you seek.

Open the text file in Word.

Turn on the SHOW ALL switch (the paragraph mark icon).

Your post example has MANUAL LINE BREAKS after each line - if the source file has them, replace all of them with PARAGRAPH MARK.

Please Help - Data in Text File to be arranged in Excel in a specific-source-1.gif


Add a PARAGRAPH MARK (Enter) at the very beginning of the document.

Please Help - Data in Text File to be arranged in Excel in a specific-source-2.gif
Please Help - Data in Text File to be arranged in Excel in a specific-source-3.gif
Please Help - Data in Text File to be arranged in Excel in a specific-source-4.gif


Replace DOUBLE PARAGRAPH MARKS with PARAGRAPH MARK, hash mark (#) - makes it easier to see what you are doing next - and PARAGRAPH MARK like this:

Find what: ^p^p
Replace with: ^p#^p

Please Help - Data in Text File to be arranged in Excel in a specific-source-5.gif
Please Help - Data in Text File to be arranged in Excel in a specific-source-6.gif


Now add the tilde (~) in front of each line:

Find what: ^pBSSID:
Replace with: ^p~BSSID:

Please Help - Data in Text File to be arranged in Excel in a specific-source-7.gif
Please Help - Data in Text File to be arranged in Excel in a specific-source-8.gif


Repeat for each line of a record (total of 11 times).

Please Help - Data in Text File to be arranged in Excel in a specific-source-9.gif


Delete all of the hash marks (#):

Find what: ^p#^p
Replace with: ^p

Please Help - Data in Text File to be arranged in Excel in a specific-source-10.gif


Now your records are:
> Separated by a carriage return (paragraph mark)
> Fields are separated by the tilde (~)
> The first imported column will be EMPTY

Please Help - Data in Text File to be arranged in Excel in a specific-word_after_replace.gif

Save the txt file and close Word.

Open Excel.
Open file type: All Files (*.*)

Please Help - Data in Text File to be arranged in Excel in a specific-excel-open.gif


Select your text file.

In the import steps:
Step 1: Delimited
Step 2: deselect Tab and select Other, set to ~

Please Help - Data in Text File to be arranged in Excel in a specific-excel-1.gif
Please Help - Data in Text File to be arranged in Excel in a specific-excel-2.gif


Click FINISH.

Insert a header row and label each column.

Select each colum one at a time and delete the corresponding record field leaders. Be sure to include the SPACE that follows the colon as part of the FIND WHAT.

Please Help - Data in Text File to be arranged in Excel in a specific-excel-3.gif
Please Help - Data in Text File to be arranged in Excel in a specific-excel-4.gif


Done.

Save it.

Regards,
GEWB


My System SpecsSystem Spec
19 Dec 2011   #3
Anak

Microsoft Community Contributor Award Recipient

Win 7 Home Premium 64bit Ver 6.1.7600 Build 7601 - SP1
 
 

Great post GEWB! this should be a "sticky".
My System SpecsSystem Spec
.

Reply

 Please Help - Data in Text File to be arranged in Excel in a specific




Thread Tools




Similar help and support threads
Thread Forum
Microsoft Excel and Windows re-arranged links to Roaming App Data
I have made this spreadsheet that is supposed to work on more than one Windows 7 -10 computers. The spreadsheet uses photos and they are embedded into the cells and they have a hyperlink to their location They live C:\MT FILES See that way I can copy the spreadsheet and the MT FILES directory to...
Microsoft Office
Can't pin a shortcut to a specific Excel file to the Taskbar
For several years I've had a shortcut to a specific Excel file pinned to the Windows 7 Taskbar. When I clicked it, it caused Excel to open the file as desired. Somehow the shortcut disappeared from the Taskbar, and I'm now trying to recreate it. I can create a shortcut on the Desktop with the...
Microsoft Office
Excel 2013 cannot open simple text file with .acc extension
Inside .acc file is a simple text, but the mighty Excel tries to open it like some kind of database. It is asking for accounts, sources, passwords etc. and finally it cannot open it at all. It can open this file if I change extension to something else like .txt or .xls. How can I turn off this...
Microsoft Office
Excel Sheets Disapeared on one specific file.
Hello, i have Office 2010 installed in my company. a user edited an Excel workbook and somehow got the sheets to disappear. if i open a new workbook, they appear fine. i know the trick about maximizing, but it's not the case. i also tried the Home>format>visibility>hide/unhide i managed to...
Microsoft Office
PDF File data into Excel Spreadsheet ?
I have WIN 7 with Excel 2003. Is it possible to transfer data contained in a .pdf file onto an Ecel spreadsheet ? If it is possible what is the procedure ?
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 17:54.
Twitter Facebook Google+