Exporting data in Excel is a direct process, with various format options:

• Values separated by commas (.CSV).
• Flat text delimited by tabs.
• XML (extensible marking language).
• Traditional Excel Book (.xlsx or .xls).

To export data, follow these steps:

1. Click File > Save like .
2. Select change file type .
3. Choose the desired format, such as CSV (delimited by Comas) .
4. Select the destination folder.
5. Click Save .

Sometimes, a message will appear indicating that some characteristics of the book will not be saved in the new format. If it is a simple file, you can continue without problem.

Import data from databases

Excel can connect to databases such as Microsoft Access or SQL Server to import tables and perform analysis. Let's see how to do it from each one:

From Access

1. In Excel, go to data> Get data> from Access .
2. Select the .ACCDB or .MDB file desired.
3. Choose the tables you want to import.
4. Indicate if you want a dynamic report .

Excel will create a dynamic table linked to Access data. This table can be updated automatically.

From SQL Server

1. In the Data tab, select New Consultation> from SQL Server database .
2. Enter the name of the server and database.
3. Select the tables to import or provide an SQL query if you want to filter the information.
4. Choose the place on the sheet where the data will be inserted.

You can also use SQL Server Management Studio to export from there directly to Excel.

Import data from the web

What if the data is on a web page? Excel allows you to extract tables lodged in HTML sites directly from the application:

1. Go to data> Obtain data> from web .
2. Enter the URL of the website.
3. Select the table or tables you want to import.
4. Click load .

The data will be automatically inserted into your sheet. This method is useful for importing quotes, economic indicators, price tables and much more.

Import by API with JSON

Some web services such as Hubspot, Shopify or Pipedrive offer public API from which you can download data in JSON format. Excel allows you to import this type of data, but if you want to do it automatically and without programming, Coupler.Oo is an excellent solution.

With Coupler.io you can program the automatic data update from JON Fuentes to an Excel sheet. You just need to configure the URL of the API and authorize yourself (if necessary). Then you choose the destination sheet and voila.

Synchronize data between Excel files

When you work with several Excel files, it is common to need to maintain certain synchronized data. This can be achieved through formulas linked between books:

1. Open both Excel files.
2. Select a cell file cell and write = .
3. Click the origin cell origin that you want to link.
4. Press intro .

You can also copy the origin cell origin and paste it as a link at the destination with special paste> Paste link .

Remember that the links between files are interrupted if the origin file is not open.

Import data from Google Sheets

There are several ways to bring data from Google Sheets to Excel:

1. with Coupler.io

1. Access coupler.io and select Google Sheets as origin and Excel as destination.
2. Connect your Google and Microsoft accounts.
3. Select the sheet and the range you want to import.
4. Set the automatic update frequency.

2. Through public CSV

1. From Google Sheets, go to file> Share> Publish on the web .
2. Choose the leaf and CSV format.
3. Copy the generated link.
4. In Excel, go to data> Obtain data> From Web .
5. Paste the URL of the published CSV.

This method is not recommended for sensitive data, since they become publicly accessible.

Import from PDF files

Do you have tables locked in a PDF? Excel allows you to easily extract them:

1. Go to data> Get data> From file> from PDF .
2. Select the file and expect Excel to detect the tables.
3. Select the desired table and press loading .

If you do not have this option available, you can copy the data from the PDF and paste them in Word. From Word, convert the text into a table and then copy the table to Excel.

Import and export data in Project

Microsoft Project also allows you to exchange data with Excel through its export/import assistant. You can export tasks, resources or assignments, selecting exactly what fields include and how to save them.

Compatible formats include:

• Excel (.xlsx or .xls)
• Flat text (.txt)
• CSV
• XML

The assistant allows you to create allocation maps , which define how the fields between both programs should be transferred. You can create new maps or use an existing one.

Import and paste

This is the simplest and most direct method, but not the most efficient. When copying data from another application or file, you can paste them into Excel directly. Although it is practical, it can cause format errors, loss of disorganized formulas or columns.

It is preferable to use the import assistant whenever possible.

Common mistakes when importing and how to avoid them

When performing any import or export operation, errors may arise. Some usual:

• Fields without expected format : Be sure to check the wizard and select the correct format.
• Columns are missing : some objects such as subforms or sub -informes are not automatically exported.
• Expressions are not exported : only the results are exported, not the calculations. Add formulas manually in Excel.
• Desalineated data : It may be due to incorrect delimiters.
• Dates prior to 1900 : Excel does not recognize them and are replaced by null values.

Excel is extremely versatile to import and export data from multiple sources. Whether you work with text files, databases, web pages, PDF or APIS JSON documents, Excel gives you flexibility to integrate all this information in one place, process it and use it as a base for dynamic tables, reports or data models.

The above is the detailed content of Import and export data in Excel: Complete guide. For more information, please follow other related articles on the PHP Chinese website!