How To Import and Export Data In Google Sheets? (CSV, Excel, and Others)

Looking to bring data into a spreadsheet for easier analysis and organization? Or maybe you want to export your Google Sheets file to share it with your colleagues?

One of the many impressive functions of Google Sheets is its ability to seamlessly import and export data through CSV, PDF, Excel files, and more.

This flexibility means you can effortlessly move your essential business data between tools and people, making collaboration and data management smoother than ever.

Read more: Top 9 Google Sheets templates for business. 

Luckily, Google Sheets offers multiple ways to bring in data, depending on the specific file type or source you're working with: 

1. Import a Google Sheets, Excel, or TXT file.
2. Import a CSV file.
3. Copy and paste data directly from another spreadsheet.
4. Import data from a URL or web page.
5. Connect to Google Forms.

There are also a couple of ways to export your data from Google Sheets, depending on the format:

1. Export as Excel, PDF, CSV, or other formats.
2. Share your Google Sheets spreadsheet.

Before you learn how to import and export data in Google Sheets using the methods above, it’s important to understand the different formats available for importing and exporting.

Read more: Everything you can do with Google Sheets AI 

Microsoft Excel (.xlsx): For transferring data across to Excel.

PDF Document (.pdf): Best for printing or sharing uneditable, formatted reports.

Comma-separated values (.csv, current sheet): Useful for importing or exporting into other databases or tools.

Tab-separated values (.tsv, current sheet): A plain-text format especially useful for programming use.

OpenDocument Format (.ods): Compatible with OpenOffice and LibreOffice.

Web Page (.html, zipped): For HTML files.

Read more: Google Sheets vs Excel as a CRM. 

The process of importing data into Google Sheets essentially depends on the file type you want to import.

Let’s take a closer look at how you can import data into Google Sheets for all format types:

Open your Google Sheets spreadsheet file.

From the top menu, click ‘File > Import’.

Choose where to import from (Upload, ‘My Drive’, ‘Shared with me’, or ‘Recent’).

Find the file you want to import and select ‘Insert’.

Choose the import location (new spreadsheet, insert into current sheet, or replace current sheet).

Click ‘Import Data’.

Resize cells if required.

If you want to import a CSV file into Google Sheets, the process is slightly different:

Open an existing or new Google Sheets spreadsheet.

Click ‘File’ in the top menu, then select ‘Import’.
Select ‘Upload’ and then ‘Browse’ to find the CSV file on your computer.

Choose how you want to import the data (create a new spreadsheet, insert a new sheet, replace the entire spreadsheet, replace the current sheet, etc).

Choose the ‘Separator type’ (for most cases, ‘Detect automatically’ works best.

Click ‘Import Data’.

And that’s it! Your CSV will then be pulled into your Google Sheet instantly!

While Google Sheets doesn’t directly support importing a PDF file, there are still a few ways you can import data from a PDF into your Google Sheets! Here’s how to do it:

Use an online tool like Adobe Acrobat, Smallpdf, or ILovePDF to convert your PDF to an Excel or CSV file.

Then, in Google Sheets, go to ‘File > Import’ and upload the converted file using the method explained above.

If your PDF file only has simple data (like tables), open the PDF and copy the text manually.

Paste it directly into a new or existing Google Sheets spreadsheet.

Clean up formatting or use ‘Split text to columns’ (under the Data menu) to organize the pasted data.

Upload your PDF document to Google Drive.

Right-click the file and choose ‘Open with > Google Docs’.

Google will use OCR to extract the text from the file.

Copy the extracted text data from the Google Doc.

Paste it into Google Sheets.

Note: While this method is effective, the accuracy of the data often depends on how the PDF was first created (scanned images vs. actual text). 

Also, remember that complex PDF formatting may not convert cleanly to your spreadsheet.

Read more: Can Google Sheets be used for business?

Sometimes, copying and pasting data is one of the quickest ways to transfer information from a web table or Google Sheets/Excel spreadsheet.

To do this:

1. Open the spreadsheet that contains the data you want to copy.
2. Highlight the cells you want to copy.
3. Press Ctrl + C (Windows) or Cmd + C (Mac) to copy the data.
4. Open the Google Sheets spreadsheet where you want to paste the data.
5. Select the cell where you want the pasted data to appear.
6. Press Ctrl + V (Windows) or Cmd + V (Mac) to paste the data.
7. Click ‘Format > Clear formatting’ in the top menu to clear any formatting issues.

It’s important to note that if you’re copying from Excel, Google Sheets automatically converts the file’s structure, but the formatting may not import correctly for web tables. 

To avoid this, paste the data into a blank Google Doc to clean it up and then import it straight into Google Sheets.

Want to import live data from a URL, web page, CSV link, or another Google Sheet? You can use the IMPORTHTML, IMPORTDATA, or IMPORTRANGE functions!

To import tables or lists from a web page:

1. Open your Google Sheets spreadsheet.
2. Click on the cell where you want the data to appear.
3. Enter the formula: =IMPORTHTML("URL", "table", index)
4. Replace "URL" with the full web address (make sure you keep the quotes!).
5. Replace "table" with "list" if you're importing a list.
6. Replace “index” with the number of the table or list on the page (start with 1).
7. Click ‘Enter’. 

Google Sheets will then automatically load the live data from your chosen web page URL into your sheet!

To import a CSV or TSV file from a URL:

1. Find a link to a .csv or .tsv file (this link must be publicly accessible).
2. In a Google Sheets cell, enter the IMPORTDATA  formula alongside the link URL. For example:  =IMPORTDATA("https://example.com/data.csv")
3. Select ‘Enter’.

And the file’s content will populate your sheet automatically! This method is helpful for any CSV/TSV files found online.

To import live data from another Google Sheets file:

1. Open the source Google Sheet and copy the URL.
2. In your destination sheet, type the import range function: =IMPORTRANGE("URL", "SheetName!A1:D100").
3. Delete "URL" from the formula and replace it with the full sheet URL you just copied in quotes.
4. Replace "SheetName!A1:D100" with the sheet and range you want to import.
5. Click ‘Allow Access’.

And there you go! The Google Sheets data will automatically load into your sheet and stay synced at all times.

If you want to link a Google Form (and the data within that Google Form) to a new or existing Google Sheets spreadsheet:

1. Go to forms.google.com and select the correct form you want to use.
2. Click ‘Responses’ at the top of the form editor.
3. Select the Google Sheets icon (it looks like a green spreadsheet).

And that’s it! Your form is now linked to your chosen Google Sheet. And every new response will be automatically added as a new row in the spreadsheet!

This method is perfect for those looking to collect responses or data through a Google Form.

Read more: Can Google Forms generate an invoice?

How to export data in Google Sheets?

Want to easily share, back up, or use your Google Sheets data in other spreadsheet tools, databases, or reporting systems? You must export your data!

Here’s how you can export your Google Sheets data (all formats):

1. Open the Google Sheets file you want to export.
2. Click ‘File’ in the top menu and hover over ‘Download’.
3. Choose your export format from the list of drop-down options.

Optional: Before finalizing your export, you can tailor how your document looks through the export settings.

You can change the export range, paper size & orientation, scaling options, margins, and page numbers to suit your preferences (for PDF files).

The file will then save to your computer’s downloads folder.

Open the downloaded file to ensure it has been exported correctly.

Email, print, or upload the document.

Note: CSV format only supports one sheet at a time and strips all formatting (you get raw text and numbers).

And it’s that easy! You’ve now successfully exported your Google Sheets spreadsheet data!

Read more: How to lock cells in Google Sheets?

1. When collaborating with a team or external clients, sharing your Google Sheets file directly is often a faster and more efficient way to do it.

Here’s how you can share your spreadsheet with specific people:

Open your Google Sheets file.

1. Click  ‘Share’ in the top-right corner.
2. Enter the email addresses of those you want to share the file with.
3. Choose the access level from the dropdown: Viewer (can only view), Commenter (can add comments), or Editor (can make changes).
4. Add a message if you like (your recipients will see this message).
5. Click ‘Send’.

Alternatively, simply share your Google Sheets spreadsheet via a link. To do this:

1. Click ‘Share’ in the top-right corner.
2. Select ‘Copy link’.
3. Under ‘General access’, click the dropdown and select Restricted (only added people can access it) or Anyone with the link (choose view/comment/edit rights).
4. Click ‘Copy link’ then ‘Done’.
5. Simply share the link by email, chat, or on your website!

Here are some top tips for sharing your file safely:

• Use the ‘Viewer’ access when you want to share with clients or for public reports (this means they can’t edit the file).
• Enable ‘Notify people’ if you want collaborators to get an email alert.
• Set up ‘Version history’ (under File > Version history) if multiple people will be editing the file.

Read more: How to share only one tab in a spreadsheet? 

Essentially, importing and exporting Google Sheets data gives you the flexibility to move, manage, and share data effortlessly across different platforms, formats, and teams.

Whether you're working with CSVs from your eCommerce dashboard, pulling real-time data from a URL, or sending a report to a client, these functions streamline your workflow.

By importing your data from multiple sources, you’ll create one centralized data management system that can be easily analyzed and used for many functions.

And exporting data helps with collaborating with others in a version that works for them.

In short, by leveraging these importing and exporting features, you can turn Google Sheets into a powerful data hub perfect for analysis, reporting, collaboration, and automation.

Thanks so much for reading, and happy importing and exporting!