基于文本的数据文件是当今世界上最常用的数据存储方法之一。这是因为文本文件通常会占用最后一个空间并且它们最容易存储。值得庆幸的是，将CSV(CSV)（逗号分隔值）或TSV（制表符分隔值）文件插入Microsoft Excel非常容易。
如果要在Excel工作表中插入CSV或TSV，您只需要具体了解文件中的数据是如何分隔的。您不一定必须知道有关数据的详细信息，除非您想将这些值重新格式化为字符串、数字、百分比等。
在本文中，您将学习如何将CSV或TSV文件插入Excel工作表，以及如何在导入过程中重新格式化该数据，以节省一些时间。
如何将 CSV 文件(A CSV File Into)插入Excel 工作表(Excel Worksheet)
在将CSV文件插入Excel工作表之前，您需要确认数据文件实际上是逗号分隔的（也称为“逗号分隔”）。
验证它是逗号分隔的文件
为此，请打开Window Explorer并导航到存储文件的目录。选择查看(View)菜单并确保选择了预览窗格(Preview pane)。
然后，选择您认为包含逗号分隔数据的文件。您应该在文本文件中的每条数据之间看到一个逗号。
下面的示例来自包含 2010 年SAT 大学理事会(SAT College Board)学生成绩结果的政府数据集。
如您所见，第一行是标题行。每个字段用逗号分隔。之后的每一行都是一行数据，每个数据点用逗号分隔。
这是一个带有逗号分隔值的文件的示例。现在您已确认源数据的格式，可以将其插入Excel工作表。
将 CSV 文件插入工作表(Insert A CSV File Into Your Worksheet)
要将源CSV数据文件插入Excel工作表，请打开一个空白工作表。
- 从菜单中选择数据(Data)
- 从功能区上的获取(Get)和转换数据(Transform Data)组中选择获取数据(Get Data)
- 从文件中(From File)选择
- From Text/CSV选择
注意：作为替代方案，您也可以直接在功能区上选择From Text/CSV
这将打开文件浏览器。浏览(Browse)到您存储CSV文件的位置，选择它，然后选择Import。
这将打开数据导入向导。Excel根据前 200 行，根据输入数据文件的格式分析传入数据并设置所有下拉框。
您可以通过更改以下任何设置来调整此分析：
- 文件来源(File Origin)：如果文件是另一种数据类型，如ASCII或UNICODE，您可以在此处更改。
- 分隔符(Delimiter)：如果使用分号或空格作为替代分隔符，您可以在此处选择。
- 数据类型检测(Data Type Detection)：您可以强制Excel基于整个数据集进行分析，而不仅仅是前 200 行。
当您准备好导入数据时，选择此窗口底部的加载。(Load)这会将整个数据集带到您的空白Excel工作表中。
在Excel(Excel)工作表中拥有该数据后，您可以重新组织该数据、对行和列进行分组(group rows and columns)，或对数据执行Excel 函数(Excel functions)。
将CSV 文件(CSV File Into)导入其他Excel元素
工作表并不是您可以将CSV数据导入其中的全部。在最后一个窗口中，如果您选择Load To而不是Load，您将看到其他选项的列表。
此窗口中的选项包括：
- 表(Table)：这是将数据导入空白或现有工作表的默认设置
- 数据(PivotTable Report)透视表：将数据带入数据透视表报告(Pivot Table report)，让您汇总传入的数据集
- PivotChart：在汇总图表中显示数据，如条形图或饼图
- 仅创建连接(Only Create Connection)：创建与外部数据文件的连接，您稍后可以使用该连接在多个工作表上创建表格或报告
数据透视图(PivotChart)选项非常强大。它使您可以跳过将数据存储在表格中，然后选择字段以创建图表或图形的步骤。
作为数据导入过程的一部分，您可以选择字段、过滤器、图例和轴数据以在一个步骤中创建这些图形。
如您所见，将CSV插入Excel工作表时有很大的灵活性。
如何将 TSV 文件(A TSV File Into)插入Excel 工作表(Excel Worksheet)
如果您的传入文件是制表符分隔而不是逗号分隔怎么办？
该过程与上一节中的大部分相同，但您需要使用Delimiter下拉框来选择Tab。
此外，请记住，当您浏览数据文件时，Excel会自动假定您正在查找 *.csv 文件。所以在文件浏览器窗口中，记得把文件类型改成All Files (*.*)才能看到*.tsv 类型的文件。
选择正确的分隔符后，将数据导入任何Excel工作表、数据透视图(PivotChart)或数据透视报表(Pivot Report)的工作方式完全相同。
转换数据的工作原理
在Import Data窗口中，如果您选择Transform Data而不是选择Load，它将打开Power Query Editor窗口。
此窗口让您深入了解Excel如何自动转换它正在导入的数据。您还可以在此处调整数据在导入过程中的转换方式。
如果您在此编辑器中选择一列，您将在功能区的“转换”(Transform)部分下看到假定的数据类型。
在下面的示例中，您可以看到Excel假定您希望将该列中的数据转换为整数格式(Number)。
您可以通过选择数据类型旁边的向下箭头并选择您喜欢的数据类型来更改此设置。
您还可以通过选择一列并将其拖动到您希望它在工作表中的位置来重新排序此编辑器中的列。
如果您的传入数据文件没有标题行，您可以将Use First Row as Headers更改为Use Headers as First Row。
通常，您永远不必使用Power Query 编辑器(Power Query Editor)，因为Excel非常擅长分析传入的数据文件。
但是，如果这些数据文件的数据格式不一致，或者您想要重新组织数据在工作表中的显示方式，则Power Query 编辑器(Power Query Editor)可让您执行此操作。
您的数据在MySQL数据库中吗？了解(Learn)如何将 Excel 连接到 MySQL(connect Excel to MySQL)以引入该数据。如果您的数据已经在另一个Excel文件中，还有一些方法可以将多个 Excel 文件中的数据合并(merge data in multiple Excel files)到一个文件中。
How To Insert CSV or TSV Into An Excel Worksheet
Text based datа files are one of the mоst common methods of storing data in the world today. This is because text files, in general, consume thе last space and they’re easiest to store. Thankfully, it’s very easy to insert either CSV (comma sepаrated values) or TSV (tab separated νaluеs) files into Microsoft Excel.
If you want to insert CSV or TSV into an Excel worksheet, you only need to know specifically how the data in the file is separated. You don’t necessarily have to know the details about the data, unless you want to reformat those values into strings, numbers, percentages and more.
In this article you’ll learn how to insert either a CSV or TSV file into your Excel worksheet and how to reformat that data in the process of importing, to save yourself some time.
How To Insert A CSV File Into An Excel Worksheet
Before you can insert a CSV file into your Excel worksheet, you need to confirm that the data file is actually comma separated (also known as “comma-delimited”).
Verify It’s A Comma Separated File
To do this, open Window Explorer and navigate to the directory where the file is stored. Select the View menu and make sure the Preview pane is selected.
Then, select the file that you believe contains comma separated data. You should see a comma between each piece of data in the text file.
The example below comes from a government dataset containing 2010 SAT College Board student score results.
As you can see, the first line is the header line. Each field is separated by a comma. Every other line after that is a line of data, with each data point separated by a comma.
This is an example of what a file with comma separated values looks like. Now that you’ve confirmed the formatting of your source data, you’re ready to insert it into your Excel worksheet.
Insert A CSV File Into Your Worksheet
To insert the source CSV data file into your Excel worksheet, open a blank worksheet.
- Select Data from the menu
- Select Get Data from the Get & Transform Data group on the ribbon
- Select From File
- Select From Text/CSV
Note: As an alternative, you could also select From Text/CSV directly on the ribbon.
This will open the file browser. Browse to where you’ve stored the CSV file, select it, and select Import.
This will open the data import wizard. Excel analyzes the incoming data and sets all of the dropdown boxes according to the format of the input data file, based on the first 200 rows.
You can adjust this analysis by changing any of the following settings:
- File Origin: If the file is of another data type like ASCII or UNICODE, you can change that here.
- Delimiter: If semicolons or spaces are used as an alternative delimiter, you can select that here.
- Data Type Detection: You can force Excel to analyze based on the entire dataset rather than just the first 200 rows.
When you’re ready to import the data, select Load at the bottom of this window. This will bring the entire dataset into your blank Excel worksheet.
Once you have that data in an Excel worksheet, you can then reorganize that data, group rows and columns, or perform Excel functions on the data.
Import CSV File Into Other Excel Elements
A worksheet isn’t all you can import CSV data into. In the last window, if you select Load To instead of Load, you’ll see a list of other options.
Options in this window include:
- Table: This is the default setting that imports the data into a blank or existing worksheet
- PivotTable Report: Bring data into a Pivot Table report that lets you summarize the incoming data set
- PivotChart: Display the data in a summarized chart, like a bar graph or pie chart
- Only Create Connection: Creates a connection to the external data file, which you can use later to create tables or reports on multiple worksheets
The PivotChart option is very powerful. It lets you skip over the steps of storing the data in a table and then selecting fields to create charts or graphs.
As part of the data import process, you can choose the fields, filters, legends, and axis data to create those graphics in a single step.
As you can see, there’s a lot of flexibility when it comes to inserting CSV into an Excel worksheet.
How To Insert A TSV File Into An Excel Worksheet
What if your incoming file is tab delimited instead of comma delimited?
The process is mostly the same as in the previous section, but you’ll want to use the Delimiter dropdown box to select Tab.
Also, remember that when you’re browsing for the data file, Excel automatically assumes you’re looking for a *.csv file. So in the file browser window, remember to change the file type to All Files (*.*) to see the *.tsv type file.
Once you’ve selected the correct delimiter, importing data into any Excel worksheet, PivotChart, or Pivot Report works exactly the same way.
How Transform Data Works
In the Import Data window, if you select Transform Data instead of selecting Load, it’ll open the Power Query Editor window.
This window gives you insight into how Excel automatically converts the data that it’s importing. This is also where you can tweak how that data gets converted during the import.
If you select a column in this editor, you’ll see the assumed data type under the Transform section in the ribbon.
In the example below, you can see that Excel assumed you’d want to convert the data in that column into a Whole Number format.
You can change this by selecting the down arrow next to the data type and selecting the data type you prefer.
You can also reorder columns in this editor by selecting a column and dragging it to the position where you want it to go in your worksheet.
If your incoming data file doesn’t have a header row, you can change Use First Row as Headers to Use Headers as First Row.
Normally, you should never have to use the Power Query Editor, since Excel is pretty good at analyzing incoming data files.
However, if those data files are inconsistent in how the data is formatted or you want to reorganize how the data appears in your worksheet, then the Power Query Editor lets you do that.
Is your data in a MySQL database? Learn how to connect Excel to MySQL to bring in that data. If your data is already in another Excel file, there are also ways to merge data in multiple Excel files into one file.
