Formatting Tables

[justlearning4]

I have searched through the existing threads that pertain to things like this and so far, I have not been able to find a solution. I have this text file (see number 2 below) that I need to get specific data from and load it into excel; the first line of each data chunk is the part that I am looking for specifically. Ideally, I would like to then space this data out into separate columns based on the "space" delimiter that is between each word/number/date/character. Additionally, I would like this to be repeatable since this is a project that will occur on a weekly basis.


For reference, this is what I would like the first lines of each data chunk to look like once it's brought into excel.

CITY AND COUNTY OF REGION, MEXICO	11/06	1,000,000.00	Aaa3/AA-/AA-/NR
New Caldwell State	11/04	1,000,000.00	Aaa/AAA/AAA/NR

2. For reference, here is a sample of how the txt file will look once it is loaded into excel. The code that I am looking for would then take the data from the sheet that the txt file loads into and converts it into the format I laid out above:

Book3
	A	B
1	Column1
2	CITY AND COUNTY OF REGION, MEXICO 11/06 1,000,000.00 Aa3/AA-/AA-/NR
3	FOR AND ON BEHALF OF ITS DEPARTMENT OF AVIATION
4	AIRPORT SYSTEM REVENUE BONDS
5	SERIES 2022C (FIXED RATE NON-AMT)
6	SERIES 2022D (FIXED RATE AMT)
7	SERIES 2022E (TERM RATE NON-AMT)
8	MGR: Barclays Capital Inc., New York
9
10
11	New Caldwell State 11/04 1,000,000.000 Aaa/AAA/AAA/NR
12	Environmental Facilities Corporation
13	State Revolving Funds Revenue Bonds, Series 2022 B
14	(2010 Master Financing Program) (Green Bonds)
15	MGR: Jefferies LLC, New York
16
17
NEGOTIATED_OFFERING_DATA

 

[datatronics505]

From what you presented, there has to be some clearing of the input data before you attempt to import it into Excel. Make your text file into .csv. Use delimeters other than a comma to construct new columns. The example screenshot I attached uses semicolon as a delimiter, not comma. In the origiginal text of that csv file, I used semicolons to separate "CITY AND COUNTRY OF REGION, MEXICO" from "11/6/22" from "1000000" etc. When importing the file into Excel, set that delimiter instead of a comma:

 

[justlearning4]

From what you presented, there has to be some clearing of the input data before you attempt to import it into Excel. Make your text file into .csv. Use delimeters other than a comma to construct new columns. The example screenshot I attached uses semicolon as a delimiter, not comma. In the origiginal text of that csv file, I used semicolons to separate "CITY AND COUNTRY OF REGION, MEXICO" from "11/6/22" from "1000000" etc. When importing the file into Excel, set that delimiter instead of a comma:
View attachment 79609

Thank you for your reply! Is there a code that can automatically input those delimiters that you referenced in your post? The problem I am having is that the source text file cannot be changed.

 

[justlearning4]

From what you presented, there has to be some clearing of the input data before you attempt to import it into Excel. Make your text file into .csv. Use delimeters other than a comma to construct new columns. The example screenshot I attached uses semicolon as a delimiter, not comma. In the origiginal text of that csv file, I used semicolons to separate "CITY AND COUNTRY OF REGION, MEXICO" from "11/6/22" from "1000000" etc. When importing the file into Excel, set that delimiter instead of a comma:
View attachment 79609

If the file was a short list of data, this would be a no-brainer to manually clean it. Unfortunately, the text file contains nearly 150 of those individual data chunks, and the time it would take to manually clean that is unfeasible for what I am trying to do with the data (its a weekly process).

 

[datatronics505]

Unless there is a format and structure that your raw text input follows every week you receive it, then every week it is going to be a different formula. Notice how your input in row 2 "CITY AND COUNTRY OF REGION, MEXICO..." further divides into separate segments that each becomes a column, hard to predict without your own description of what you wanted as an output- but then I am assuming each row that is written with all caps becomes a column of its own without being divided like the input in row 2. So, if there is a formula to divide the contents of that row 2 into what you said you want to see as an output, that formula won't be good for the rest of the text.
Further, it looks like the double line feed is a delimiter for the list of actual values within the columns previously listed in all caps ("CITY AND COUNTRY OF REGION, MEXICO" column underneath has a value of "New Caldwell State", the date in the row for "New Caldwell State" becomes the value for column "11/14") etc. That structure has to repeat week after week, that row 2 has to have the same format week after week, that format of the first row after the double line feed has to repeat every week for the formula to work- and more, importing has to happen to exactly the same cell every time your new text comes in.
My point is, you'll have to use some text pre-processing utility outside of Excell to clean up your weekly raw text input and apply some predictable structure and format before importing that text into Excel. Python and AWK/regular expressions come to mind.

 

[datatronics505]

... if this is something you had in mind...

 

[Peter_SSs]

Duplicate to: Text File to Excel Worksheet

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In this particular case I have closed the older thread since this has more responses.

 

[Alex Blakenburg]

I don't know why you didn't just add your XL2BB to your original post as requested by @rlv01, who would have been well placed to help you with this. By doing it as a separate post you have effectively excluded someone who has indicated that they are willing to assist you.

You can try the below on a copy of your workbook.

    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim rngSrc As Range, arrSrc As Variant
    Dim rngDest As Range
    Dim lrowSrc As Long
    Dim arrDest As Variant
    Dim destHdg As Variant
    Dim fullString As String, p1_desc As String, p2_sdate As String
    Dim p3_amt As Variant, p4_info As String
    Dim arrSplit As Variant
    Dim i As Long, j As Long, idest As Long
    
    Set wsSrc = Worksheets("NEGOTIATED_OFFERING_DATA")
    With wsSrc
        lrowSrc = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngSrc = .Range("A2:A" & lrowSrc)
    End With
    
    arrSrc = rngSrc.Value
    ReDim arrDest(1 To UBound(arrSrc), 1 To 4)
    
    Set wsDest = wsSrc                                      ' To facilitate changing output to a different sheet later
    Set rngDest = wsDest.Range("C1")
    destHdg = Array("Name", "Date", "Amount", "More Info")
    
    For i = 1 To UBound(arrSrc)
        If i = 1 Then
            fullString = arrSrc(i, 1)
        ElseIf arrSrc(i, 1) <> "" And Trim(arrSrc(i - 1, 1)) = "" Then
            fullString = arrSrc(i, 1)
        End If
        
        ' Split first row in each data set
        If fullString <> "" Then
        arrSplit = Split(fullString, " ")
            For j = 0 To UBound(arrSplit)
                If Len(arrSplit(j)) = 5 Then
                    If InStr(arrSplit(j), "/") Then
                        p2_sdate = arrSplit(j)
                        p3_amt = arrSplit(j + 1)
                        
                        p1_desc = Left(fullString, InStr(fullString, p2_sdate) - 2)
                        p4_info = Right(fullString, Len(fullString) - InStr(fullString, p3_amt) - Len(p3_amt))
                        fullString = ""
                        idest = idest + 1
                        arrDest(idest, 1) = p1_desc
                        arrDest(idest, 2) = p2_sdate
                        arrDest(idest, 3) = p3_amt
                        arrDest(idest, 4) = p4_info
                        Exit For
                    End If
                End If
            Next j
        End If
    Next i
    
    ' Output results
    With rngDest
        .Resize(, UBound(destHdg) + 1).Value = destHdg
        .Resize(, UBound(destHdg) + 1).Font.Bold = True
        .Offset(1, 1).Resize(idest, 2).NumberFormat = "@"
        .Offset(1).Resize(idest, UBound(destHdg) + 1).Value = arrDest
        .Offset(1).Resize(idest, UBound(destHdg) + 1).EntireColumn.AutoFit
    End With
End Sub

 

[John_w]

I have searched through the existing threads that pertain to things like this and so far, I have not been able to find a solution. I have this text file (see number 2 below) that I need to get specific data from and load it into excel; the first line of each data chunk is the part that I am looking for specifically.

You don't explicitly say it, but if the 11/06 and 11/04 examples are MM/DD dates and they only occur in the lines you want to extract then a regular expression can be used to identify those lines and parse them into Excel.

This macro reads the text file directly (no need to import it or copy and paste it into Excel first) and parses the required data into 4 columns in the active sheet (modify the textFile string to suit).

Public Sub Import_Specific_Data_From_Text_File()

    Dim reMMDD As Object 'VBScript_RegExp_55.RegExp
    Dim matches As Object 'VBScript_RegExp_55.MatchCollection
    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String, fileLine As Variant
    Dim destCells As Range, n As Long
    Dim amount As String, info As String

    textFile = "C:\path\to\THE TEXT FILE.txt"     'CHANGE THIS
    
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:D1").Value = Array("Name", "Date", "Amount", "More Info")
        Set destCells = .Range("A2:D2")
        n = 0
    End With
    
    Set reMMDD = CreateObject("VBScript.RegExp") 'New VBScript_RegExp_55.RegExp
    With reMMDD
        .Pattern = "\s(0[1-9]|1[012])/(0[1-9]|[12][0-9]|3[01])\s"  ' match " mm/dd "
        .Global = True
    End With
    
    fileNum = FreeFile
    Open textFile For Binary As #fileNum
    fileData = Space(LOF(fileNum))
    Get #fileNum, , fileData
    Close #fileNum
    
    For Each fileLine In Split(fileData, vbCrLf)
        Set matches = reMMDD.Execute(fileLine)
        If matches.Count = 1 Then
            amount = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(0)
            info = Split(Mid(fileLine, matches(0).FirstIndex + matches(0).Length + 1), " ")(1)
            destCells.Offset(n).Value = Array(Left(fileLine, matches(0).FirstIndex), "'" & Trim(matches(0).Value), amount, info)
            n = n + 1
        End If
    Next
    
End Sub

 

[justlearning4]

Duplicate to: Text File to Excel Worksheet

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In this particular case I have closed the older thread since this has more responses.

Sorry about that chief. Will not happen again.