Import lots of txt files into separate rows in one worksheet

MP_7

New Member
Joined
Feb 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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:
ABC
DEF

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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

New Member
Joined
Feb 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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...

 

MP_7

New Member
Joined
Feb 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
could you post link to shared, zipped file with few txt files?
 

MP_7

New Member
Joined
Feb 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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
 

MP_7

New Member
Joined
Feb 23, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like this?
of course there is much more rows
Custom.1Custom.2Custom.3Custom.4Custom.5Custom.6Custom.7Custom.8Custom.9Custom.10Custom.11Custom.12Custom.13Custom.14Custom.15Custom.16Custom.17Custom.18Custom.19Custom.20Custom.21Custom.22Custom.23Custom.24Custom.25Custom.26Custom.27Custom.28Custom.29Custom.30Custom.31Custom.32
GMR Enlights231442.5732.5OR4_GL02_300_3K_2AOR4_GL02_300_3K_2AOR4_GL02_300_3K_2AOR4_GL02_300_3K_2A.LDT05-09-2019 / Agilux40020060150150111199.811001011OR4_GL02_300_3K_2A1030.18300070
9.750.215760.295890.368360.444940.507610.607610.677430.728580.795330.837330.002.557.51012.501517.52022.525.0027.53032.53537.540.0042.5045.0047.5050.00
52.505557.56062.56567.570.0072.5075.0077.5080.0082.58587.59092.509597.5100102.5105.00107.5110112.5115117.5120.00122.50125.00127.50130.00
132.50135137.5140142.5145147.5150.00152.50155.00157.50160.00162.5165167.5170172.50175177.5180182.5185.00187.5190192.5195197.5200.00202.50205.00207.50210.00
212.50215217.5220222.5225227.5230.00232.50235.00237.50240.00242.5245247.5250252.50255257.5260262.5265.00267.5270272.5275277.5280.00282.50285.00287.50290.00
292.50295297.5300302.5305307.5310.00312.50315.00317.50320.00322.5325327.5330332.50335337.5340342.5345.00347.5350352.5355357.50.002.505.007.5010.00
12.501517.52022.52527.530.0032.5035.0037.5040.0042.54547.55052.505557.56062.565.0067.57072.57577.580.0082.5085.0087.5090.00
92.509597.5100102.5105107.5110.00112.50115.00117.50120.00122.5125127.5130132.50135137.5140142.5145.00147.5150152.5155157.5160.00162.50165.00167.50170.00
172.50175177.5180297.94499260.307202.05162.69700142.26600124.41000110.36400100.6240094.375391.287788.607981.834576.0444072.218872.962574.71368.56665.1848068.871165.682664.618362.953261.665160.0848055.6486030.807007.074673.42107

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

Watch MrExcel Video

Forum statistics

Threads
1,122,498
Messages
5,596,509
Members
414,073
Latest member
Contilly

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
Top