none
Reading repeating lines and extracting values RRS feed

  • Question

  • Hi Experts,

    I am new to VBA and I need to read a txt file and fetch some values from it and then create an excel file, please guide me through it. For example I want to read this file :

    MODULOS PVCS
     ---------------
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-MigrationTUC-ES1104125-migration#xdsl$NEBA.xslt   1.39
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-NewInstallTUC-ES1102498-OSM#Fiber#Activation#With#Television.xslt   1.83
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-Profile#ChangeTUC-ES1103027-OSM#Fiber#Speed#Change.xslt   1.31
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   TIBCO_SERPA-API30TUC-ES1108328-RoamingActivation.xslt   1.3
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   TIBCO_SERPA-API30TUC-ES1108328-RoamingDeactivation.xslt   1.2

    I want to fetch values after CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF and create an excel file.

    Regards,

    Rahul Madaan

    Monday, November 14, 2016 7:13 AM

Answers

  • Sub GetMarkedData()
        Dim strReadData As String
        Dim strFName As String
        Dim intFNum As Integer
        Dim i As Integer
        Dim lngCounter As Long
        Dim wkbkImport As Workbook
        Dim shtImport As Worksheet
        Dim v As Variant
        
        strFName = Application.GetOpenFilename(Title:="Select the file", MultiSelect:=False)
        If strFName = "" Then End
        
        intFNum = FreeFile()
        Open strFName For Input As #intFNum
        Set wkbkImport = Workbooks.Add(Template:=xlWorksheet)
        Set shtImport = wkbkImport.Worksheets(1)
        lngCounter = 1
        
        Do While Seek(intFNum) <= LOF(intFNum)
            Line Input #intFNum, strReadData
            v = Split(strReadData, " ")
            If v(0) = "CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF" Then
                For i = 1 To UBound(v)
                    shtImport.Cells(lngCounter, i).Value = v(i)
                Next i
                lngCounter = lngCounter + 1
            End If
        Loop
        
        Close intFNum
    End Sub

    Monday, November 14, 2016 3:55 PM

All replies

  • Sub GetMarkedData()
        Dim strReadData As String
        Dim strFName As String
        Dim intFNum As Integer
        Dim i As Integer
        Dim lngCounter As Long
        Dim wkbkImport As Workbook
        Dim shtImport As Worksheet
        Dim v As Variant
        
        strFName = Application.GetOpenFilename(Title:="Select the file", MultiSelect:=False)
        If strFName = "" Then End
        
        intFNum = FreeFile()
        Open strFName For Input As #intFNum
        Set wkbkImport = Workbooks.Add(Template:=xlWorksheet)
        Set shtImport = wkbkImport.Worksheets(1)
        lngCounter = 1
        
        Do While Seek(intFNum) <= LOF(intFNum)
            Line Input #intFNum, strReadData
            v = Split(strReadData, " ")
            If v(0) = "CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF" Then
                For i = 1 To UBound(v)
                    shtImport.Cells(lngCounter, i).Value = v(i)
                Next i
                lngCounter = lngCounter + 1
            End If
        Loop
        
        Close intFNum
    End Sub

    Monday, November 14, 2016 3:55 PM
  • Hi Bernie,

    I used your code to run the macro but it is giving "Subscript of range" error.

    Please suggest what to do ?

    Regards,

    Rahul Madaan

    Monday, November 14, 2016 4:45 PM
  • Which line does the code break on?

    When I tested, it worked with the dummy file I made up - maybe the extra spaces are the problem, or lines without spaces....  So try this version.

    If that does not work, upload a file (with sensitive data removed) that produces an error, and share the link here.

    Sub GetMarkedData()
        Dim strReadData As String
        Dim strFName As String
        Dim intFNum As Integer
        Dim i As Integer
        Dim lngCounter As Long
        Dim wkbkImport As Workbook
        Dim shtImport As Worksheet
        Dim v As Variant
        
        strFName = Application.GetOpenFilename(Title:="Select the file", MultiSelect:=False)
        If strFName = "" Then End
        
        intFNum = FreeFile()
        Open strFName For Input As #intFNum
        Set wkbkImport = Workbooks.Add(Template:=xlWorksheet)
        Set shtImport = wkbkImport.Worksheets(1)
        lngCounter = 1
        
        Do While Seek(intFNum) <= LOF(intFNum)
            Line Input #intFNum, strReadData
            v = Split(Trim(strReadData), " ")
            If v(0) = "CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF" Then
                If UBound(v) > 0 Then

                For i = 1 To UBound(v)
                    shtImport.Cells(lngCounter, i).Value = v(i)
                Next i
                lngCounter = lngCounter + 1

                End If
            End If
        Loop
        
        Close intFNum
    End Sub




    Monday, November 14, 2016 5:33 PM
  • Hi Rahul_Madaan,

    I try to test the code suggested by Bernie Deitrick,  Excel MVP 2000-2010 and it is working correctly on my side.

    I suggest you to please again test the code and check it work for you or not.

    if it work as expected then please mark the suggestion given by the Bernie Deitrick,  Excel MVP 2000-2010 as an answer.

    if it not work then let us know about that so that we can try to suggest you further to solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 15, 2016 6:10 AM
  • Hi Bernie,

    Please find below the file, I don't know how to upload via link :

    COMANCHE: Datos Paquete
    Identificador      : 224846
    Tipo Aplicación    : IAL - PPRD1
    Aplicación         : XSLT IAL
    Release            : ES1611FD
    Codigo Proyecto    : UNP03345F01V00
    Service Request    : UNP03345F01V00
    Descripción        : UNP03345F01V00#*#Roaming Limite consumo CBU&Micro
    Estado             : en construcción
    Entorno Origen     : desarrollo
    Entorno Destino    : preproducción1
    Usuario Creador    : pomalik3
    Fecha Creación     : 21-10-2016 13:33:38
    Última Modificación: 09-11-2016 09:55:45
    Usuario            : pomalik3

     MODULOS PVCS
     ---------------
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-MigrationTUC-ES1104125-migration#xdsl$NEBA.xslt   1.39
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-NewInstallTUC-ES1102498-OSM#Fiber#Activation#With#Television.xslt   1.83
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   OSM-Profile#ChangeTUC-ES1103027-OSM#Fiber#Speed#Change.xslt   1.31
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   TIBCO_SERPA-API30TUC-ES1108328-RoamingActivation.xslt   1.3
    CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF   TIBCO_SERPA-API30TUC-ES1108328-RoamingDeactivation.xslt   1.2

     MODULOS DATOS
     ---------------

     REFERENCIAS
     ---------------
    path :/home/tibuat/products/soporte/scripts/IALTestDataMapping please run script at above path script : IALDataMapping.sh

    Now below is the requirement :

    • Poll a location for new file arrival.
    • When a new file arrives parse that file to crate a excel file.
    • Create a new row with value in Identificador and create a new cell with this row with values in CRM/XSLT/IAL/TBL_MAPPINGXSLT/XSLTDEF. I need to append these values in cell separated by enter

    Please help !!!

    Regards,

    Rahul Madaan

    Tuesday, November 15, 2016 8:22 AM
  • I don't know all the details but you should consider using a database rather than using an Excel workbook as a database.  I have done this before and it is very unreliable.  Also you need to consider several things:

    1. Make sure the file has completed writing before you process it.  You should open it with a lock to make sure it has exclusive rights to file:

    Open myFile For Input Lock Read As #1

    2. Make sure you have a reliable backup plan for the Excel file.

    3. Saving text files in case they are not processed correctly.

    Tuesday, November 15, 2016 1:58 PM