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.

 

[sandy666]

you didn't show your M so hard to say something about it
try From Folder option with #(lf) as a delimiter
also try to use XL2BB to show data from Excel in the post

 

[MP_7]

Hey sandy!

yeah sorry about that, indeed it's hard to understand my steps. I made a video tracing my steps so maybe you will understand what I'm doing wrong and missing...

 

[sandy666]

so you got what you wanted, why not?

 

[MP_7]

No, not exactly, that's just one row from one file, I need let's say the first 40 rows of each file transposed into one row

 

[sandy666]

could you post link to shared, zipped file with few txt files?

 

[MP_7]

sure, no probs, I put only 4 files in the rar

1 file - 79.03 KB

File sent via Smash, a fast, secure and responsible service for sending (super) large files.

fromsmash.com

as you will see and as showed in the video each file has +5000 lines, and I need a little less than the first 40 of each file
so basically if I have 500 files, as a result I need one workbook with almost 40 columns and 500 rows

 

[sandy666]

do you know how many columns should be there?

 

[MP_7]

I think the first 32 lines of each file is what I need, everything after that is not needed, so I would say 32 columns

 

[sandy666]

something like this?
of course there is much more rows

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
GMR Enlights	2	3	144	2.5	73	2.5	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	1	0	1	1	OR4_GL02_300_3K_2A	1030.18	3000	70
9.75	0.21576	0.29589	0.36836	0.44494	0.50761	0.60761	0.67743	0.72858	0.79533	0.83733	0.00	2.5	5	7.5	10	12.50	15	17.5	20	22.5	25.00	27.5	30	32.5	35	37.5	40.00	42.50	45.00	47.50	50.00
52.50	55	57.5	60	62.5	65	67.5	70.00	72.50	75.00	77.50	80.00	82.5	85	87.5	90	92.50	95	97.5	100	102.5	105.00	107.5	110	112.5	115	117.5	120.00	122.50	125.00	127.50	130.00
132.50	135	137.5	140	142.5	145	147.5	150.00	152.50	155.00	157.50	160.00	162.5	165	167.5	170	172.50	175	177.5	180	182.5	185.00	187.5	190	192.5	195	197.5	200.00	202.50	205.00	207.50	210.00
212.50	215	217.5	220	222.5	225	227.5	230.00	232.50	235.00	237.50	240.00	242.5	245	247.5	250	252.50	255	257.5	260	262.5	265.00	267.5	270	272.5	275	277.5	280.00	282.50	285.00	287.50	290.00
292.50	295	297.5	300	302.5	305	307.5	310.00	312.50	315.00	317.50	320.00	322.5	325	327.5	330	332.50	335	337.5	340	342.5	345.00	347.5	350	352.5	355	357.5	0.00	2.50	5.00	7.50	10.00
12.50	15	17.5	20	22.5	25	27.5	30.00	32.50	35.00	37.50	40.00	42.5	45	47.5	50	52.50	55	57.5	60	62.5	65.00	67.5	70	72.5	75	77.5	80.00	82.50	85.00	87.50	90.00
92.50	95	97.5	100	102.5	105	107.5	110.00	112.50	115.00	117.50	120.00	122.5	125	127.5	130	132.50	135	137.5	140	142.5	145.00	147.5	150	152.5	155	157.5	160.00	162.50	165.00	167.50	170.00
172.50	175	177.5	180	297.94499	260.307	202.05	162.69700	142.26600	124.41000	110.36400	100.62400	94.3753	91.2877	88.6079	81.8345	76.04440	72.2188	72.9625	74.713	68.566	65.18480	68.8711	65.6826	64.6183	62.9532	61.6651	60.08480	55.64860	30.80700	7.07467	3.42107

I really don't know how it should be
You are the one who knows