How to Easily Import External Data into Excel & Import Data from the Web
HTML-код
- Опубликовано: 23 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover how to optimize your data handling in Excel using two powerful features: Text to Columns and External Data Import. Learn to efficiently manage complex data sets, overcome common issues like varying decimal and thousand separators, and even automate data refresh from web sources.
This video is a practical guide for anyone dealing with data exports from systems like SAP or Oracle, aiming to streamline their Excel workflow.
If you'd like to learn the newest version of this feature check out Power Query: • Master Excel Web Scrap...
✨ Excel Features Explored:
▪️ Text to Columns: A step-by-step guide to split a single column of data into multiple columns efficiently, ideal for separating names, addresses, or any combined data.
▪️ Importing External Data (CSV/TXT files): Learn how to import structured data files into Excel, controlling decimal and thousand separators, and maintaining data integrity.
▪️ Auto-Refresh and Connections: Understand how to set automatic refresh intervals for your imported data, ensuring your Excel sheets always reflect the most current data from external sources.
▪️ Web Query for Online Data: An introduction to importing data directly from web sources into Excel, with options for setting automatic updates.
00:00 How To Properly Import Data to Excel
01:16 How To Use Text to Columns in Excel
03:27 How To Import Data from CSV / TXT files to Excel
09:52 How To Import Data from The Web to Excel
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Discover the latest enhancements to import data into Excel here: ruclips.net/video/EOhSODFLqic/видео.html
Thank you,your teachings are great for New Beginners in Excel
You have identified an oft-underused aspect of Excel. Good job.
This is all great stuff. You can connect to databases half way around the world and pull stuff into your spreadsheet. You can even do some macros that will rearrange the columns as needed and do calculations for you. Really powerful stuff.
I have interview on Thursday next week and this vlog has helped me enormously with the assessment I will have to complete. Thank you x.
You're welcome! I'm happy to hear the content here helps with the assessment. All the best for your interview!
I must say I am impressed. I started working in excel two months ago, I thought I was a five on a scale from 1 to 10 but now I think I am a 2. The amount of knowledge that you have is astonishing. And the fantasy too. You have to have an immense knowledge of excel to use formulas as you do. How long have you been using it?
Ps. After 13 hours of working it's 2:10 am and I am watching all of your videos. Thanks for sharing your knowledge.
Wonderful. Usually we do only text to column; but the web import and other import is not something we use on recurring basis. This is therefore absolutely important video. Thanks for throwing light on the subject.
Agree: it's not used that often. Glad you like it Sachin.
Thanks Leila. This is excellent and will be very helpful for me! Keep up the good work!
I'm glad you find this useful :)
You are one of the best excel expert. You pick good topics and explain it very well. I'm growing fond of excel more and more, thanks to you!
Thank you for the kind comment. I'm glad you find the topics useful.
I thoroughly enjoy your excel training videos and appreciate that you began adding the excel file and step by step written instructions to refer to as a guide. I have a request. Many companies use quick books; however, the reporting is not as visually appealing. Is there a way for excel to connect directly to QB desktop (.QBB or .QBW source files) or online files in orders to make financial reporting and visualization to dynamic to easily prepare monthly financial reporting package?
I watched your videos similar to this one, ability to connect to websites, and other excel files. But haven’t found anything that directly addresses my need. Any insight, instructional video or training would be greatly appreciated.
I discovered you today and you answered my question straight away. Am about to test the data and My Word amd I glad I found you!
Hello ma'am can you show video web scraping and data scraping full video on RUclips and use in which software easy to use
You are such a great tutor, Thanks !!
You're welcome Ester.
Madam, I improved my self in excel because you’re videos. Thank you
I'm VERY HAPPY to hear that! Many thanks for your comment & support.
Thanks for the video. I need help on how to record the pulled data, that is periodically refreshed, on another sheet. For example, if I need to track a record of stock price movement automatically, how would I do that? If you already have a video on this, then please provide the link.
Thank you for this, really helpful
This what I need. ..
Thanks
Do you have a video to show how to insert number of column/columns according to the specified number. e.g input data = 5, insert 5 columns automatically. If not, will you do one please
Very useful data import tips for Excel
Good job. Not exactly what I was looking for but you were very clear and I like the hot tip. I will watch for other videos as I continue to develop my process system.
Thanks - I hope you also find what you were looking for.
MANY THANKS FOR ALL THE THINGS I'VE LEARNED FROM YOU
Thanks in a million!
Hello Thank you for the awesome video. could i use this to copy stock data exported from a point of sale from one csv to another template for import to a new csv? Thanx
Note that the "From Web" Import is only available on Windows, not Mac. Great video as always Leila.
Thanks this helped a lot...
Excellent Video, Thank you!
You're very welcome Esan.
Thank you thank you!! You are making me smart!
You're very welcome. I'm glad to hear that :)
I understood it very well & thank you!!!!!!!
You're welcome Narth.
I wanted to import "column to comma" link into excel. Tell me if it is possible to edit that imported sheet and put my column wise data to convert into comma data?
Can I ask if you can use the text to column or even format data extracted from a website without risking the data running awry whenever you refresh the extracted dataset?
Quite useful, Text to column does have many features as you have shown here and in other posts. Importing to excel was also handy but making changes in excel and re-export to system is often unexplored. Good one again
Glad you like it.
I have imported custom ribbons on my Excel having Macros. I would like to change or modify the references to the ribbon button. Could you advise how can I change?
Can you do a video on importing data from a website you have to sign in before you can view the data?
Great job. thanks.
You're welcome Mahmud.
Thanks a lot
Will this work to import all Utube video url data from monthly filtered history to an EXCEL file ??
Appreciate yr confirmation
Thx
I like the tutorial!!
saved me few hours. Kudos !
That's great! Thanks for sharing.
excellent video, thanks alot for these easy to understand videos.
I'm happy to hear this Simoiya! Many thanks for your support.
excellent video perfect and thank u
You're very welcome.
Hello, I have a question.
If we use data from web, & checkmark the box that says refresh data. Considering the example in the video will the stock market values in +/- get updated automatically ?
Thank you ma’am great content
Do you have a video on how to import SharePoint list columns into a word doc stored in OneDrive for business
Hey.. i am learning from you via UDEMY also.
hi, would be possible to do the same with google sheets, I have data in my laptop every time I add more data to the workbook, I would like the google sheet get updated as well, vice versa.???
Leila, u r such a darling! You just landed me to my exam room with a confident smile. Excellent job!!!
I'm very happy to hear that! Good luck on your exam.
Hello Ma'am..
Is it possible to download excel file from web automatically and save it in a specific folder via macro or power query or with any other features?????
Very useful
Thank you for the video.
My pleasure.
Respected Madam,
Am fan of your videos and new idea thoughts… Am impressed valuable lessons…
Really I tired lot and output was no result.. First off all am beginner of learner in excel...
I need your help/idea in excel…. In work sheet1 I want two (2) things
1. Search box and display the output there from work sheet2/3/4 are etc.
2. Automatically transfer data and save the data from work sheet1 into work sheet2/3/4 are etc..
Whether it is possible to do like this I don’t know!! Am regularly watch your videos.
I hope you and needful to me and who are following your's students of richest ideas and thoughts from your videos...
Your slowly step by step briefing method helps me lot. Fast showing is just shows your skills but not understand many people. thanks for helping us...keep going with the slow method.
Thank Manash.
Can u pls send me your mail address... if any problem arise i send you. My mail id is smartmanash@gmail.com
Hello, I have a VLOOKUP function that sources a separate file (Workbook), which gets rolled/saved daily with a NEW Date. I am using IDIRECT to change the Daily Roll date forward, in my VLOOKUP, daily. The limitation of this technique, requires to have both Workbooks open, for VLOOKUP to function. Is there a way to use IMPORT DATA functionality, for FileNames that change Daily? Effectively, I am attempting to eliminate the need to open multiple workbooks, to perform the VLOOKUP. I want to perform the VLOOKUP from tables, that update, when the data changes. Is there an effective way to direct IMPORT DATA functionality to change the source, on a Daily Basis?
Hey, I find this very helpful & you were very calm throughout the video and yeah, subscribed to your channel. Love
Thanks for subbing!
Thanks leila.
Hi ..hope u doing well..if I want to import data from a file whose extension is .pc how will I do it.. cos excel is not giving any such option to import data from these kind of files..
very helpfully this video
Glad to hear that :)
You are best
Thanks.
Hi there, is there any way I can import Excel spreadsheet into Xplan document vault?
1:40 should be "highlight the cells" not 'highlight the column'. Great video.
Thanks Leila
I love that
Glad you liked it.
Thanks for the upload - it´s a nice tutorial. I had some challenges when importing stock prices to Excel via an external data source (an American website) and the price was quoted with a comma as a thousand separator and a dot as the decimal and my Excel had a European setup. Like you described in the video I just used used "Text to columns" and stated under "advanced" that I wanted the dot as a thousand separator and the comma as the decimal. Problem solved 😉
I'm glad the tutorial was helpful Bryan. I hope the stock market is treating you well :)
@@LeilaGharani is there any excel tool to get automate specific data from gmail to spreadsheet . Like part from the mail........ order number, item number, price , feedback etc. Whenever get mail, automatic data pull in spreadsheet.
Hi Leila
I’m using Office 365. But when I tried to import the currencies web data (shown at the 10mins 33 mark in this video) the ‘navigator’ window in Excel wouldn’t let me highlight or copy the data. Do you know why that may be?
Thanks in advance.
In Excel any function to see data import source see and all activities done in Excel to see
How do I separate 1 column address into Street column, City Column, ZIP, STATE. One time I received a lot data more than 200 rows and the whole address was put in 1 column, I could leave like that in a emergency, but I need to split the address column mentioned above. I spend 2 hours to figure out or clean the address, what a mess. Example: 201 17th Street, apt 15, Anaheim, CA. i had to multiply the colums and search and replace. When it's between commas its easier to split, when street and city has no commas, difficult to clean by searching and replacing.
Hello. I enjoyed your lesson.
I have a little problem. After importing the file and saving as csv file on my excel. The numbers I changed didn’t reflect on my work pad
سلام خواهر عزیزم لیلا جان
شما واقعا افتخار ما هستید .
informative as always....
Excel 2016 for some reason doesn't provide a way of importing data from mysql ..most of the other databases are offered like (SQL server and oracle) is there a way of importing data to mysql by establishing a connection to the mysql database ?
Have you tried using an ODBC? Many years ago I needed to import data from several different systems. A few of them were DOS, one was Oracle running on Unix and one was a COBOL and I think it was running on a Wang computer. I used ODBC and was able to adjust it for each different computer and it would import the data first thing when I opened the Excel file. True, none of them were MySQL but I have worked with MySQL and don't see shy the ODBC wouldn't work on it.
In Excel, sheet1 range B1:B200 contains many website links of zip files, download the zip file of all those links together and keep them in the same folder using vba, make a video for that.... Thanks
Thanks for the hot chili pepper. Cool!. (dichotomy 😎) Nice visual aid.
😊
usefull video thank u
Glad you like it George.
While importing data from excel it is using internet explorer as a default browser. Actually, I want to change it to chrome . is it possible.
Thanks for your efforts, Laila. Your way of how you introduce information is really interesting.. Do you have any videos or courses on into to Excel VBA or VBA for beginners??
Thanks for the help :)
Amr
Hi Amr, Thanks for your comment. Glad you like the videos. I'm currently working on a comprehensive VBA online course. It will take a few more months to be ready. I'm also planning to post some VBA content here. Is there anything specific you're looking to learn? You can find a good introduction to Excel VBA here: ruclips.net/user/ExcelVbaIsFun
Is there a video on importing data from text throug office 365? Because that's just totaly different and i cannot figure out how to get my data straight.
What a difference a few years makes...
Would be good to see other sources like sql and nosql
How to import data from pdf file, plz make a video on that
Vielen Dank😍. Can you Show this with REST API. PLEASE. 🥺😍🥰
Do I need to load an external data source in excel in order to use it in a data validation drop down list?
Currently my external data is set to "Connection only" however when I try set the source of a data validation list to "=dbLIST", (the name I gave to the data source) it doesn't load the list for me & when I try define a name first in excel and refer that name to the data source and then use that name as the source for the data validation list it throws an error and the list won't load.
How to import data by function for example importxml and SAVE data automatically?
We can use flash fill right??
How do make Pdf to excel
What happen if web page require login? please help I'm stuck here. i cant go further due to login. I have a password and id both with me. Thanks in advanced.
Can you show me how to make csv file
how to have few XL files on one sheet ( not xl sheets )
i love you
and also pls upload the excel files with direct link where i/we download directly the video tutorial file without any hassle...
The file for this lecture is included in my online course on Udemy. If the files are on the site, I will put the direct link in the descriptions.
i have sql database, i want to upload that data in my web site but there is my sql, so i need sql database in csv format(table format). how i can export that data in csv format. plz. help me
You can download sql data into Excel and then convert to csv (i.e. you can create a connection and use pivot or power query if you have that) - but there must be an option in sql to directly create a csv (specially if you have a large data set). I don't have an sql database that I can check but I am sure there would be sql forums which can help with this...
Hi Leila,
Nice Video. I have a query here. Say when we import data from web , in this case the currency EURO to a cell say A5 and on each refresh, instead of the cell A5 getting refreshed and updated with the new value, I would like the new value to go the cell A6 and A7 and so on upon each refresh leaving the previous log or series as it is. I did it 10 yrs ago and forgot how to do this. Will you be able to help me with this. I guess I used count, reset / macro ......not sure.
Thanks in advance
Do you have Excel 2010? Try using append in the query settings to see if that does the job. I don't have 2010 anymore to test. In 2016 you can use powerquery to append the updated values below....
Thanks Leila.
you save my ass for tomorrow meeting! thanks babe
What if the data is a formula, how do I import them as formulas? I am having the same issue with workbook connections where everything is raw data? Anyone can help with this please?
سلام. ممنون از آموزش خوبتون
شما ایرانی هستین؟
I want to download some data from web on the report which updates on weekly basis.. But the problem here is I can not select the table while on that page. Can you suggest me how to manually select the table from a given link. Also what if the data is too large that it does not fit in one page?
Your answers will be very helpful
Thanks for video & thanks in advance for reply.
Hi Umesh - not all web data would be compatible for this. Is it possible to get your data in a text file or to download the webpage to a csv file? that way you can easily import it into Excel --- if it's too big for Excel to handle, I would suggest to use Power BI - it's included inside Excel if you have 2016 professional, otherwise you can download the add-in....
These options r not in my excel,which excel is used to do this???
Wonderful, great job
ضخمة بيم محد كماش لا
Hi Mam... Can you make a video in Excel which is related with vba macros where cursor automatic move from one sheet to another program, software, application in pc. Please make it.
Hi Nilesh - do you mean opening other Office programs from Excel with VBA?
Jesus Loves You. Did yoyyu over than I Wichita whevpv
4:22 How would we import a .txt file into Excel which has NO structure at all - no coma separators, just a bunch of spaces, but no logic to it. There could be columns in the data, but the import file doesn't separate the text that way - and also has a bunch of repeating headers at the top of each page? I am just curious. Thanks.
I want copy two different documents
I want to copy the sheets of one excel project into the other with messing it up
export the paper packaging, paper bag, paper box, etc.