How to import multiple text files into excel

No video

How to import multiple text files into excel

Рет қаралды 176,650

7 жыл бұрын

How to put lots of text files into a single excel spreadsheet all in one go.
Sometimes you have data in lots of separate text files and you want to be able to put that data together in an excel document without having to copy and paste each text file individually. You can do this using Power Query. This is a really useful feature in Excel 2016 which lets you organize large amounts of data. This only works if all your text files have the same columns. This works with .txt and .csv files.
---------------------------------------------------------------------------------
#exceltutorial #powerquery #exceltipsandtricks
SUBSCRIBE: goo.gl/c46YPs
VIDEO INFO: These data are related to my PhD research
---------------------------------------------------------------------------------
IF YOU LIKE THIS VIDEO YOU MAY ALSO LIKE:
- How to combine tables side by side in excel • How to combine tables ...
- How to combine tables with different columns in excel? • How to combine tables ...
- How to rename multiple files in Windows • How to rename multiple...

Пікірлер: 67

@karinaadcock 6 жыл бұрын

Query is only included in Excel 2016. In Excel 2010 and Excel 2013 it is an add-in called power query. Watch this other video for more information. kzfaq.info/get/bejne/jMuDY8mZnd65hnU.html

@Mileisha_Lyann 2 жыл бұрын

How can I separate each of those text files ( or .asc) to a separate column? In my case they all have the same X axis values but different Y values . HELP please . I have 120 files, cannot do them one by one!

@mstee5346 2 жыл бұрын

Beautifully orchestrated, this is what data analyst do make data user friendly for analysis !

@Rennautobrumm 5 жыл бұрын

Awesome! Just told my colleague: I thought this would take all afternoon, now its done in 5 minutes! - the reply - thats what she said. :) anyway, thanks a lot!

@coltenneath2404 4 жыл бұрын

Oh my goodness this literally saved me HOURS! Thank you, thank you, thank you!!!!!

@jaejinkim5773 5 жыл бұрын

Thank you for teaching the easy way to combine TEXT files.

@samrame 22 сағат бұрын

very helpful!!!!! Saved me a ton of my time.

@robthue7233 4 жыл бұрын

Excellent video! Clear! Concise! Perfect instruction! Very helpful! Thank you so much!

@davidtaglieri5882 2 жыл бұрын

Thank you! Just made my thesis research analysis much much easier.

@hakmatullahtookhi8313 4 жыл бұрын

Thank you so much for sharing the valuable teachings & information

@aikendrum7301 4 жыл бұрын

Thanks !!! needed this in a hurry !! In Office 365 Excel 2019 it's the 'GetData' option under the 'Data' menu :)

@farisal-amer7635 Жыл бұрын

This is an excellent video and well explained. Thank you very much.

@lozfitz 3 жыл бұрын

Thank you, sounds like you were on the verge of tears!

@hannahterry1676 5 жыл бұрын

Thank you very much Karina! This saved me from having to do a ridiculous amount of copying and pasting LOL!

@armadooo8294 Жыл бұрын

Thank you. This video is helpful.

@technodex8678 Жыл бұрын

Well explained ty .

@stephenstephen3279 2 жыл бұрын

Just what I needed. Thank you so much.

@ayhobbyist 2 жыл бұрын

you literary saved me with this video 👍

@MatheusCorreiaGomes 5 жыл бұрын

Excellent, saved me A LOT of time. Thank you very much!

@TrektoTomorrow Жыл бұрын

Thank you! this was really helpful

@brianklebig8803 2 жыл бұрын

You are my new hero.

@laurynasstonys3235 2 жыл бұрын

thank you a ton!

@lorenzohuamanchay7390 6 жыл бұрын

Magnífica presentación Karina. Gracias.

@divinorodriguesdasilva5048 8 ай бұрын

Fantastic!

@hardiksur8423 3 жыл бұрын

Really Helpful Video

@mofoho2004 5 жыл бұрын

Genius! Massive time saver - thank you!

@Seftehandle 3 жыл бұрын

phenomenal saved me hours

@lanternofthegreen 5 жыл бұрын

Thanks a lot. You saved me from days of work.

@unchurched 3 жыл бұрын

Nicely done. A perfect solution for my task.

@unchurched 3 жыл бұрын

And clearly explained, I meant to add.

@talharehman9458 Жыл бұрын

Thanks!

@thabokgotsa3174 6 жыл бұрын

hi there... thanks a lot for this.. how do I get the query to only pull through data after a certain line and before another in the text file

@Mileisha_Lyann 2 жыл бұрын

Does anyone know how to do this with a Macbook? The settings seem to be different, as the option of "Data from folder" does not appear. I have to import 120 text files one by one...

@dharyamalhotra2701 4 жыл бұрын

YOU HAVE AWESOME VOICE...

@cristhiantorres7084 5 жыл бұрын

Hi Karina, your video help me to move to the next step, but I have an issue with the "Combine Files" step, when I start to combine my files, the query requires open an "sample" file it can be opened successfully but later I don't know how I can combine the rest of the files to make the load. I appreciate your comments

@karinaadcock 5 жыл бұрын

When you have loaded the files and you have only the first file showing is there a vertical bar down the left hand side called Queries? Click on the arrow to expand that Queries bar and then you should see two queries. One is called sample query and contains only the first file. The second one is called other queries and should contain all your files combined together.

@Craqerzrus 7 жыл бұрын

U just saved me 5 hours thank you so much

@noobsnuper8356 3 жыл бұрын

i found your video very informative but can you tell me how many text files can be imported into one excel sheet

@saathvik2005 4 жыл бұрын

This really helpful. Thanks.

@danielpais 3 жыл бұрын

Amazing! Thank you so much

@tonyxatnaa1017 4 жыл бұрын

content click, this is , how to solve ? DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: Unexpected XML declaration. The XML declaration must be the first node in the document, and no white space characters are allowed to appear before it. Line 1, position 1705.) Details: Binary

@Mileisha_Lyann 2 жыл бұрын

@charlottehyland523 4 жыл бұрын

Is it possible to only get one row from the documents imported?

@estyanike 5 жыл бұрын

Thanks. But this still seem difficult. Someone should help, I need to convert 84 text files(Tab delimited) into excel sheets and still retain each file column spacing. This procedure takes alot of time converting manually to excel. Can someone help make this seem-less for me? Thanks

@preethikaralapati5774 7 жыл бұрын

Hi, I have done uploading text files into Excel but unable convert all the files into excel. can you please help me on this

@logesh1987 5 жыл бұрын

Hope, similar procedure for multiple .dat files

@akasshvijayaddittan7647 7 жыл бұрын

Hi, I have done uploading the text file but the Excel pop up a new query editor tab whereas in the video it just shows a small dialogue box (0:52 sec). Can you pls help me on this

@cactushong4931 5 жыл бұрын

Great video! Thanks :)

@alyismael91 5 жыл бұрын

Absolutely beautiful!!

@theDfLopes 5 жыл бұрын

thanks, save me a lot of time.

@natalivalim2653 6 жыл бұрын

Thank you very much!!!

@jeansalazar7812 5 жыл бұрын

Thanks a lot!

@twshengting 4 жыл бұрын

does this work if your data is in rows and not columns?

@Russeellee 4 жыл бұрын

How to import the txt files into columns? I have numerous files with two columns and tens of thousands of rows, all formatted similarly. I can edit the query and transpose so the "binary" cells are the top row, but I can't seem to find a way to expand all of the columns. Or is there another way to do it? Thanks,

@karinaadcock 4 жыл бұрын

could you rearrange the data using a pivot table?

@hikariz2931 3 жыл бұрын

How can I import the multiple line into only one cell?

@rtlinsn5085 3 жыл бұрын

amazing accent

@psagar 5 жыл бұрын

you fucking legend. This saved my life

@thabokgotsa3174 6 жыл бұрын

i basically want to keep the data separated by the source tx file name in the worksheet and be able to delete some rows and keep others to work with...

@karinaadcock 6 жыл бұрын

You could just use column filters to remove the rows you don't want. Or you could click on the small icon of a table to the left of the column headings and then select 'remove alternate rows' and then select the number of rows you want to remove and the number of rows you want to keep. Or I think you can go to 'view' and then 'advanced editor' and then change the code so the query only takes the rows you want from the text files but I don't know how to do this.

@baverabhargav6136 6 жыл бұрын

thank you :)

@krishna20100 6 жыл бұрын

Hi Dear doing a wonderful full by transferring your valuable knowledge to us i am requesting you i have querie for automation reports can please help me

@karinaadcock 6 жыл бұрын

what do you want help with?

@Craqerzrus 7 жыл бұрын

If I only knew how to not get it to be in numerical order it may save even more

@karinaadcock 7 жыл бұрын

You could try renaming the text files so there is a number in front of the name of each file. You could try inserting a column and then numbering the rows in the order that you want them to be in. You could try creating a Custom List to sort the data. kzfaq.info/get/bejne/adh_i9ury7jdeZs.html

@Craqerzrus 6 жыл бұрын

Karina Adcock ty