如果您一直在Excel中处理数据并且您已经意识到您想要使用Google 表格函数或脚本,那么将文件从(Google Sheets)Excel转换为Google 表格(Google Sheets)是相当容易的。
但是,根据原始数据(format of the original data)的格式,有多种方法可以做到这一点。该过程取决于Excel电子表格是保存为CSV格式、标准Excel格式还是已经包含任何脚本。
在本文中,我们将介绍将Excel文件转换为Google 表格(Google Sheets)电子表格的四种方法。
1. 最简单:通过复制(Copy)和粘贴(Paste)将 Excel 转换(Convert Excel)为Google 表格(Google Sheets)
如果您只需要将数据从Excel转换为Google 表格(Google Sheets),并且后台没有任何复杂的函数或脚本,那么只需简单地复制和粘贴数据即可。
1. 为此,只需突出显示要在Excel中复制的所有单元格。右键(Right)单击所选范围内的任意位置,然后选择Copy。
2. 右键单击 Google 表格(Google Sheets)电子表格左上角您要放置数据的单元格。选择粘贴(Paste)。
这将使用您从Excel电子表格复制的所有数据填充工作表。就这么简单。
2.从Excel文件:(Excel File)直接(Excel File Directly)打开Excel文件
您可以直接打开Excel文件 (*.XLSX),但您需要先执行一个步骤来上传文件。
在Google 表格(Google Sheets)中,只需按照以下步骤直接打开Excel文件。
- 从菜单中选择文件,然后选择(File)打开(Open)或导入(Import)。
- 在出现的窗口中,选择上传(Upload)选项卡,然后选择从设备中选择文件(Select a file from your device)按钮。
- 浏览(Browse)到存储在您计算机上的Excel文件,选择它,然后选择“打开(Open)”按钮。
- 这将上传Excel文件,然后在Google 表格(Google Sheets)中打开它。
当您使用这种方法将Excel转换为Google 表格(Google Sheets)时,您会注意到发生了几件事。
- Excel文件会自动转换为Google 表格(Google Sheets)格式。
- 它会在您的Google 表格(Google Sheets)帐户中新创建的电子表格中打开。
- 格式尽可能与原始Excel电子表格的格式相匹配。(Excel)
3. 导入CSV:直接(Import Directly)从CSV文件导入
您可以直接从以CSV 格式保存的(CSV format)Excel文件导入。但是,您需要知道文件是如何分隔的;无论是制表符还是逗号分隔,还是使用其他字符。
使用导入功能(Using the Import Feature)
遵循上述相同的步骤,但当您选择CSV文件时,您会看到一个窗口,用于选择文件的格式,以及您希望如何将电子表格数据转换为Google Sheet电子表格。
您还可以配置是否要在新电子表格中打开数据、替换当前工作表或附加到当前工作表。还要配置是否要将CSV文件中的文本转换为适当的数据类型(数字、日期和公式)。
完成转换选项后,选择导入(Import)数据以将CSV文件数据导入您的Google 表格(Google Sheets)电子表格。
使用 IMPORTDATA 函数(Using the IMPORTDATA Function)
另一种从保存为CSV格式的Excel文件中导入数据的方法是使用(Excel)Google 表格中的(Google Sheets)IMPORTDATA函数。
这种方法有一个问题。该文件需要在线存储在网络服务器上,并可通过直接链接访问。例如,人口普查局(Census Bureau)提供了大量CSV格式的数据文件,例如以下链接中各州的人口总数:
https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv
您可以使用IMPORTDATA函数将此类CSV文件中的数据直接导入您的Google 表格电子表格。(Google Sheets)
1. 打开一个新的Google 表格(Google Sheets)电子表格。单击(Click)要导入数据的单元格。在该单元格中,输入:
=IMPORTDATA(“https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv”)
2. 当你按下Enter时,你会看到加载状态几秒钟。然后所有数据将显示在电子表格中。
3. 如果您想对数据进行快照并将其放入新的电子表格中,以便在源CSV(CSV)数据在线更改时不会更新,只需突出显示原始工作表的所有单元格并复制它们即可。然后打开一个新工作表,右键单击您希望数据所在的单元格并选择Paste special,然后Paste values only。
这会将所有原始数据复制到Google 表格(Google Sheets)中的一个新的静态电子表格中。
4. 打开方式:上传后打开文件(Open File)
还有另一种技术可以将Excel转换为Google 表格(Google Sheets)。无论文件是以CSV还是XLSX格式保存,这都有效。
1. 将文件直接上传到您的Google Drive帐户中的任何文件夹中。
2. 右键单击文件并选择Open with,然后选择Google Sheets。
3. 如果这是CSV文件,它将直接以Google 表格(Google Sheets)格式打开。如果它是XLSX格式,它将在Google Docs中以XLSX格式打开。您会看到它显示在顶部。要将其转换为Google 表格(Google Sheets)格式,请从菜单中选择文件(File),然后选择另存为 Google 表格(Save as Google Sheets)。
这将转换电子表格并将其打开到Google 表格(Google Sheets)中的新电子表格中。
将 Excel 转换为 Google 表格
如您所见,将Excel(Excel)电子表格数据导入Google 表格(Google Sheets)的方法不止一种。您选择的方法取决于原始文件的格式(format of the original file),以及您觉得哪种程序最方便。
4 Ways to Convert Excel to Google Sheets
If you’ve been working with data in Excel and you’ve realized yoυ want to use Google Sheets functions or scripting, it’s fairly easy to convert files from Excel to Google Shеets.
However, there are multiple ways you can do this depending on the format of the original data. The process depends on whether the Excel spreadsheet is saved in CSV format, standard Excel format, or includes any scripting already.
In this article we’ll look at four ways you can convert Excel files to a Google Sheets spreadsheet.
1. Simplest: Convert Excel to Google Sheets with Copy and Paste
If you only need to convert data from Excel to Google Sheets and you don’t have any complicated functions or scripts in the background, a simple copy and paste of the data will do.
1. To do this, just highlight all of the cells you want to copy in Excel. Right click anywhere in the selected range and select Copy.
2. Right-click in the upper left cell in the Google Sheets spreadsheet where you’d like to place the data. Select Paste.
This will fill in the sheet with all of the data you’ve copied from the Excel spreadsheet. It’s as simple as that.
2. From Excel File: Just Open the Excel File Directly
You can open an Excel file (*.XLSX) directly, but you’ll need to go through a step to upload the file first.
In Google Sheets, just follow these steps to open an Excel file directly.
- Select File from the menu, and select Open or Import.
- In the window that appears, select the Upload tab and then select the Select a file from your device button.
- Browse to the Excel file that’s stored on your computer, select it, and select the Open button.
- This will upload the Excel file and then open it in Google Sheets.
You will notice several things happen when you use this approach to convert Excel to Google Sheets.
- The Excel file automatically converts to Google Sheets format.
- It opens in a newly created spreadsheet in your Google Sheets account.
- The formatting matches that of the original Excel spreadsheet as much as possible.
3. Importing CSV: Import Directly From an CSV File
You can import directly from an Excel file that’s saved in CSV format. However, you’ll need to know how the file is delimited; whether it’s tab or comma delimited, or with some other character.
Using the Import Feature
Follow the same procedure above, but when you select the CSV file, you’ll see a window to select what the formatting of the file is, and how you want to convert the spreadsheet data into a Google Sheet spreadsheet.
You can also configure whether you want to open the data in a new spreadsheet, to replace the current sheet, or append to the current sheet. Also configure whether you want the text from the CSV file converted to the appropriate data type (numbers, dates, and formulas).
Once you’ve made your conversion options, select Import data to bring the CSV file data into your Google Sheets spreadsheet.
Using the IMPORTDATA Function
Another approach to bringing in data from an Excel file saved in CSV format is by using the IMPORTDATA function in Google Sheets.
There is one catch to this approach. The file needs to be stored on a web server online, and accessible via a direct link. For example, the Census Bureau provides lots of data files in CSV format, such as population totals by state at the following link:
https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv
You can import the data directly from such a CSV file into your Google Sheets spreadsheet using the IMPORTDATA function.
1. Open a new Google Sheets spreadsheet. Click on the cell where you want the imported data to go. In that cell, type:
=IMPORTDATA(“https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv”)
2. When you press Enter, you’ll see a loading status for a few seconds. Then all of the data will show up in the spreadsheet.
3. If you want to take a snapshot of the data and put it into a new spreadsheet so that it doesn’t update any time the source CSV data changes online, just highlight all of the cells of the original sheet and copy them. Then open a new sheet, right-click in the cell where you want the data to go and select Paste special, then Paste values only.
This will copy all of the original data into a new, static spreadsheet in Google Sheets.
4. Open With: Open File After Uploading
There is one more technique to convert Excel to Google Sheets. This works whether the file is saved in CSV or XLSX format.
1. Upload the file directly to your Google Drive account inside any folder.
2. Right-click the file and choose Open with, then choose Google Sheets.
3. If this is a CSV file, it’ll open directly in Google Sheets format. If it’s in XLSX format it will open in Google Docs in XLSX format. You’ll see this displayed at the top. To convert it to Google Sheets format, select File from the menu and then select Save as Google Sheets.
This will convert the spreadsheet and open it into a new spreadsheet in Google Sheets.
Converting Excel to Google Sheets
As you can see there’s more than just one way to get Excel spreadsheet data into Google Sheets. The approach you choose depends on the format of the original file, and which procedure feels the most convenient to you.