Paste and automatic sorting to rows and columns of hexadecimal data from text file

S

Ste

New Member
Joined
Jun 23, 2021
Messages
3
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello

I am trying to create a template for reading a vast amount of hexadecimal data collected by a data logger on my test equipment at work.

Usually I would split the data in to the correct column length (in notepad) then copy, paste to excel, followed by text to columns function. Once in my spreadsheet the formulas, to the right of the collected data, decipher in hexadecimal data etc.

However the data logger can be running for weeks and manually sorting the text file before importing the data to Excel would be a mammoth task so I want to try automate the population of columns and rows.

So is it possible to create a template where you can paste a long string of information (as per the .txt image below) and it auto populates in a spread sheet?
1624444361863.png


For example: the copied data needs to have a separate column whenever there is a “space” (which I can do with text to columns) and after 21 columns it needs to start a new row (this is where I get stuck). The below image is how I require the data to be sorted. Columns A-U then a new row is started.
1624445219583.png


Any advice would be greatly appreciated.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Radoslaw Poprawski said:
can u provide that txt file?
Click to expand...
Hi

Thanks for your reply.

I am unsure how to attach a file so i have pasted the data below. Is that ok?

01 00 11 81 00 08 01 21 06 18 11 51 08 00 01 00 00 3C 3C 03 C0 01 00 11 81 01 08 01 21 06 18 11 51 08 01 01 00 00 0A 8C 03 E0 01 00 11 81 02 08 01 21 06 18 11 51 08 01 02 00 00 0B 86 03 DD 01 00 11 81 03 08 01 21 06 18 11 51 08 01 03 00 00 13 1A 03 7B 01 00 11 81 04 08 01 21 06 18 11 51 08 01 04 00 00 13 1A 03 7D 00 01 00 11 81 00 08 01 21 06 18 11 51 21 00 01 00 00 00 00 03 61 01 00 11 81 01 08 01 21 06 18 11 51 21 01 01 00 00 00 00 00 60 01 00 11 81 02 08 01 21 06 18 11 51 21 01 02 00 00 00 00 00 62 01 00 11 81 03 08 01 21 06 18 11 51 21 01 03 00 00 00 00 00 64 01 00 11 81 04 08 01 21 06 18 11 51 21 01 04 00 00 00 00 00 66 01 00 11 81 05 08 01 21 06 18 11 51 30 00 01 00 00 3C 46 03 F7 01 00 11 81 06 08 01 21 06 18 11 51 30 01 01 00 00 0A 8C 03 0D 01 00 11 81 07 08 01 21 06 18 11 51 30 01 02 00 00 0B 90 03 14 01 00 11 81 08 08 01 21 06 18 11 51 30 01 03 00 00 13 1A 03 A8 01 00 11 81 09 08 01 21 06 18 11 51 30 01 04 00 00 13 1A 03 AA 01 00 11 81 0A 08 01 21 06 18 11 51 40 00 01 00 00 3C 3C 03 02 01 00 11 81 0B 08 01 21 06 18 11 51 40 01 01 00 00 0A 8C 03 22 01 00 11 81 0C 08 01 21 06 18 11 51 40 01 02 00 00 0B 90 03 29 01 00 11 81 0D 08 01 21 06 18 11 51 40 01 03 00 00 13 1A 03 BD 01 00 11 81 0E 08 01 21 06 18 11 51 40 01 04 00 00 13 1A 03 BF 01 00 11 81 0F 08 01 21 06 18 11 51 50 00 01 00 00 3C 3C 03 17 01 00 11 81 10 08 01 21 06 18 11 51 50 01 01 00 00 0A 82 03 2D 01 00 11 81 11 08 01 21 06 18 11 51 50 01 02 00 00 0B 90 03 3E 01 00 11 81 12 08 01 21 06 18 11 51 50 01 03 00 00 13 1A 03 D2 01 00 11 81 13 08 01 21 06 18 11 51 50 01 04 00 00 13 1A 03 D4
 
Upvote 0
Book3
ABCDEFGHIJKLMNOPQRSTU
1123456789101112131415161718192021
2001181000801210618115108000100003C3C03C001
3001181010801210618115108010100000A8C03E001
4001181020801210618115108010200000B8603DD01
500118103080121061811510801030000131A037B01
600118104080121061811510801040000131A037D00
7010011810008012106181151210001000000000361
8010011810108012106181151210101000000000060
9010011810208012106181151210102000000000062
10010011810308012106181151210103000000000064
11010011810408012106181151210104000000000066
1201001181050801210618115130000100003C4603F7
1301001181060801210618115130010100000A8C030D
1401001181070801210618115130010200000B900314
150100118108080121061811513001030000131A03A8
160100118109080121061811513001040000131A03AA
17010011810A0801210618115140000100003C3C0302
18010011810B0801210618115140010100000A8C0322
19010011810C0801210618115140010200000B900329
20010011810D080121061811514001030000131A03BD
21010011810E080121061811514001040000131A03BF
22010011810F0801210618115150000100003C3C0317
2301001181100801210618115150010100000A82032D
2401001181110801210618115150010200000B90033E
250100118112080121061811515001030000131A03D2
260100118113080121061811515001040000131A03D4
Sheet1


Power Query: 
let
    Source = Csv.Document(File.Contents("C:\Users\rjpop\Desktop\hex data.txt"),526,"",ExtraValues.Ignore,1250),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column1", "Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 21)+1, type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL")[Modulo]), "Modulo", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

1drv.ms

hex data.xlsx

Microsoft Excel Workbook
1drv.ms

im sharing the file for ur convinience.
Veryfy with provided sample if all matches with ur needs.
 
Upvote 0
Radoslaw Poprawski said:
Radoslaw Poprawski said:
Book3
ABCDEFGHIJKLMNOPQRSTU
1123456789101112131415161718192021
2001181000801210618115108000100003C3C03C001
3001181010801210618115108010100000A8C03E001
4001181020801210618115108010200000B8603DD01
500118103080121061811510801030000131A037B01
600118104080121061811510801040000131A037D00
7010011810008012106181151210001000000000361
8010011810108012106181151210101000000000060
9010011810208012106181151210102000000000062
10010011810308012106181151210103000000000064
11010011810408012106181151210104000000000066
1201001181050801210618115130000100003C4603F7
1301001181060801210618115130010100000A8C030D
1401001181070801210618115130010200000B900314
150100118108080121061811513001030000131A03A8
160100118109080121061811513001040000131A03AA
17010011810A0801210618115140000100003C3C0302
18010011810B0801210618115140010100000A8C0322
19010011810C0801210618115140010200000B900329
20010011810D080121061811514001030000131A03BD
21010011810E080121061811514001040000131A03BF
22010011810F0801210618115150000100003C3C0317
2301001181100801210618115150010100000A82032D
2401001181110801210618115150010200000B90033E
250100118112080121061811515001030000131A03D2
260100118113080121061811515001040000131A03D4
Sheet1


Power Query: 
let
    Source = Csv.Document(File.Contents("C:\Users\rjpop\Desktop\hex data.txt"),526,"",ExtraValues.Ignore,1250),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column1", "Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 21)+1, type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL")[Modulo]), "Modulo", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

1drv.ms

hex data.xlsx

Microsoft Excel Workbook
1drv.ms

im sharing the file for ur convinience.
Veryfy with provided sample if all matches with ur needs.
Click to expand...


Power Query: 
let
    Source = Csv.Document(File.Contents("C:\Users\rjpop\Desktop\hex data.txt"),526,"",ExtraValues.Ignore,1250),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column1", "Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 21)+1, type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL")[Modulo]), "Modulo", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

1drv.ms

hex data.xlsx

Microsoft Excel Workbook
1drv.ms

im sharing the file for ur convinience.
Veryfy with provided sample if all matches with ur needs.
Click to expand...
Power Query: 
[CODE=vba]
[/CODE]
Radoslaw Poprawski said:
Book3
ABCDEFGHIJKLMNOPQRSTU
1123456789101112131415161718192021
2001181000801210618115108000100003C3C03C001
3001181010801210618115108010100000A8C03E001
4001181020801210618115108010200000B8603DD01
500118103080121061811510801030000131A037B01
600118104080121061811510801040000131A037D00
7010011810008012106181151210001000000000361
8010011810108012106181151210101000000000060
9010011810208012106181151210102000000000062
10010011810308012106181151210103000000000064
11010011810408012106181151210104000000000066
1201001181050801210618115130000100003C4603F7
1301001181060801210618115130010100000A8C030D
1401001181070801210618115130010200000B900314
150100118108080121061811513001030000131A03A8
160100118109080121061811513001040000131A03AA
17010011810A0801210618115140000100003C3C0302
18010011810B0801210618115140010100000A8C0322
19010011810C0801210618115140010200000B900329
20010011810D080121061811514001030000131A03BD
21010011810E080121061811514001040000131A03BF
22010011810F0801210618115150000100003C3C0317
2301001181100801210618115150010100000A82032D
2401001181110801210618115150010200000B90033E
250100118112080121061811515001030000131A03D2
260100118113080121061811515001040000131A03D4
Sheet1


Power Query: 
let
    Source = Csv.Document(File.Contents("C:\Users\rjpop\Desktop\hex data.txt"),526,"",ExtraValues.Ignore,1250),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column1", "Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 21)+1, type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "pl-PL")[Modulo]), "Modulo", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

1drv.ms

hex data.xlsx

Microsoft Excel Workbook
1drv.ms

im sharing the file for ur convinience.
Veryfy with provided sample if all matches with ur needs.
Click to expand...
Hello

I am unable to do anything with this due to the IT systems at work blocking access to the file.

I am not familiar with power queries and code for Excel. I was hoping there would be a simple solution already available in Excel to automatically sort the data.

I have noticed some issues with my data that when there is a power interrupt the output device sends some 0x00 values which will confuse the situation and result in the data being misinterpreted.

I feel this task may need some more thought at my end for now. Also I need to vastly improve my Excel knowledge

Thank you for you replies, sorry for wasting your time today.
 
Upvote 0
then create blank query, op[en avanced editor
paste that mcode in.
Change file path to the folder ur keeping that txt file and load the query.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
709
Micron
M
B
  • Question Question
Table refresh removes Data Validation value from previous last row and pastes it to newly imported last row
Replies
0
Views
227
bdbilotta
B
C
  • Solved
Transposing Columns to Rows
2
Replies
10
Views
194
Fluff
Fluff
N
  • Question Question
Read/Write between Excel and Text Files
Replies
4
Views
213
Vogateer
V
C
  • Solved
Copy paste columns based on matching header into another workbook
Replies
5
Views
112
mumps
mumps

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top