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]

Thx a bunch buddy I'll try it soon! Will let you know

 

[sandy666]

Welcome

Will let you know

will be nice

 

[MP_7]

Heh sandy I know I'm being a pain in the *** but I'm getting an error

So I paste it into advanced editor, change my source


and then get an error;
Expression.Error: The import Transform File from txt matches no exports. Did you miss a module reference?
Expression.Error: The name 'Sample File' wasn't recognized. Make sure it's spelled correctly.

 

[sandy666]

so do From Folder, Edit, remove all Changed Type, then you should see

then do a monkey job with the next steps

btw. Power Query is Case Sensitive so if the name of files are different you'll get error
you've more than 5587 rows so it should be changed (this is hardcoded in the code)
each gear on the right side will show you how this step was done
warning: you will get a problem with Split step

 

[sandy666]

Question: how many .txt files do you have?

 

[MP_7]

Between 800 and 900

 

[sandy666]

it's possible to rar them and share to me?

 

[MP_7]

Sure thing! Here you go

1 file - 16.93 MB

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

fromsmash.com

 

[sandy666]

gimmie a few...

 

[sandy666]

I have a business meeting so it will take some time, sorry for delay