This guide will teach you how to extract data from Excel or CSV file to Google Sheets.
In other words, you can get raw data from an Excel or CSV file and import them to Google Sheets. Then, you can use the various tools in the application to format and organize the data to make it more presentable.
Just like the name suggests, CSV stands for comma-separated values, a type of file containing data separated by commas. Since it’s one of the most common file types, it can be opened using many other programs. Although, the most common is with Google Sheets. Also, Excel is a famous file type that is commonly opened with Google Sheets.
So extracting data from these two file types to Google Sheets is relatively simple. After following the steps, you can start editing your data into something more orderly.
Now, let’s look at an example wherein there is a need for you to extract data from an Excel or CSV file.
Supposed you are in charge of presenting a market survey data for your company. And the raw data will be sent to you by your colleague. But, the colleague sent you a CSV file when you were planning to use Google Sheets to format and visually present the data.
Not only would it take too much time to manually type in the data but it also will be a challenge to organize the data into something orderly. Besides, it’s easy to extract that data to Google Sheets.
Now that’s just one out of the many scenarios that will occur wherein you will need to know how to extract data from Excel or CSV file to Google Sheets.
Anyway, let’s start by seeing a real example of what this would look like if you do extract data from a CSV file to Google Sheets.
A Real Example of Extracting Data from a CSV File to Google Sheets
Take a look at the example below. First, let’s check what a CSV file looks like before importing its data to Google Sheets. It is data about the sex, weight, height, age, and birthday of 10 people.
And one thing about CSV file types is that they can be opened using a NotePad or WordPad. Furthermore, the data is disorganized and is separated by the defining characteristic of CSV files–commas.
So you want to present this data in a more organized manner. For this reason, you extracted the data from the CSV file to Google Sheets. Finally, check below to see what the data looks like when you do this.
The larger amount of data there is, the messier the extracted data will look. Hence, there will be unnecessary data and incorrectly formatted data. To organize your extracted data, you can delete the column of unnecessary data.
For instance, you do not need to have the birthday data. First, simply right-click on the column letter E. In the dropdown menu, click Delete Column. Then, you can also choose to Hide Column if you think the data may be useful in the future, but you don’t want to see it right now.
As you can see, the data is now more organized. Also, this makes it easier to format, edit, and create charts to make your work more efficient.
You can make your own copy of the spreadsheet above using the link attached below.
So we’ve seen what the data would like after being extracted and transferred to Google Sheets. Great! Let’s not waste any more time and learn the step-by-step process for you to apply in your own work!
Steps in Extracting Data from Excel or CSV file to Google Sheets
This section will provide you with detailed instructions on how to extract the data to Google Sheets. Additionally, each step will have images to guide you visually too.
1. First, open Google Sheets on your computer. On the Start a new spreadsheet, choose Blank. Wait for the spreadsheet to open.
2. Once it is opened, click on File on the upper left. On the dropdown menu, click on Import.
3. An Import file menu will open. Then, head over to Upload on the right side. Click on Select a file from your device. Browse through the location of the file on your device and click it. Otherwise, you can simply drag the file to the menu box.
4. After that, the file will automatically upload. And a new menu box will appear. This is where you can decide the import settings of your file.
5. The first setting you can decide on is the Import location. Once you click it, a dropdown menu will appear, showing six options. Choose carefully what works best for you.
First, Create new spreadsheet will open a spreadsheet for the imported data in a new browser tab. While Insert new sheets will open the imported data in a new sheet in your existing spreadsheet. Then, Replace spreadsheet will simply open the imported data in the opened spreadsheet.
From the name itself, Replace current sheet will replace whatever is in the current sheet with the imported data. And Append rows to current sheet will add the imported data after the last row of data in the current sheet. Lastly, Replace data starting at selected cell will replace the data in the range of selected cells.
Note: Not all file types will have all the aforementioned six options.
6. If you import a CSV file, you have three options under the Separator type. You can choose what letter or symbol Google Sheets will use to separate the cells.
First, Detect automatically means that Google Sheets will detect what is separating your data and split it appropriately. Then, the other two options are Tab and Comma, which is most often the separator in a CSV file. Finally, Custom means you can choose a custom separator to split the data.
Note: Unless a unique separator is used in your file, Detect automatically is the best option and works fine.
7. Additionally, there is a checkbox to convert your data. If you do not want your text to change into numbers and dates, make sure to uncheck the Convert text to numbers and dates.
8. Finally, click Import data.
9. And tada! You have successfully extracted data from a CSV file to Google Sheets.
That’s it! Now, you can work on all the data extracted in the spreadsheet. You can edit, organize, and format all you want! Wasn’t this truly easy and simple? Indeed, whenever the situation arises, you can extract data you need from an Excel or CSV file to Google Sheets.
Are you interested in learning more about what Google Sheets can do?
Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.