Windows 7 - 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

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.




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






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





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

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





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




Delete all of the hash marks (#):

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




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



Save the txt file and close Word.

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




Select your text file.

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





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.





Done.

Save it.

Regards,
GEWB

Great post GEWB! this should be a "sticky".