Excel can handle this kind of data ?

Hi Expert, I have a lot group of data. The following is just four group of data out of a lot date group in text file. Each group of these data is separated with one line space with up and down group( please see the following). I would like to input these four group data as four units, respectively into Excel(here just is example,).  I doubt Excel is able to handle it. Even so, it is complicated since that needs to edit some program. My purpose is to select some of group of data and pick it out based on character in the unit( group of data) from a great amount of group of data. Here my question is which is easier way to do that? In another word, Excel can do that ? or Any other program ? Thank you
 


interface GigabitEthernet2/14
 description Conn
 switchport
 switchport access vlan 101
 switchport mode access
 spanning-tree portfast

interface GigabitEthernet2/17
 description Conn
 switchport
 switchport access vlan 101
 switchport mode access
 spanning-tree portfast

interface GigabitEthernet3/22
 description Conne
 switchport
 switchport access vlan 100
 switchport mode access
 spanning-tree portfast

interface GigabitEthernet4/23
 description CO
 switchport
 switchport access vlan 100
 switchport mode access
 spanning-tree portfast
eemoonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This can only be achieved programmatically. Scripting languages are fine for that. I would prefer PowerShell, but VBS or VBA is suited the same for this task. Or any other text parsing enabled language.
Nevertheless, it needs some effort to do that properly.
0
Martin LissOlder than dirtCommented:
I assume you have a text file.

On the Data tab in the Get External data section, choose 'From Text'. Select the file, choose the 'Fixed Width' option and click 'Finish'. It will place the data by default starting in A1 but you can change that.
0
Martin LissOlder than dirtCommented:
Here's a macro I recorded while I did that.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Temp\test..txt", _
        Destination:=Range("$A$1"))
        .Name = "test._1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

eemoonAuthor Commented:
Hi Thank you so much for your fast reply. It looks like Excel can handle it after I tried it just now. The problem is how to separate the four group data as four basic unit with mark " ! " or its one line blank space between group, or indented since second line in one group. The four group of data could be like this :

interface GigabitEthernet2/14
 description Conn
 switchport
 switchport access vlan 101
 switchport mode access
 spanning-tree portfast
!
interface GigabitEthernet2/17
 description Conn
 switchport
 switchport access vlan 101
 switchport mode access
 spanning-tree portfast
!
interface GigabitEthernet3/22
 description Conne
 switchport
 switchport access vlan 100
 switchport mode access
 spanning-tree portfast
!
interface GigabitEthernet4/23
 description CO
 switchport
 switchport access vlan 100
 switchport mode access
 spanning-tree portfast
0
Martin LissOlder than dirtCommented:
Add this to the end of the macro.

    Dim lngLastRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A1048576").End(xlUp).Row
    For lngRow = 1 To lngLastRow
        If Cells(lngRow, "A") = "" Then
            Cells(lngRow, "A").Value = "!"
        End If
    Next

Open in new window

0
Saqib Husain, SyedEngineerCommented:
You can even do this without a macro

Select the data range
press F5
Click on special
Select blanks
Click OK
type   !
press ctrl-enter
0
eemoonAuthor Commented:
Hi Thank you Saqib. I think this is a good way for the issue because I do not know how to use macro.
After I went through the steps that you mentioned, the process only added " ! "

What I want is, for example, to pick out the unit which has "100" among the four units and put them together. You can see that there are 2 units which have "100". so the third and fourth one are picked out.
0
Saqib Husain, SyedEngineerCommented:
What do you mean by pickout?

Can you upload an example file showing what it is before and what it is after the pickout?
0
EirmanChief Operations ManagerCommented:
My purpose is to select some of group of data and pick it out based on character in the unit( group of data) from a great amount of group of data. Here my question is which is easier way to do that? In another word, Excel can do that ? or Any other program ?
In my opinion, Excel is not suitable for this type (or any type for that matter) of Data Management.

You would be much better off using Microsoft Access (which you probably have).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I think this is a good way for the issue because I do not know how to use macro.

Here's how to install and use a macro.
In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’.  Then anytime you want to run the macro press Ctrl+Shift+A

What I want is, for example, to pick out the unit which has "100" among the four units and put them together.
That sounds like what should be a new question since you didn't say anything about it in your original post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.