Import lots of txt files into separate rows in one worksheet

[MP_7]

Hello,

I'm not good at VBA and neither at power gueries, but I'm trying with the latter, and I'm having some troubles.
What I have is a lot of txt files and the data in those files is just row after row, part of it below
2
3
144
2.50
73
2.50
OR4_GL02_300_3K_2A
etc...

I just need what each row contains into separate columns all in one row

for example:
file 1
A
B
C

file 2
D
E
F

Result:

A	B	C
D	E	F

I defined in the query to import only the first 40 rows but after I load it literally imports those 40 values into one row. I don't know how to import the rest of the files?

Thx.

 

[MP_7]

Unofrtunately no
There has to be only one row for each file, in your example there are multiple rows for one file
Let me just make an example of what the first 2 rows should look like

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Custom.1	Custom.2	Custom.3	Custom.4	Custom.5	Custom.6	Custom.7	Custom.8	Custom.9	Custom.10	Custom.11	Custom.12	Custom.13	Custom.14	Custom.15	Custom.16	Custom.17	Custom.18	Custom.19	Custom.20	Custom.21	Custom.22	Custom.23	Custom.24	Custom.25	Custom.26	Custom.27	Custom.28	Custom.29	Custom.30	Custom.31	Custom.32
2	GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_300_3K_2A	1030.18	3000	70	9.75
3	GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_350_3K_2A	1197.62	3000	70	11.46
Sheet1

 

[sandy666]

there is more labels or GMR Enlights only?
i'll try but in my spare time

 

[MP_7]

GMR Enlights is the first value in every txt file.
No probs bud, I appreciate every second you can spare, thank you very much!

 

[sandy666]

I know it's the first label but is there more labels?
here is just for this single label

Custom	List.1	List.2	List.3	List.4	List.5	List.6	List.7	List.8	List.9	List.10	List.11	List.12	List.13	List.14	List.15	List.16	List.17	List.18	List.19	List.20	List.21	List.22	List.23	List.24	List.25	List.26	List.27	List.28	List.29	List.30	List.31	List.32
GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A	OR4_GL02_300_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_300_3K_2A	1030.18	3000	70	9.75	0.21576
GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A	OR4_GL02_350_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_350_3K_2A	1197.62	3000	70	11.46	0.21576
GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_400_3K_2A	OR4_GL02_400_3K_2A	OR4_GL02_400_3K_2A	OR4_GL02_400_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_400_3K_2A	1362.18	3000	70	12.61	0.21576
GMR Enlights	2	3	144	2.50	73	2.50	OR4_GL02_450_3K_2A	OR4_GL02_450_3K_2A	OR4_GL02_450_3K_2A	OR4_GL02_450_3K_2A.LDT	05-09-2019 / Agilux	400	200	60	150	150	1	1	1	1	99.81	100.00	1.00	0.00	1	1	OR4_GL02_450_3K_2A	1523.95	3000	70	13.33	0.21576

most of the values in the columns are the same but sometimes they differ
and as you said number of columns are limited to 32

 

[MP_7]

If I understand what you're asking, no it's the only label I mean it's 1 per file
Holy crap, I think you got it, how the hell did you do that?

 

[sandy666]

refresh thread and see if this is what you want because total number of columns is 5587

 

[MP_7]

Yes I know that's the problem, everything after the 32nd value is not needed, that's why we talked about 32 columns

 

[sandy666]

You want M only or excel file?
btw. these are the first 32 columns

 

[MP_7]

Not sure what you mean with M, any way you want as long as it lets me understand how you did it

 

[sandy666]

M is the name of Power Query language
so I'll give you M
change source path to your folder with txt files

let
    Source = Folder.Files("D:\test\mp_7\txt"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from txt", each #"Transform File from txt"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from txt"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from txt", Table.ColumnNames(#"Transform File from txt"(#"Sample File"))),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Table Column1", "Custom", each if Text.Contains([Column1], "GMR Enlights") then [Column1] else null),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Source.Name", "Custom"}, {{"Count", each _, type table}, {"CR", each Table.RowCount(_), type number}}),
    #"Filled Down" = Table.FillDown(#"Grouped Rows",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([CR] = 5587)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "List", each [Count][Column1]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3", "List.4", "List.5", "List.6", "List.7", "List.8", "List.9", "List.10", "List.11", "List.12", "List.13", "List.14", "List.15", "List.16", "List.17", "List.18", "List.19", "List.20", "List.21", "List.22", "List.23", "List.24", "List.25", "List.26", "List.27", "List.28", "List.29", "List.30", "List.31", "List.32"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Source.Name", "CR"})
in
    #"Removed Columns"