Any way to create a Mask to Get Data from Text

E

excel?

Board Regular
Joined
Sep 14, 2004
Messages
143
Office Version
  1. 365
Platform
  1. Windows
I have to import data from a .csv file every week. The data is based on a report so throughout the data there is Header information due to the page breaks of the report. Does Excel have any feature that would allow me to create a Mask (or template) that recognizes the layout of the report and only imports the actual data and not the Header information.

In the past, I had access to a software program called DataImport that you could create a mask for and when you imported the .csv file into it. The mask was designed to exclude the header info, so all I imported into Excel was the data.

Wanted to see if Excel has any way to do something similar??

Thanks.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Using XL2BB, please upload a small sample of your data so that we can manipulate it and give you a viable solution. I am thinking that this is easily accomplished using Power Query/Get and Transform, but will need to see the actual layout.
 
Upvote 0
Here is a sample containing 2 pages of data. The 1st 9 rows of each page are repeated at the top of subsequent pages and are what I would like to not be imported. At the bottom of each page there is a little square character in the first column that does not need to be imported either. Thank you.

Book1
ABCDEFGHIJ
1SF9108A 05 7:21:196/29/20 PAGE1
2PRODUCTYIELD PRODUCTIONREPORT
3Seq. By Component
4Tran datefrom: 6/22/20 To: 6/28/20ClassFrom: 600 To: 805 Product From:To:
5================================================================================================================================================================
6AdjustmentVariance
7ProductDescriptionClassQty-ProducedBackFlush-QtyCycle-CountBase-QtyActual-UsageYield %Quantity
8================================================================================================================================================================
9
1081280Pkng:Ctn CF "F" 6 5/16"60076,403.0078,361.70719.0578,361.9577,642.6598.4-719.301
11
1281281Pkng:Ctn CF "G" 5"60028,798.0029,536.451,920.8029,536.3727,615.65104.2-1,920.72
13
1482486Pkng:Ctn CCF "B" light6005,979.006,132.331,084.736,132.305,047.59118.4-1,084.71
15
1682489Pkng:Ctn CCF "I" light6001,461.001,498.46-0.1811,498.461,498.6497.40.182
17
1882494Pkng:Ctn CCF "E" light600718736.406-0.233736.407736.63997.40.232
19
2084476APkng:Ctn Box A6003,547.003,637.95-0.1263,637.943,638.0897.40.136
21
2284477BPkng:Ctn Box B6006,175.006,333.33-0.496,333.326,333.8297.40.497
23
2484478CPkng:Ctn Box C60011,959.0012,265.70-0.03412,265.6312,265.7397.40.105
25
2684479DPkng:Ctn Box D6003,733.003,828.73-0.3553,828.713,829.0897.40.369
27
2884480EPkng:Ctn Box E6003,547.003,637.95-0.1263,637.943,638.0897.40.136
29
3084481FPkng:Ctn Box F6006,175.006,333.33-0.496,333.326,333.8297.40.497
31
3284482GPkng:Ctn Box G60011,959.0012,265.70-0.03412,265.6312,265.7397.40.105
33
3484483HPkng:Ctn Box H6003,733.003,828.73-0.3553,828.713,829.0897.40.369
35
3684484JPkng:Ctn Box J6003,547.003,637.95-0.1263,637.943,638.0897.40.136
37
3884485KPkng:Ctn Box K6006,175.006,333.33-0.496,333.326,333.8297.40.497
39
4084486LPkng:Ctn Box L60011,959.0012,265.70-0.03412,265.6312,265.7397.40.105
41
4284487MPkng:Ctn Box M6003,733.003,828.73-0.3553,828.713,829.0897.40.369
43
4484842Pkng:Ctn RT 6005,033.005,162.010.2785,162.045,161.7497.5-0.308
45
4684907Pkng:Ctn AG600888910.7720.206910.767910.56697.5-0.201
47
4884908Pkng:Ctn AS600806826.6660.127826.665826.53997.5-0.126
49
5085844Pkng:Ctn SL 60013,957.0014,314.86301.01814,314.8614,013.8499.5-301.018
51
5286530Pkng:Ctn CF "F" Blank6001,860.001,907.68-236.7521,907.692,144.4486.7236.747
53
5486531Pkng:Ctn CF "G" Blank6001,014.001,040.00-675.1781,040.001,715.1859.1675.181
55
5686532Pkng:Ctn CF "B" Blank600702720.0020.001719.998720.00197.40.003
57
5887896Pkng:Ctn LL6001,800.001,846.150.4581,846.151,845.6997.5-0.461
59
6088325Pkng:Ctn Tray60021,681.0022,236.98436.3521,066.6421,800.6399.4733.993
61
6288324APkng:Ctn Tray A6003,733.003,828.73-0.3553,828.713,829.0897.40.369
63
64
65SF9108A 05 7:21:196/29/20 PAGE1
66PRODUCTYIELD PRODUCTIONREPORT
67Seq. By Component
68Tran datefrom: 6/22/20 To: 6/28/20ClassFrom: 600 To: 805 Product From:To:
69================================================================================================================================================================
70AdjustmentVariance
71ProductDescriptionClassQty-ProducedBackFlush-QtyCycle-CountBase-QtyActual-UsageYield %Quantity
72================================================================================================================================================================
73
7488325BPkng:Ctn Tray B6003,733.003,828.73-0.3553,828.713,829.0897.40.369
75
7688326CPkng:Ctn Tray C6003,733.003,828.73-0.3553,828.713,829.0897.40.369
77
7888327DPkng:Ctn Tray D6003,733.003,828.73-0.3553,828.713,829.0897.40.369
79
8088328EPkng:Ctn Tray E6003,733.003,828.73-0.3553,828.713,829.0897.40.369
81
8288329FPkng:Ctn Tray F6003,733.003,828.73-0.3553,828.713,829.0897.40.369
83
8488330GPkng:Ctn Tray G6003,733.003,828.73-0.3553,828.713,829.0897.40.369
85
8688331HPkng:Ctn Tray H6003,733.003,828.73-0.3553,828.713,829.0897.40.369
87
8888332JPkng:Ctn Tray J6003,733.003,828.73-0.3553,828.713,829.0897.40.369
89
9088333KPkng:Ctn Tray K6003,733.003,828.73-0.3553,828.713,829.0897.40.369
91
9288334LPkng:Ctn Tray L6003,733.003,828.73-0.3553,828.713,829.0897.40.369
93
9488335MPkng:Ctn Tray M6003,733.003,828.73-0.3553,828.713,829.0897.40.369
95
96
Sheet1
 
Upvote 0
With Power Query, a few quick Mouse clicks and the following is the result.
Book20
ABCDEFGHIJ
1ProductDescriptionClassQty-ProducedBackFlush-QtyCycle-CountBase-QtyActual-UsageYield %Quantity
281280Pkng:Ctn CF "F" 6 5/16"6007640378361.698719.0578361.94977642.64898.4-719.301
381281Pkng:Ctn CF "G" 5"6002879829536.451920.80129536.3727615.649104.2-1920.721
482486Pkng:Ctn CCF "B" light60059796132.3261084.7346132.2975047.592118.4-1084.705
582489Pkng:Ctn CCF "I" light60014611498.46-0.1811498.4591498.64197.40.182
682494Pkng:Ctn CCF "E" light600718736.406-0.233736.407736.63997.40.232
784476APkng:Ctn Box A60035473637.953-0.1263637.9433638.07997.40.136
884477BPkng:Ctn Box B60061756333.329-0.496333.3226333.81997.40.497
984478CPkng:Ctn Box C6001195912265.698-0.03412265.62712265.73297.40.105
1084479DPkng:Ctn Box D60037333828.725-0.3553828.7113829.0897.40.369
1184480EPkng:Ctn Box E60035473637.953-0.1263637.9433638.07997.40.136
1284481FPkng:Ctn Box F60061756333.329-0.496333.3226333.81997.40.497
1384482GPkng:Ctn Box G6001195912265.698-0.03412265.62712265.73297.40.105
1484483HPkng:Ctn Box H60037333828.725-0.3553828.7113829.0897.40.369
1584484JPkng:Ctn Box J60035473637.953-0.1263637.9433638.07997.40.136
1684485KPkng:Ctn Box K60061756333.329-0.496333.3226333.81997.40.497
1784486LPkng:Ctn Box L6001195912265.698-0.03412265.62712265.73297.40.105
1884487MPkng:Ctn Box M60037333828.725-0.3553828.7113829.0897.40.369
1984842Pkng:Ctn RT 60050335162.0130.2785162.0435161.73597.5-0.308
2084907Pkng:Ctn AG600888910.7720.206910.767910.56697.5-0.201
2184908Pkng:Ctn AS600806826.6660.127826.665826.53997.5-0.126
2285844Pkng:Ctn SL 6001395714314.856301.01814314.85614013.83899.5-301.018
2386530Pkng:Ctn CF "F" Blank60018601907.683-236.7521907.6882144.43586.7236.747
2486531Pkng:Ctn CF "G" Blank60010141040.001-675.1781039.9981715.17959.1675.181
2586532Pkng:Ctn CF "B" Blank600702720.0020.001719.998720.00197.40.003
2687896Pkng:Ctn LL60018001846.1480.4581846.1511845.6997.5-0.461
2788325Pkng:Ctn Tray6002168122236.98436.3521066.63721800.6399.4733.993
2888324APkng:Ctn Tray A60037333828.725-0.3553828.7113829.0897.40.369
2988325BPkng:Ctn Tray B60037333828.725-0.3553828.7113829.0897.40.369
3088326CPkng:Ctn Tray C60037333828.725-0.3553828.7113829.0897.40.369
3188327DPkng:Ctn Tray D60037333828.725-0.3553828.7113829.0897.40.369
3288328EPkng:Ctn Tray E60037333828.725-0.3553828.7113829.0897.40.369
3388329FPkng:Ctn Tray F60037333828.725-0.3553828.7113829.0897.40.369
3488330GPkng:Ctn Tray G60037333828.725-0.3553828.7113829.0897.40.369
3588331HPkng:Ctn Tray H60037333828.725-0.3553828.7113829.0897.40.369
3688332JPkng:Ctn Tray J60037333828.725-0.3553828.7113829.0897.40.369
3788333KPkng:Ctn Tray K60037333828.725-0.3553828.7113829.0897.40.369
3888334LPkng:Ctn Tray L60037333828.725-0.3553828.7113829.0897.40.369
3988335MPkng:Ctn Tray M60037333828.725-0.3553828.7113829.0897.40.369
Sheet2


Here is the Mcode that resulted from the few Mouse Click steps.

Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> null and [Column2] <> "===============================") and ([Column1] <> "Tran date")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type any}, {"Description", type text}, {"Class", type any}, {"Qty-Produced", type any}, {"BackFlush-Qty", type any}, {"Cycle-Count", type any}, {"Base-Qty", type any}, {"Actual-Usage", type any}, {"Yield %", type any}, {"Quantity", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Product] <> "Product"))
in
    #"Filtered Rows1"
 
Upvote 0
Solution
Thank you. I will give this a try. Really appreciate the assist.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
139
MichaelMorris
M
P
  • Question
Macro to import multile xml into xlsm with Map data properties or schema
Replies
3
Views
50
Kerryx
K
B
  • Question
Number format with 6 digits from import without leading zeros
Replies
4
Views
487
bkeebaugh
B
K
  • Solved
VBA to copy column/data from two .csv files to one combined file.
Replies
6
Views
477
kbilly70
K
L
  • Question
How to Determine If Donating to One Cause Led to Long-Term Donating?
Replies
4
Views
179
LPax11
L

Forum statistics

Threads
1,214,382
Messages
6,119,194
Members
448,874
Latest member
Lancelots

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