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. 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. For example, 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. So why import CSV to Excel?
Although CSV is good for storing large amounts of data, once you need to start manipulating, analyzing, and reporting your data, you’ll probably want to move it to a spreadsheet. Spreadsheet tools like Excel offer incredible functionalities to effectively organize and optimize your raw data into actionable insights, which you can then use to make important business decisions.
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. Without further ado, let’s explore 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.
Converting CSV files to Excel format manually
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 a customer database into CSV format. Here’s how the raw *csv data looks when opened as a simple text file:
As you can see, this data is extremely difficult to read; the raw data is not formatted.
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 familiarity with writing queries.
Manually import CSV data into Excel with Power Query
To import CSV data into Excel with Power Query, you need to:
1. Download the CSV file to your computer.
2. Open a blank workbook in Excel and go to Get Data. Choose the data source Text/CSV
3. Browse for your CSV file. Once selected, click Get data.
4. Click Next. A preview of your CSV data should load in Excel and look something like this:
5. At the bottom of the preview window, select Load to add it to your worksheet.
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.
The best option is to use an automation tool like Sheetgo to import your CSV files to Excel for you.
Why use Sheetgo to connect CSV to Excel?
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.
It’s far easier and more efficient to set up an automated workflow to connect CSV to Excel. With Sheetgo, you can:
- Convert CSV to Excel format automatically: Create a workflow where you can automate the transfer process from start to finish. What’s more, when you update your original CSV file, Sheetgo will automatically update your Excel file for you.
- Import CSV data to Excel without manual adjustments: Simply upload your CSV to an online storage folder like OneDrive or Dropbox, and directly import your data to Excel.
- Sync CSV to Excel with regular updates: Create a custom automation schedule for your needs; automate from once an hour to once a month.
- Merge multiple CSV files into one Excel file: Combine multiple CSV files into tabs within a single workbook for easy access to all of your data.
- Pull data from colleagues’ CSV files into Excel: Create connections between various CSV files and your Excel file to receive CSV data from various locations at the same time.
Now that you understand the benefits of automatically syncing CSV to Excel with Sheetgo, let’s go through a simple step-by-step on how to import your CSV to Excel using the Sheetgo web app.
How to import CSV to Excel automatically
Step 1: Sign up to Sheetgo
Signing up to Sheetgo is easy – simply log in with your Google, Microsoft, or Dropbox account. Click on the button below to sign up for free.
Step 2: Sync files to an online storage folder
In order to automate data transfer between your CSV and Excel files, you need to make sure you have activated the synching between your files and an online storage folder.
This way, any changes made to your source file (CSV) will automatically be transferred to your new destination file (Excel). It also keeps your files secure and allows you to access them from anywhere. Learn more here.
Step 3: Create a new workflow
Now it’s time to start connecting! Head to My workspace in the Sheetgo web app, and click +New workflow. Under Create a custom workflow section, click Create connection.
Step 4: Select source file
Select your source file. If you’re importing multiple CSV files, click the Multiple files option. In this example, I am only importing one CSV, so I will click Single file.
Please note: Don’t forget to name your workflow at the top of the screen!
Now it’s time to upload your file. Click +Select file and browse to find your CSV. You can change the online storage file on the left-hand side of the pop-up. Double-click to select your file.
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 5: Connect to your Excel file
Scroll down to the Send to section. As I am connecting to a single Excel file, I will choose Single file.
Here, you can choose whether to import the CSV to a new or an existing spreadsheet.
Import CSV to an existing Excel file
Select the Existing file option, then click +Select file to upload your Excel. Sheetgo will automatically create a new tab for your imported data – simply rename the tab if needed.
Import CSV to a new Excel 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. If you want to rename the tab, type a name in the New file tab box.
Step 6: Complete the connection
To save your connection between your CSV and Excel file, click Finish and Save.
If you open your Excel file, you will see that your CSV file data has successfully been imported.
To get a visual representation of how your files are connected, click on Workflow.
Step 7: Sync your files
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. You can either manually or automatically sync your files.
Option 1: Manually sync files
In Workflow, click on the Run button to update your connections and sync your files.
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.
Option 2: Automatically sync files
In Workflow, click on the Automate button. Activate the Run automatically button and create your own automation schedule. You can choose what days to automate your workflow, up to every hour. Once you’ve created your schedule, click Save.
And there you have it! Your workflow is complete and ready to use! To start importing your CSV to Excel automatically, you can sign up for free to Sheetgo by pressing the blue button below!
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, let’s see how to add an extra file to the connection you already made.
Add another CSV to a connection
Let’s say I want to import data from a second CSV file into the Excel workbook I just created – subscription invoice data.
1. Open the Sheetgo web app and select your workflow within the My workspace area.
2. Select Workflow and expand the sidebar on the right-hand side. In the Connections tab, click the more options button (⋮).
3. Select Edit.
4. Under the Source section, click +Add another source file.
5. Click +Select file, then browse and select your additional CSV file 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.
6. Repeat this step until you’ve added all the additional CSV files you want to merge. Click Done editing.
Optional: Identify source
When you 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 data feature.
- Click on Settings underneath your source files.
- Enable Identify data by switching on the button.
- Choose an identifier: Source file location (cloud storage folder), Source file name, Source file creation date, Source file update date, Source tab name, or Destination file update date and time.
Sheetgo will add an additional column to the destination sheet. Values in this column will indicate which CSV file the imported data originated from.
Once you’ve added your additional CSV file and made changes to the settings, click Save changes.
Sheetgo will now add the additional CSV file(s) to the workflow. To transfer the data, update the workflow by clicking Run.
Open your Excel workbook and you will see that data from multiple CSVs has been imported into the destination sheet, along with an extra column identifying your data source.
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.
1. Open the Sheetgo web app and create a new workflow.
2. This time, under the Source section, select Files in a folder.
3. Select the folder from your cloud storage.
4. Continue with the usual steps listed in the how to import CSV to Excel automatically section.
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
1. Create a new workflow and connect your source file.
2. Scroll down to the Process section and select Filter. Here, you can select whether to filter your data by Condition, or Query.
3. In this example, let’s filter my data by condition. Adjust the settings to create a condition, specifying the criteria and value.
4. Once finished with your filters, click Next.
5. Choose your destination file and save the workflow.
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.
You can start connecting and synching your CSV and Excel files automatically if you sign up for a free trial to Sheetgo’s paid plans! Click on the blue button below to get started today.
Automated CSV to Excel imports
And there you have it! That’s a complete guide 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.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.