How to import CSV to Excel automatically

how-to-import-csv-to-excel-automatically

Written by Laura Tennyson

Feb 16, 2021

CSV files offer a simple and reliable way to record and share database information in a tabular format. They hold plain text as a series of values (cells) in a series of rows.

If you use specialist software or an ERP, you might download that data to CSV before processing the information further, or sharing it with colleagues.

The great thing about CSV files is that they’re quick and compact, using less memory than spreadsheets. As a result, they provide a quick and easy way to handle, store, and parse data. They’re versatile too: most applications can open CSV files.

So far so good. But once you need to start manipulating, analyzing, and reporting from your data, you’ll probably want to move it to a spreadsheet.

If you’ve ever converted a CSV to Excel manually, you’ll know that it can be a cumbersome task. Luckily there’s now a way to do it automatically.

In this article, I’ll show you how to convert CSV to Excel automatically in a few simple steps. You can even sync multiple CSV files to one Excel file, or split one CSV file into multiple Excel workbooks in one click.

It’s as easy as it sounds. Here’s how it works.

Converting CSV files to Excel format

Option 1: manual import

Excel can read CSV files but when you open a CSV directly inside Excel, your data may be imported incorrectly.

CSV stands for comma-separated values and those commas can prove problematic when Excel tries to read them.

Here I’ve downloaded medical data for a list of patients into CSV format. Here’s how the raw *csv data looks when opened as a simple text file:

import-csv-to-excel-automatically-raw-csv-data

One common problem is that leading zeroes are truncated – or cut off – from numbers when they’re imported into Excel. Other typical issues include formatting errors.

To solve data loss problems, you may need to manually adjust the settings when saving CSV as Excel.

If you’re working with an older version of Excel (Office 2010-2016), use the Text Import Wizard feature.

Those working with a newer version of Excel can import CSV data into Excel using Power Query.

This is a good choice if you want to pull CSV data into specific tables, charts, and visual reports inside Excel. To manipulate imported CSV data at an advanced level with Power Query’s M formula language, note that you’ll need a basic knowledge of coding and a familiarity with writing queries.

To import CSV data into Excel with Power Query:

  • Download the CSV file to your computer.
  • Open a blank workbook in Excel and go to Data > From Text/CSV

A preview of your CSV data loads in Excel:

import-csv-to-excel-automatically-manual-import-method-power-query

At the bottom of the preview window, you’ll see three options.

  • Click Load to import the data directly to Excel
  • Select Load to from the dropdown menu if you want to import the data to a Pivot Table or chart.
  • Select Transform data to edit the data using Power Query.

If you find that some of the columns are still displaying data incorrectly, you can convert them manually. Check Microsoft’s tutorials for details on how to adjust dates and numbers imported from text files.

Automated method: Why sync CSV to Excel?

For one-off data transfers, the manual import method works perfectly well. But if you regularly need to import a bunch of CSVs to Excel, this is extremely time-consuming.

You’ll need to repeat the process for each CSV file you want to import. And when the data in the CSV file changes, you’ll need to create a new file version — again.

In this case, it’s far easier and more efficient to set up an automated workflow to connect CSV to Excel. This allows you to:

 

  • Convert CSV to Excel format automatically
  • Import CSV data to Excel without manual adjustments
  • Sync CSV to Excel with regular updates, from once an hour to once a month
  • Merge multiple CSV files into one Excel file
  • Pull data from colleagues’ CSV files into Excel
Link CSV to Excel files Sheetgo

How to import CSV to Excel automatically

Sheetgo is a no-code tool that allows you to link CSV to Excel and import CSV data into spreadsheets automatically. You can import one single file or import CSV files in bulk.

Once the connection is created, the system will run automatically. This enables you to pull dynamic CSV data directly into Excel.

Follow the steps below to get started.

Step 1: Open Sheetgo

  • Access Sheetgo via the blue button below.
  • Log in with your Google, Microsoft, or Dropbox account.

Step 2: Select the CSV file you want to import

  • Inside Sheetgo, create a new workflow by clicking Connect.
  • Give the workflow a name at the top of the screen so you can identify and edit it later.
  • Under Select source data, click on CSV or TSV file.

Here I’ll name my workflow Lab data CSV import:

import-csv-to-excel-automatically-select-csv-source-file
  • Under Source data > CSV or TSV file click +Select file to locate the CSV inside your cloud storage folder.

To connect your CSV files to Excel, your files must be available online.

If your CSV files are currently saved to your desktop, just follow the steps below to sync them to the cloud.

Are your files stored on your computer?

If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.

This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.

  • When you’ve selected the correct CSV file from your cloud storage, click Continue.

Here I’m importing a lab data file stored on OneDrive:

import-csv-to-excel-automatically-csv-source-file-loaded

In this example, I’m showing you how to import data CSV from one CSV file to Excel.

To import data from multiple CSVs into one Excel worksheet, click +Add another source file. Jump to: how to merge multiple CSVs into one Excel sheet.

Step 3: Choose your Excel file

  • Under Send data, select Excel file.
  • Under Destination > Excel file, select whether to import the CSV file to a new or an existing spreadsheet.

Import to an existing file

  • Select the Existing file option.
  • Click +Select file and choose the Excel file from one of your cloud storage folders. This can be the same folder as where your CSV source file is located or a different platform.

Sheetgo will create a new tab (worksheet) in the destination workbook containing the imported CSV data. By default, Sheetgo will name this tab Sheetgo_SourceFileName. If you want to rename the tab, just enter a name in the New file tab box.

Import to a new file

If you choose to import the CSV data to a new Excel workbook, Sheetgo will create it for you automatically.

  • Select the New file option.
  • By default, the file will be saved to your main (root) folder in the same cloud storage folder as your CSV file. If you want to save the new Excel file to a specific folder or a different cloud storage platform, click Change destination folder.
  • In the File name box, enter a name for your new spreadsheet.
  • Sheetgo will create a new tab (worksheet) in the Excel file containing the imported CSV data. By default, Sheetgo will name this tab Sheetgo_SourceFileName. If you want to rename the tab, type a name in the New file tab box.

Here I’ll ask Sheetgo to send my CSV data to a new Excel workbook in OneDrive called Patient data USA.

I’ll leave the tab name as it is so that I can correctly identify the origin of my data in the Excel file. In this case, that’s Sheetgo_Lab_patients.csv

import-csv-to-excel-automatically-excel-destination-file

Step 4: Complete the connection

  • Click Finish and save to create the connection between your CSV and Excel file.

Wait a few moments while Sheetgo connects the two files.

Here you can see the connection has successfully been created:

import-csv-to-excel-automatically-connection-created
  • Click on Workflow to get a visual representation of how your files are connected.
import-csv-to-excel-automatically-workflow-view

Open the Excel workbook and you will see that it contains the imported CSV data.

Here’s the new Excel file that Sheetgo has created for me automatically: Patient data USA.

The Sheetgo_Lab_patients tab contains the imported data from my CSV file:

import-csv-to-excel-automatically-new-workbook-created

Sync your files at any time

Your CSV file is now linked to the Excel file via a Sheetgo connection. This means that you can sync changes in the CSV file to the Excel file, whenever you like.

  • Open Sheetgo and select the workflow from the list on the left.
  • Click Run on the menu bar.
import-csv-to-excel-automatically-run-workflow-update-sync

When you run the connection, data in the destination Excel tab will be refreshed with the latest data from the CSV file. For this reason, it’s best not to edit or analyze your data inside the Sheetgo tab because changes will be removed when the connection is updated.

To work on the imported data, transfer it to another tab using formulas.

Step 5: Automate the workflow

To save time and ensure that your files are updated on a regular basis, automated the workflow.

  • Open Sheetgo and select your CSV to Excel workflow.
  • Click Automate on the menu bar.
  • Choose how often you want the data to be updated.

Here I’ll schedule automatic updates on Tuesdays and Thursdays at midday.

import-csv-to-excel-automatically-schedule-workflow-updates

How to import multiple CSV files into Excel

If you have a bunch of CSVs that you want to combine – or merge – into one Excel file, you can simply add more files to the Sheetgo connection. You can even import an entire folder of CSV files into one Excel worksheet.

First I’ll show you how to add an extra file to the connection you already made.

Add another CSV to a connection

Returning to my previous example, let’s say I want to import data from a second CSV file into the Excel workbook I just created: Patient data USA.xlsx.

  • Open Sheetgo and select your workflow from the list.
  • Click on the Connections tab then go to the menu icon on the right-hand side of the screen ()
  • Select Edit Connection
import-csv-to-excel-automatically-edit-connection

Under Source data > CSV or TSV file, you’ll see the file that’s currently connected.

 

  • Click +Add another source file
import-csv-to-excel-add-another-source-file
  • Select the additional CSV from your cloud storage

Data merging tip: to consolidate (combine) data from multiple CSVs into one Excel file correctly, you must check that your source files all have the same column layout and headers.

Here I want to add a second CSV file called External_lab_patients:

import-csv-to-excel-automatically-select-additional-csv
  • Repeat this step until you’ve added all the additional CSV files you want to merge
  • Click Done

Optional: Identify source

When you consolidate, or merge, multiple CSV files into one Excel sheet it’s sometimes useful to know where the data originated from.

To add an identifying code to your destination file, enable Sheetgo’s Identify Source feature.

  • Click on Settings
  • Enable Identify source.
  • Choose an identifier: Source location (cloud storage folder), File name, File creation date, or File update date.

Sheetgo will add an additional column in the destination sheet. Values in this column will indicate which CSV file the imported data originated from.

import-csv-to-excel-automatically-identify-source
  • Click Finish and Save

Sheetgo will now add the additional CSV file(s) to the workflow. To transfer the data, update the workflow.

  • In the Connections tab, click the Update connection icon or click Run on the menu bar.
import-csv-to-excel-automatically-merge-multiple-csvs-into-excel
  • Click on Workflow to see how your files are connected
import-csv-excel-combine-multiple-csvs-into-one-excel-file
  • Open your Excel workbook and you will see that data from multiple CSVs has been imported into the destination sheet.

The Sheetgo_Lab_patients tab in my destination file Patient Data USA now contains consolidated data from 2 CSV files.

Column H (Source) tells me which file each row of data was imported from.

import-csv-to-excel-automatically-consolidate-CSV-files-into-one-Excel-sheet

Merge multiple CSVs into one Excel file: folder option

If you want to combine a large number of CSV files into one Excel file, it’s more efficient to consolidate from a folder. Sheetgo lets you consolidate up to 80 files in one.

It’s a time-saving option as there’s no need to select each source file individually.

On top of that, it automates another stage of your workflow. Whenever you drop a new CSV file into the folder, it will be included in the next update automatically.

  • Open Sheetgo and click Connect to create a new workflow.
  • Give your workflow a name at the top of the screen so you can identify it later.
  • Select An entire folder.
import-csv-to-excel-automatically-merge-multiple-CSVs-from-a-folder
  • Select the source folder from your cloud storage.
  • Optional: Under Settings, enable Identify source.
  • Select your destination file: a new or existing Excel file.
  • Optional: Rename the destination tab.
  • Click Finish and Save.

Want to filter CSV data to Excel?

By default, Sheetgo will import data from the entire CSV source file into the destination tab (sheet). What if you only want to pull specific data from your CSV into Excel? No problem — just apply a filter.

Note that you can currently only apply a filter to files stored in Google Drive. If you’ve already signed into Sheetgo using your Microsoft details, just log out of Sheetgo and sign in again using your Google account.

How to apply a filter

  • Open Sheetgo and go to Create workflow > Connect.
  • Give your Untitled Workflow a name at the top of the screen.
  • Under Select source data, choose CSV or TSV file > +Select file.
  • Select the CSV file from your Google Drive.
  • When the file is loaded onto the screen, click Continue.
filter-csv-to-excel-select-csv-file
  • Under Filter data, select Filter by condition or Filter by query.

In this example, I have a CSV file containing a list of property sales in California.

I only want to export the properties with 2 bathrooms.

how-to-filter-csv-data-to-excel

To do that, I’ll use Filter by condition, and apply the following criteria:

  • Condition type > Data matches one condition
  • Column > Column F: baths
  • Criteria > Number is equal to
  • Value > 2
filter-csv-to-excel-conditions
  • When you’ve applied your filter, click Continue.
  • Under Select destination, choose Excel file.
  • Choose to send the filtered CSV data to a new Excel file or an existing Excel file.
    • Note that, because you’re using a filter, the Excel file must also be stored on Google Drive.
  • Give the new file a name, or rename the new file tab (optional).
  • Click Finish and save to create the connection.

Here I’m sending the filtered CSV data to a new .xlsx file called Two-bath apartment sales.

The new Excel file opens in Sheetgo. As you can see, only the list of properties with two bathrooms has been imported.

filter-csv-to-excel-connection-complete

Once you’ve set up the workflow, you can update it at any time by clicking Run. Or click Automate to schedule automatic updates.

Each time the connection is updated, the latest data will be filtered from the CSV file to Excel.

You can also adjust the filter, or add new connections to the workflow to build a larger system that manages all your dynamic data.

Automated CSV to Excel imports

That’s the lowdown on how to convert CSV files to Excel automatically!

Once you’ve set up your workflow it’s easy to add more files and build a bigger data management system. Head to Sheetgo for more inspiration.

Looking for more time-saving CSV automation tips? Check out our guide on how to merge and combine multiple CSV files into one or how to import CSV to Google Sheets automatically.

You may also like…