• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Table Formatting

P

premjeetgupta

Member
Hi Friends,

Attaching a text file along with excel file. Can anyone please help me any way (formaula / macro) from that i can convert the text file to excel ( in format) attached. I have 100+ files like that.
Following rows are not required which contains text.

"DCM"
"Signal"
"Mux"
"------------------------------"
"Measurement"
"OCH trail"
"Show-xc"
"dB"


Thanks in advance.


Thanks,
Premjeet
 

Attachments

  • UEBGRMBGRM1ILAO32101-UEKNNJALDR1NA1O32101_9420.txt
    4.6 KB · Views: 10
  • Result.xlsx
    10.1 KB · Views: 11
I think this will get you pretty close. Just need to clean up header afterwards.

Open text file in XL with no special options. Should see all the data in a single column.
Do a Find & Replace. Replace double space " " with pipe symbol "|".
THen do a Text to Columns, Delimited, using pipe symbol. Treat consecutive delimiters as one.
 
Luke M said:
I think this will get you pretty close. Just need to clean up header afterwards.

Open text file in XL with no special options. Should see all the data in a single column.
Do a Find & Replace. Replace double space " " with pipe symbol "|".
THen do a Text to Columns, Delimited, using pipe symbol. Treat consecutive delimiters as one.
Click to expand...

Thanks Luke for you response. But by this way it mess the whole data.
Can you suggest any alternate.
 
Ok, then PQ is definitely well suited for this.
You mentioned that you need to do this for 100+ files. Should each be merged in to one table in the format you uploaded?

If so, can you upload 2nd sample file? It makes it so much easier for me to test solution :)
 
T
Chihiro said:
Ok, then PQ is definitely well suited for this.
You mentioned that you need to do this for 100+ files. Should each be merged in to one table in the format you uploaded?

If so, can you upload 2nd sample file? It makes it so much easier for me to test solution :)
Click to expand...

Sure, Please refer attached 3 text files.

Thanks
 

Attachments

  • UEBGRMBGRM1ILAO32101-UEKNNJALDR1NA1O32101_9420.txt
    4.6 KB · Views: 7
  • UEKNNJALDR1NA1O32101-UEFKBGKDGT1NA1O32101_9340 .txt
    4.6 KB · Views: 2
  • UELCKNQMNR1SA2T32101 -UEBGRMBGRM1ILAO32101_9430.txt
    6.3 KB · Views: 3
Here you go. Sample file attached. You will need to change fPath parameter to folder of your choice.

upload_2017-11-30_13-3-36.png

PowerTrim is custom function used to remove space character from string (since Text.Trim was unable to handle it). You can find explanation in the link.
https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/


Edit: FYI - I kept the file name column for reference. But You can remove it in the PQ editor if not needed.
 

Attachments

  • Result.xlsx
    27.6 KB · Views: 5
Last edited:
Hm, I just realized file has Exp AZ & Meas AZ as well as Exp ZA & Meas ZA.

If that's the case, you'll need to add 2 additional replace value steps (2 & 3) in "Transform Sample File from TextImport (2)" steps. See attached.

upload_2017-11-30_13-24-51.png
 

Attachments

  • Result.xlsx
    27.8 KB · Views: 2
1. With 100+ files, do you put all the files' data in one table, in separate tables, or even in separate sheets?
2. How do you decide which txt files to process? Are they all in one folder and you want to process all such files in that folder, or would you like to be able to pick and choose which file(s) you process?
 
Chihiro said:
Here you go. Sample file attached. You will need to change fPath parameter to folder of your choice.

View attachment 47740

PowerTrim is custom function used to remove space character from string (since Text.Trim was unable to handle it). You can find explanation in the link.
https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/


Edit: FYI - I kept the file name column for reference. But You can remove it in the PQ editor if not needed.
Click to expand...

Thanks Chihiro for your response.

Can you guide how to do this. I'm new with Power query. I'm not able to fine options which you have shared with snap. Thanks.
 
p45cal said:
1. With 100+ files, do you put all the files' data in one table, in separate tables, or even in separate sheets?
2. How do you decide which txt files to process? Are they all in one folder and you want to process all such files in that folder, or would you like to be able to pick and choose which file(s) you process?
Click to expand...


Hi P45cal,

Thanks for looking on my query.

1. I have to prepare separate tables/sheet for each text file.

2. Yes all text file in a one folder but each text file have a unqiue name. Yes I would like to pick/choose one and process the same in require format.

Thanks,
Premjeet.
 
In the attached, a macro blah, which can be run manually or by clicking one of the buttons on the 2 sheets in that file.
You can use the usual mouse'click/keyboard arrow key combinations of Shift, Ctrl, Ctrl+a etc. to select which file(s) you want processing.
A new sheet is created for each text file.

The code in that macro (it's already in the file):
Code: 
Sub blah()
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = ""
  .Title = "Choose the file(s) you want to process"
  .Filters.Add "text Files", "*.txt", 1
  .AllowMultiSelect = True
  If .Show = -1 Then
    For Each fil In .SelectedItems
      Dim myResult()
      pd = CreateObject("scripting.filesystemobject").opentextfile(fil).readall
      pd2 = Split(pd, vbCrLf)
      ReDim myResult(1 To UBound(pd2), 1 To 7)
      k = 0
      For i = LBound(pd2) To UBound(pd2)
        If Trim(pd2(i)) <> "" Then
          If InStr(1, pd2(i), "-----", vbTextCompare) = 0 Then
            If InStr(1, pd2(i), "DCM", vbTextCompare) = 0 Then
              If InStr(1, pd2(i), "Signal", vbTextCompare) = 0 Then
                If InStr(1, pd2(i), "Mux", vbTextCompare) = 0 Then
                  If InStr(1, pd2(i), "db", vbTextCompare) = 0 Then
                    If InStr(1, pd2(i), "measurement", vbTextCompare) = 0 Then
                      If InStr(1, pd2(i), "och-trail", vbTextCompare) = 0 Then
                        If InStr(1, pd2(i), "och trail", vbTextCompare) = 0 Then
                          If InStr(1, pd2(i), "show-xc", vbTextCompare) = 0 Then
                            'if code execution reaches here then it's a row to be added.
                            k = k + 1
                            x1 = Split(Application.Trim(Left(pd2(i), 38)), " ")
                            x2 = Mid(pd2(i), 39)
                            myResult(k, 1) = x1(0)
                            myResult(k, 4) = Trim(Mid(x2, 1, 9))
                            myResult(k, 5) = Trim(Mid(x2, 10, 10))
                            myResult(k, 6) = Trim(Mid(x2, 20, 8))
                            myResult(k, 7) = Trim(Mid(x2, 28))
                            If UBound(x1) > 0 Then
                              myResult(k, 2) = x1(1)
                              myResult(k, 3) = x1(2)
                            End If
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      Next i
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Cells(1).Resize(k, 1).NumberFormat = "@"  'to stop Excel interpreting the likes of 1/24/9420 as a date.
        .Cells(1).Resize(k, 7).Value = myResult
        .Columns("A:G").EntireColumn.AutoFit
      End With
    Next fil
  End If
End With
End Sub
 

Attachments

  • chandoo36569Result.xlsm
    40.3 KB · Views: 1
p45cal said:
In the attached, a macro blah, which can be run manually or by clicking one of the buttons on the 2 sheets in that file.
You can use the usual mouse'click/keyboard arrow key combinations of Shift, Ctrl, Ctrl+a etc. to select which file(s) you want processing.
A new sheet is created for each text file.

The code in that macro (it's already in the file):
Code: 
Sub blah()
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = ""
  .Title = "Choose the file(s) you want to process"
  .Filters.Add "text Files", "*.txt", 1
  .AllowMultiSelect = True
  If .Show = -1 Then
    For Each fil In .SelectedItems
      Dim myResult()
      pd = CreateObject("scripting.filesystemobject").opentextfile(fil).readall
      pd2 = Split(pd, vbCrLf)
      ReDim myResult(1 To UBound(pd2), 1 To 7)
      k = 0
      For i = LBound(pd2) To UBound(pd2)
        If Trim(pd2(i)) <> "" Then
          If InStr(1, pd2(i), "-----", vbTextCompare) = 0 Then
            If InStr(1, pd2(i), "DCM", vbTextCompare) = 0 Then
              If InStr(1, pd2(i), "Signal", vbTextCompare) = 0 Then
                If InStr(1, pd2(i), "Mux", vbTextCompare) = 0 Then
                  If InStr(1, pd2(i), "db", vbTextCompare) = 0 Then
                    If InStr(1, pd2(i), "measurement", vbTextCompare) = 0 Then
                      If InStr(1, pd2(i), "och-trail", vbTextCompare) = 0 Then
                        If InStr(1, pd2(i), "och trail", vbTextCompare) = 0 Then
                          If InStr(1, pd2(i), "show-xc", vbTextCompare) = 0 Then
                            'if code execution reaches here then it's a row to be added.
                            k = k + 1
                            x1 = Split(Application.Trim(Left(pd2(i), 38)), " ")
                            x2 = Mid(pd2(i), 39)
                            myResult(k, 1) = x1(0)
                            myResult(k, 4) = Trim(Mid(x2, 1, 9))
                            myResult(k, 5) = Trim(Mid(x2, 10, 10))
                            myResult(k, 6) = Trim(Mid(x2, 20, 8))
                            myResult(k, 7) = Trim(Mid(x2, 28))
                            If UBound(x1) > 0 Then
                              myResult(k, 2) = x1(1)
                              myResult(k, 3) = x1(2)
                            End If
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      Next i
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Cells(1).Resize(k, 1).NumberFormat = "@"  'to stop Excel interpreting the likes of 1/24/9420 as a date.
        .Cells(1).Resize(k, 7).Value = myResult
        .Columns("A:G").EntireColumn.AutoFit
      End With
    Next fil
  End If
End With
End Sub
Click to expand...


Thanks @p45cal this what i was looking. Thank you for your kind support.
 
p45cal said:
In the attached, a macro blah, which can be run manually or by clicking one of the buttons on the 2 sheets in that file.
You can use the usual mouse'click/keyboard arrow key combinations of Shift, Ctrl, Ctrl+a etc. to select which file(s) you want processing.
A new sheet is created for each text file.

The code in that macro (it's already in the file):
Code: 
Sub blah()
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = ""
  .Title = "Choose the file(s) you want to process"
  .Filters.Add "text Files", "*.txt", 1
  .AllowMultiSelect = True
  If .Show = -1 Then
    For Each fil In .SelectedItems
      Dim myResult()
      pd = CreateObject("scripting.filesystemobject").opentextfile(fil).readall
      pd2 = Split(pd, vbCrLf)
      ReDim myResult(1 To UBound(pd2), 1 To 7)
      k = 0
      For i = LBound(pd2) To UBound(pd2)
        If Trim(pd2(i)) <> "" Then
          If InStr(1, pd2(i), "-----", vbTextCompare) = 0 Then
            If InStr(1, pd2(i), "DCM", vbTextCompare) = 0 Then
              If InStr(1, pd2(i), "Signal", vbTextCompare) = 0 Then
                If InStr(1, pd2(i), "Mux", vbTextCompare) = 0 Then
                  If InStr(1, pd2(i), "db", vbTextCompare) = 0 Then
                    If InStr(1, pd2(i), "measurement", vbTextCompare) = 0 Then
                      If InStr(1, pd2(i), "och-trail", vbTextCompare) = 0 Then
                        If InStr(1, pd2(i), "och trail", vbTextCompare) = 0 Then
                          If InStr(1, pd2(i), "show-xc", vbTextCompare) = 0 Then
                            'if code execution reaches here then it's a row to be added.
                            k = k + 1
                            x1 = Split(Application.Trim(Left(pd2(i), 38)), " ")
                            x2 = Mid(pd2(i), 39)
                            myResult(k, 1) = x1(0)
                            myResult(k, 4) = Trim(Mid(x2, 1, 9))
                            myResult(k, 5) = Trim(Mid(x2, 10, 10))
                            myResult(k, 6) = Trim(Mid(x2, 20, 8))
                            myResult(k, 7) = Trim(Mid(x2, 28))
                            If UBound(x1) > 0 Then
                              myResult(k, 2) = x1(1)
                              myResult(k, 3) = x1(2)
                            End If
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      Next i
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Cells(1).Resize(k, 1).NumberFormat = "@"  'to stop Excel interpreting the likes of 1/24/9420 as a date.
        .Cells(1).Resize(k, 7).Value = myResult
        .Columns("A:G").EntireColumn.AutoFit
      End With
    Next fil
  End If
End With
End Sub
Click to expand...

Hi @p45cal can you do some changes with the code to delete column "C", "D", "F" & "G". There is no requirement for that.

Thanks again.
 
Code: 
Sub blah2()
Dim myResult()
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = ""
  .Title = "Choose the file(s) you want to process"
  .Filters.Add "text Files", "*.txt", 1
  .AllowMultiSelect = True
  If .Show = -1 Then
    For Each fil In .SelectedItems
      pd = CreateObject("scripting.filesystemobject").opentextfile(fil).readall
      pd2 = Split(pd, vbCrLf)
      ReDim myResult(1 To UBound(pd2), 1 To 3)
      k = 0
      For i = LBound(pd2) To UBound(pd2)
        If Trim(pd2(i)) <> "" Then
          If InStr(1, pd2(i), "-----", vbTextCompare) = 0 Then
            If InStr(1, pd2(i), "DCM", vbTextCompare) = 0 Then
              If InStr(1, pd2(i), "Signal", vbTextCompare) = 0 Then
                If InStr(1, pd2(i), "Mux", vbTextCompare) = 0 Then
                  If InStr(1, pd2(i), "db", vbTextCompare) = 0 Then
                    If InStr(1, pd2(i), "measurement", vbTextCompare) = 0 Then
                      If InStr(1, pd2(i), "och-trail", vbTextCompare) = 0 Then
                        If InStr(1, pd2(i), "och trail", vbTextCompare) = 0 Then
                          If InStr(1, pd2(i), "show-xc", vbTextCompare) = 0 Then
                            'if code execution reaches here then it's a row to be added.
                            k = k + 1
                            x1 = Split(Application.Trim(Left(pd2(i), 38)), " ")
                            myResult(k, 1) = x1(0)
                            If UBound(x1) > 0 Then myResult(k, 2) = x1(1)
                            myResult(k, 3) = Trim(Mid(Mid(pd2(i), 39), 10, 10))
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      Next i
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Cells(1).Resize(k, 1).NumberFormat = "@"  'to stop Excel interpreting the likes of 1/24/9420 as a date.
        .Cells(1).Resize(k, 3).Value = myResult
        .Columns("A:C").EntireColumn.AutoFit
      End With
    Next fil
  End If
End With
End Sub
 
Can you guide how to do this.
Click to expand...

Steps.
1. Data tab -> New Query -> From File -> From Folder
2. Click on Binary field and expand one of the file
3. Remove unnecessary Column2
4. Added Index column to be used in later step.
5. Add custom column with following formula to serve as grouping index.
Code: 
= if Text.End([Column1],3) = "Dev" then [Index] else if [Column1] = "" then 0 else null
Blanks are marked with 0 since those are not needed and for easy filtering.
Note: I did fill down operation on the column to propagate grouping index. However, realized this was not needed in this case.
6. Filtered out 0.
7. Filtered and kept only rows that does not contain (+/-db) & doesn't begin with "--------"
8. Unlike Excel's Trim function PQ Text.Trim only trims end of string. So used PowerTrim custom function to remove extra spaces.
9. Exp AZ & Meas AZ is really one column so used replace value to replace it with ExpAZ & MeasAZ (also did for ZA).
10. Split column going from Right most delimiter (space) for right 4 columns.
11. Since 2nd column has varying number of spaces, split this time from Left most delimiter.
12. Removed unnecessary column(s). Changed column heading.
13. This creates template for transformation steps.
14. Create another query to folder. Under Combine menu in the editor, find combine files button and click.
15. Choose "First File" and click OK.
16. This creates basic functions to combine files automatically.
17. Now go back to first query to serve as transformation template. Go into advanced editor and copy everything below "Source" line.
18. Go to "Transform Sample File from..." and in advanced editor paste in the above (below source line). Then add comma after source and adjust reference as needed.
19. Create fPath parameter as Text (any) and set Current Value to folder of your choice.

Load to sheet or data model as desired.

The process looks involved and complex. But once you get used to it, this entire process takes about 5 to 10 min to set up. Now you just have to refresh table whenever new file is added to the folder (as long as structure is same).

FYI - PQ excels at data transformation and consolidating multiple sources into one. If data need not be consolidated, use other method (Though you can combine PQ with VBA for exporting consolidated data into individual file/sheets)
 
Chihiro said:
Steps.
1. Data tab -> New Query -> From File -> From Folder
2. Click on Binary field and expand one of the file
3. Remove unnecessary Column2
4. Added Index column to be used in later step.
5. Add custom column with following formula to serve as grouping index.
Code: 
= if Text.End([Column1],3) = "Dev" then [Index] else if [Column1] = "" then 0 else null
Blanks are marked with 0 since those are not needed and for easy filtering.
Note: I did fill down operation on the column to propagate grouping index. However, realized this was not needed in this case.
6. Filtered out 0.
7. Filtered and kept only rows that does not contain (+/-db) & doesn't begin with "--------"
8. Unlike Excel's Trim function PQ Text.Trim only trims end of string. So used PowerTrim custom function to remove extra spaces.
9. Exp AZ & Meas AZ is really one column so used replace value to replace it with ExpAZ & MeasAZ (also did for ZA).
10. Split column going from Right most delimiter (space) for right 4 columns.
11. Since 2nd column has varying number of spaces, split this time from Left most delimiter.
12. Removed unnecessary column(s). Changed column heading.
13. This creates template for transformation steps.
14. Create another query to folder. Under Combine menu in the editor, find combine files button and click.
15. Choose "First File" and click OK.
16. This creates basic functions to combine files automatically.
17. Now go back to first query to serve as transformation template. Go into advanced editor and copy everything below "Source" line.
18. Go to "Transform Sample File from..." and in advanced editor paste in the above (below source line). Then add comma after source and adjust reference as needed.
19. Create fPath parameter as Text (any) and set Current Value to folder of your choice.

Load to sheet or data model as desired.

The process looks involved and complex. But once you get used to it, this entire process takes about 5 to 10 min to set up. Now you just have to refresh table whenever new file is added to the folder (as long as structure is same).

FYI - PQ excels at data transformation and consolidating multiple sources into one. If data need not be consolidated, use other method (Though you can combine PQ with VBA for exporting consolidated data into individual file/sheets)
Click to expand...


Thanks @Chihiro for giving so much time and extra effort. It worked well for me. Thanks. Thanks for your kind support.
 
You must log in or register to reply here.
Back
Top