我在一个文本文件中有很多数据,我正在尝试找出将文本文件中的各个行转换为单个单元格条目的最快方法。
每个人都可以输入多个条目,我希望每个人都在一个单元格中输入所有信息。因此,我尝试让 Bob 拥有一个单元格,而不是将 Bob 的姓名、地址和电话放在三个单独的单元格中,并且这三个条目中的每一个都应该在单元格内的新行中。
So instead of this:
-----------------------
|Name: Bob |
-----------------------
|Address: 123 Main St. |
-----------------------
|Phone Number: 555-5555|
------------------------
| |
-----------------------
|Name: Jerry |
-----------------------
|Address: 355 Main St. |
------------------------
I want this:
-----------------------
|Name: Bob |
|Address: 123 Main St. |
|Phone Number: 555-5555|
------------------------
|Name: Jerry |
|Address: 355 Main St. |
------------------------
如果能更清楚地说明情况的话,这里有一个屏幕截图:
如果有自动化的方法可以做到这一点(宏或其他任何方法),我会很高兴,因为我必须经常这样做。
答案1
Power Query 解决方案:使用 Power Query
- 在数据表中选择一些单元格
Data => Get&Transform => from Table/Range
或者from within sheet
- 当 PQ 编辑器打开时:
Home => Advanced Editor
- 记下表格姓名在第 2 行
- 将下面的 M 代码粘贴到您所看到的位置
- 将第 2 行的表名改回最初生成的表名。
- 阅读评论并探索
Applied Steps
以了解算法
M 代码
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
//set data type to text
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//add blank row at bottom of table
#"Add blank row" = Table.FromRecords(Table.ToRecords(Source) & {[Column1=null]} ),
//create a column for grouping of the data sets
#"Added Index" = Table.AddIndexColumn(#"Add blank row", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [Column1]= null then [Index] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Filled Up" = Table.FillUp(#"Removed Columns",{"Grouper"}),
//Group on the grouper column
//then combine the data
#"Grouped Rows" = Table.Group(#"Filled Up", {"Grouper"}, {
{"Data", each "#(lf)" & Text.Combine([Column1], "#(lf)"), type text}
}),
#"Remove Grouper Column" = Table.RemoveColumns(#"Grouped Rows",{"Grouper"})
in
#"Remove Grouper Column"
答案2
安装了 Python3...
测试数据:
H:\> type x.txt
Name: Bob
Address: 123 Main St.
Phone number: 555-5555
Name: Jerry
Address: 355 Main St.
脚本要求:
H:\> type x.py
#/usr/bin/env python
import sys
person=[]
for line in sys.stdin:
line=line.strip(' \r\n') # remove spaces, CR and LF
if line!='':
# not empty, append to grouped data about person
person.append(line)
else:
# empty line; print read data, get ready for next person...
person='\n'.join(person) # join read lines with LF between
print(f'"{person}"\n') # output one person data
person=[] # get ready for next person
并且测试运行...
H:\> python x.py <x.txt
"Name: Bob
Address: 123 Main St.
Phone number: 555-5555"
"Name: Jerry
Address: 355 Main St."
要实际将其转换为可导入 Excel 或 LibreOffice 的文件:
H:\> python x.py <x.txt >out.txt