格式的文本文件,所以我可以将其导入到Excel中 [英] Format text file so I can import it into excel
问题描述
我有一个巨大的,我需要转换成Excel的小号preadsheet地址和细节的清单,我认为最好的办法是读取数据,然后写的线,所以他们是分开第二个文档制表符分隔同时承认空行(数据项之间),以preserve每个单独的地址。
这是格式为:
AddressA1结果
AddressB1结果
帖子code1结果
名1结果
PhoneNumber1结果
AddressA2结果
AddressB2结果
帖子code2结果
名称2结果
名称2结果
PhoneNumber2结果
AddressA3结果
AddressB3结果
帖子code3结果
NAME3结果
PhoneNumber3结果
块引用>所以难度也说到当有一个公司多个名字,但我的手,如果必要的(理想情况下,他们希望采取相同的地址,对方)。这些格式
所得到的文本文件,然后,想成为制表符分隔到:
名称|地址a | AddressB |发表code |电话号码我想这将是最简单的一个简单的.BAT命令中呢?或者我应该在Excel中打开列表,并通过运行脚本..?
我想通过写
$名称[我,如果它增加了每次进入一个数组($地址$名称等),我可以运行通过话,我可以用它来建立一个新的文本文件]
的标签的$地址[$ i]
等有数百个条目,并把它在手被证明..困难的。
我在MEL(基本上C ++)的一些经验,所以我明白在一般的编程,但有些在如何
的.bat
和Excel亏损(VB?)处理并定义空行和制表符。解决方案下面是只使用Word和Excel的方法。我用您发布的数据。我假设名称2是唯一的可选字段。
- 您的文本粘贴到Word中。
- 一个特殊的替换所有的段落标记
字符。 (CTRL-H,搜索^ P,替换为|)- 用不同的特殊字符替换所有换行符。 (CTRL-H,特殊字符,搜索手动换行符,以取代;)
这就是它看起来像在Word中:
<$p$p><$c$c>AddressA1;AddressB1;Post$c$c1;Name1;PhoneNumber1|AddressA2;AddressB2;Post$c$c2;Name2;Name2;PhoneNumber2|AddressA3;AddressB3;Post$c$c3;Name3;PhoneNumber3||
然后将文本转换为表(插入 - >表 - >将文本转换为表),通过划定。这使得1列3行(加2个空行)。
然后复制表。
在Excel中现在
- 粘贴表。 (这将是每排一排,与所有A列中的字段。)
- 将文本转换到列(数据标签,文本列,分隔,检查分号)
- 排序列E的电话号码应该组合在一起。
- 剪下的电话号码列E和复制到F列。
I have a huge list of addresses and details I need to convert into an Excel spreadsheet and I think the best way would be to read the data and then write a second document that separates the lines so that they are tab-delimited whilst recognizing blank lines (between data entries) to preserve each separate address.
It is in the format:
AddressA1
AddressB1
Postcode1
Name1
PhoneNumber1AddressA2
AddressB2
Postcode2
Name2
Name2
PhoneNumber2AddressA3
AddressB3
Postcode3
Name3
PhoneNumber3So the difficulty also comes when there are multiple names for a company, but I can hand format those if necessary (ideally they want to take on the same address as each other).
The resulting text document then, wants to be tab-delimited to: Name|AddressA|AddressB|Postcode|Phone Number
I am thinking this would be easiest to do within a simple .bat command? or should I open the list in excel and run a script through that..?
I'm thinking if I can run through where it adds each entry to an array ($address $name etc) then I can use that to build a new text file by writing
$name[i]
tab$address[$i]
etcThere are hundreds of entries and putting it in by hand is proving.. difficult.
I have some experience in MEL (basically C++) so I do understand programming in general, but somewhat at a loss in how
.bat
and Excel (VB?) handle and define empty lines and tabs.解决方案Here's a method using only Word and Excel. I used the data that you posted. I am assuming that Name2 is the only optional field.
- Paste your text into Word.
- Replace all paragraph marks with a special characters. (Ctrl-h, Search for ^p, Replace with |)
- Replace all line breaks with a different special character. (Ctrl-h, Special character, search for Manual line break, replace with ;)
This is what it looks like in Word:
AddressA1;AddressB1;Postcode1;Name1;PhoneNumber1|AddressA2;AddressB2;Postcode2;Name2;Name2;PhoneNumber2|AddressA3;AddressB3;Postcode3;Name3;PhoneNumber3||
Then convert text to table (Insert -> Table -> Convert text to table), delimiting by ;. This gives 3 rows (plus 2 blank rows) of 1 column.
Then copy the table.
Now in Excel:
- Paste the table. (It'll be one row in each row, with all of your fields in column A.)
- Convert the text to columns (Data tab, Text to columns, Delimited, check semicolon)
- Sort by column E. The phone numbers should be grouped together.
- Cut the phone numbers in column E and copy to column F.
这篇关于格式的文本文件,所以我可以将其导入到Excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!