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

Several flavors of Linux (Mint primary) / 32bit XP, Win7 HomeP / 64bit Win7 Pro, Ultimate / 64Win8.1
 
 

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 Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
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
Change text color in a specific program?
I recently installed the U-7imate theme for Windows 7. I like it a lot, but one annoying thing is that it changes the text color in right click menus to white. As you can see, this works nicely with Windows Explorer and such, but Solidworks does not like it so much. I can't read anything in the...
Customization
Can I create a pie chart with data in an existing excel file?
Where I work an excel sheet with any and all "failed activities" (we're not getting paid) is sent out every wednesday. How can I highlight specific cells (for a specific person) and have that chart show how much of the failed activities belong to them. They obviously know that, but this will...
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
Show text from *specific* programs on taskbar?
Is there a way to make only certain programs show their text in the Windows 7 taskbar? Under normal circumstances, I still want to use icon view, but some programs use their title bars to show information that I would like to see. Is there a registry tweak or anything to do this for those specific...
Customization


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 14:10.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App