|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
『楼 主』:
[求助]批量统计文本中有效电话号码并粘贴到excel表中?
使用 LLM 解释/回答一下
现我有很多附件中那样的文件,目的就是提取出其中此人常联系的有效号码。什么1008611,10661700之类的除外。并把提取出的10个有效号码粘贴到一个excel表格的对应此文件名的行的某列中,因为数据实在是太多了。之前都是用excel排序人工弄的。效率实在太低了。而且很折磨人啊。试过了很多办法都不知道怎么办。茫然啊。望高人指点一下迷津,给个思路也行。具体用什么比较好解决。之前查到一个叫replace pioneer的软件,里面有个叫pattern counter的工具可以导入文本文件并统计排序,只要设置下参数,比如\d{7,}就是统计7位以上数据的,但是他这个软件并不能让我批处理这么多文件。苦恼哦。
Now I have many files like those in the attachments. The purpose is to extract the valid numbers that this person often contacts. Exclude things like 1008611, 10661700, etc. And paste the extracted 10 valid numbers into a certain column of the row corresponding to this file name in an Excel spreadsheet, because there are really too much data. Before, I always used Excel sorting and did it manually. The efficiency is too low. And it's really torturing. I've tried many methods and don't know what to do. Helpless! Hope a master can give a clue. Just a train of thought is okay. What is specifically better to solve it. Before, I found a software called replace pioneer. There is a tool called pattern counter in it that can import text files and count and sort. Just set parameters, such as \d{7,} is to count data with 7 or more digits, but this software can't let me batch process so many files. Troubled oh.
|
|
2009-9-17 06:58 |
|
|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
『第 2 楼』:
使用 LLM 解释/回答一下
你们找得到我上传的文件吗。叫1693.txt,第一次上传。。在什么上传系统里面
Can you find the file I uploaded? It's called 1693.txt, uploaded for the first time. In which upload system is it?
|
|
2009-9-17 07:02 |
|
|
xiaofei120
新手上路

积分 14
发帖 14
注册 2009-9-8
状态 离线
|
|
2009-9-17 07:03 |
|
|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
|
2009-9-17 08:14 |
|
|
Hanyeguxing
银牌会员
     正在学习中的菜鸟...
积分 1039
发帖 897
注册 2009-3-1 来自 在地狱中仰望天堂
状态 离线
|
『第 5 楼』:
使用 LLM 解释/回答一下
注意:
1,此批处理需要第三方命令行工具 sed.exe ,从网址 http://ishare.iask.sina.com.cn/f/5621524.html 下载,然后和批处理保存在同一目录下,放到system32目录下也可。
2,1693.txt为原始源文件,I.txt为输出文件
3, >I.txt echo 序号 次数 电话号码,中间是制表符,不是一串空格
4, if !C! lss 11 >>I.txt echo !C! !E! %%j),中间是制表符,不是一串空格
@echo off&setlocal enabledelayedexpansion&>I.txt echo 序号 次数 电话号码
for /f "delims=" %%i in ('sed "s//\n/g" 1693.txt ^| findstr ^*$') do (set A=%%i
if "!A:~0,3!" neq "000" if "!A:~0,2!" neq "10" if "!A:~0,4!" neq "2009" (if "!A:~0,5!" == "17951" set A=!A:~5!)&set /a A_!A!+=1)
for /f "tokens=2,3 delims==_" %%i in ('set ^| findstr "^A_"') do set B=00000000%%j&set C_!B:~-9!:%%i==
for /f "tokens=2,3 delims==_:" %%i in ('set ^| findstr "^C_" ^| sort /r') do (set /a C+=1
call :D %%i
if !C! lss 11 >>I.txt echo !C! !E! %%j)
start notepad I.txt&exit
:D
set E=%1
:F
if "!E:~0,1!" == "0" set E=!E:~1!&goto F
输出结果示例:
序号 次数 电话号码
1 1280 134****0555
2 1163 135****8816
3 154 137****5982
4 76 081****2445
5 48 800****54107
6 44 132****5767
7 43 153****1636
8 43 132****3700
9 35 138****4200
10 26 159****6315
......
将此文本中内容直接在 excel 中粘贴就可以。
Last edited by Hanyeguxing on 2009-9-18 at 07:53 ]
Notes:
1. This batch processing requires a third-party command-line tool sed.exe, which can be downloaded from the URL http://ishare.iask.sina.com.cn/f/5621524.html, then saved in the same directory as the batch processing, or placed in the system32 directory.
2. 1693.txt is the original source file, and I.txt is the output file.
3. >I.txt echo 序号 次数 电话号码, there are tabs in the middle, not a string of spaces.
4. if !C! lss 11 >>I.txt echo !C! !E! %%j), there are tabs in the middle, not a string of spaces.
@echo off&setlocal enabledelayedexpansion&>I.txt echo 序号 次数 电话号码
for /f "delims=" %%i in ('sed "s//\n/g" 1693.txt ^| findstr ^*$') do (set A=%%i
if "!A:~0,3!" neq "000" if "!A:~0,2!" neq "10" if "!A:~0,4!" neq "2009" (if "!A:~0,5!" == "17951" set A=!A:~5!)&set /a A_!A!+=1)
for /f "tokens=2,3 delims==_" %%i in ('set ^| findstr "^A_"') do set B=00000000%%j&set C_!B:~-9!:%%i==
for /f "tokens=2,3 delims==_:" %%i in ('set ^| findstr "^C_" ^| sort /r') do (set /a C+=1
call :D %%i
if !C! lss 11 >>I.txt echo !C! !E! %%j)
start notepad I.txt&exit
:D
set E=%1
:F
if "!E:~0,1!" == "0" set E=!E:~1!&goto F
Example of output result:
Serial number Times Telephone number
1 1280 134****0555
2 1163 135****8816
3 154 137****5982
4 76 081****2445
5 48 800****54107
6 44 132****5767
7 43 153****1636
8 43 132****3700
9 35 138****4200
10 26 159****6315
......
You can directly paste this text into Excel.
Last edited by Hanyeguxing on 2009-9-18 at 07:53 ]
|

批处理之家 http://bbs.bathome.net/forum-5-1.html |
|
2009-9-17 08:35 |
|
|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
『第 6 楼』:
使用 LLM 解释/回答一下
非也非也,我提取出的号码就是他常联系其他人的号码,为了就是找到本人。不要滤除10位以下的数字啊,我是说找到他常联系的10个人的号码比如13688888888,15988888888,8234888,0107654321之类的,不用排列不排列了,只要出现次数多的前10个号码就把他们粘出来就好了
No, no. The number I extracted is the number he often contacts others with, just to find the person himself. Don't filter out numbers with less than 10 digits. I mean, find the numbers of the 10 people he often contacts, such as 13688888888, 15988888888, 8234888, 0107654321, etc. Don't worry about arranging them. Just paste the top 10 numbers that appear more frequently.
|
|
2009-9-17 09:04 |
|
|
Hanyeguxing
银牌会员
     正在学习中的菜鸟...
积分 1039
发帖 897
注册 2009-3-1 来自 在地狱中仰望天堂
状态 离线
|
『第 7 楼』:
使用 LLM 解释/回答一下
@echo off
关闭回显及不显示本行。
setlocal enabledelayedexpansion
启用变量延迟。
>I.txt echo 序号 次数 电话号码
创建文本 I.txt 并输出内容“序号 次数 电话号码”。
sed "s//\n/g" T.txt
提取文本中所有数字,每组数字都独立一行,并忽略所有敏感字符。
findstr ^*$
从 sed 操作的结果集中搜索符合条件的数字,即7位以上的数字。
for /f "delims=" %%i in (
解析 sed 和 findstr 的结果集。
set A=%%i
将解析所获取的值赋值给变量 A 。
if "!A:~0,3!" neq "000"
截取变量 A 的前3个字符,判断其是否为 000 ,否则...
if "!A:~0,2!" neq "10"
截取变量 A 的前2个字符,判断其是否为 10 ,否则...
if "!A:~0,4!" neq "2009"
截取变量 A 的前4个字符,判断其是否为 2009 ,否则...(以上3个 if 的语句为与逻辑。)
if "!A:~0,5!" == "17951" set A=!A:~5!
截取变量 A 的前5个字符,判断其是否为 17951 ,是则截取其第6个字符及其后面所有字符,并赋值给变量 A 。
set /a A_!A!+=1
设置变量集并计数。其中,变量名为: A_!A! : A_ 为前缀, !A! 为后部分,由以上4个 if 语句判断获得。
set
输出所有环境变量。
findstr "^A_"
从环境变量中过滤出变量名字首为 A_ 的所有变量。
for /f "tokens=1,2 delims==_" %%i in (
解析过滤后的环境变量,以 = 和 _ 为区段分隔符。变量名赋值给 %%i ,变量的值赋值给 %%j 。
set B=00000000%%j
将变量 %%j 前加8个0,并赋值给变量 B 。
set C_!B:~-9!:%%i==
设置变量集。每个变量的值均是 = 。变量名是 C_!B:~-9!:%%i ,其中 C_ 为前缀, B:~-9! 为前部,由变量 B 截取最后9个字符获得, : 为中间部分,为下一语句分隔用, %%i 为后部分。
findstr "^C_"
从环境变量中过滤出变量名字首为 C_ 的所有变量。
sort /r
对 findstr 的结果集进行排序, /r 指定反向排序。
for /f "tokens=1,2 delims==_:" %%i in (
解析过滤后的环境变量,以 = 、 _ 和 : 为区段分隔符。变量名前半部分赋值给 %%i ,变量名后半部分赋值给 %%j 。
set /a C+=1
加计数,并将累积值赋值给变量 C 。
call :D %%i
调用标签 :D ,并传递变量 %%i 为第一个参数 %1 。
>>I.txt echo !C! !E! %%j
输出变量 !C! 、!E! 和 %%j 。
if !C! lss 11 (
判断变量 C 是否小于11,是则...
start notepad I.txt
使用记事本程序打开文本 I.txt ,并不等待关闭退出这个程序。
exit
退出本批处理。
set E=%1
将传递的参数 %1 设置为变量 E 。
if "!E:~0,1!" == "0" set E=!E:~1!&goto F
截取变量 E 的第一个字符并判断其是否为0,如果不是,退出标签 :D ;如果是0,则截取第2个字符及后面所有字符,即去掉这个0,并赋值给变量 E 。然后返回标签 :F ,重复这个操作,直到变量 E 的字首没有0为止。
Last edited by Hanyeguxing on 2009-9-18 at 08:02 ]
@echo off
Turn off echoing and do not display this line.
setlocal enabledelayedexpansion
Enable variable delay.
>I.txt echo Serial number Number of times Phone number
Create the text I.txt and output the content "Serial number Number of times Phone number".
sed "s//\n/g" T.txt
Extract all numbers in the text, each group of numbers is on an independent line, and ignore all sensitive characters.
findstr ^*$
Search for numbers that meet the conditions from the result set of the sed operation, that is, numbers with 7 or more digits.
for /f "delims=" %%i in (
Parse the result set of sed and findstr.
set A=%%i
Assign the value obtained from the parsing to variable A.
if "!A:~0,3!" neq "000"
Intercept the first 3 characters of variable A, judge whether it is 000, otherwise...
if "!A:~0,2!" neq "10"
Intercept the first 2 characters of variable A, judge whether it is 10, otherwise...
if "!A:~0,4!" neq "2009"
Intercept the first 4 characters of variable A, judge whether it is 2009, otherwise... (The above 3 if statements are in logical AND.)
if "!A:~0,5!" == "17951" set A=!A:~5!
Intercept the first 5 characters of variable A, judge whether it is 17951, if yes, intercept its 6th character and all characters behind it, and assign it to variable A.
set /a A_!A!+=1
Set the variable set and count. Among them, the variable name is: A_!A! : A_ is the prefix,!A! is the latter part, obtained by the above 4 if statements.
set
Output all environment variables.
findstr "^A_"
Filter out all variables whose variable names start with A_ from the environment variables.
for /f "tokens=1,2 delims==_" %%i in (
Parse the filtered environment variables, with = and _ as the section delimiters. The variable name is assigned to %%i, and the value of the variable is assigned to %%j.
set B=00000000%%j
Add 8 zeros in front of variable %%j and assign it to variable B.
set C_!B:~-9!:%%i==
Set the variable set. The value of each variable is =. The variable name is C_!B:~-9!:%%i, where C_ is the prefix, B:~-9! is the front part, obtained by intercepting the last 9 characters of variable B, : is the middle part, used for the next statement separation, %%i is the latter part.
findstr "^C_"
Filter out all variables whose variable names start with C_ from the environment variables.
sort /r
Sort the result set of findstr, /r specifies reverse sorting.
for /f "tokens=1,2 delims==_:" %%i in (
Parse the filtered environment variables, with =, _ and : as the section delimiters. The first half of the variable name is assigned to %%i, and the second half of the variable name is assigned to %%j.
set /a C+=1
Add to the count and assign the cumulative value to variable C.
call :D %%i
Call the label :D and pass variable %%i as the first parameter %1.
>>I.txt echo !C! !E! %%j
Output variables!C!,!E! and %%j.
if!C! lss 11 (
Judge whether variable C is less than 11, if yes...
start notepad I.txt
Open the text I.txt with the notepad program and do not wait for the program to close and exit.
exit
Exit this batch processing.
set E=%1
Set the passed parameter %1 as variable E.
if "!E:~0,1!" == "0" set E=!E:~1!&goto F
Intercept the first character of variable E and judge whether it is 0. If not, exit the label :D; if it is 0, intercept the 2nd character and all characters behind it, that is, remove this 0, and assign it to variable E. Then return to label :F and repeat this operation until there are no 0s at the beginning of variable E.
Last edited by Hanyeguxing on 2009-9-18 at 08:02 ]
|

批处理之家 http://bbs.bathome.net/forum-5-1.html |
|
2009-9-17 11:10 |
|
|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
『第 8 楼』:
使用 LLM 解释/回答一下
呵呵,真好用,我刚试了下,剩下的就该我好好去消化学习一下这些代码,好改成真正适合自己的东西。还有就是能不能让这10个号码自动的就导入一个excel表的指定位置去啊。完了就是把一个文件夹里面所有的txt原始数据都像这条那样自动一个一个往excel里面导
Hehe, really easy to use. I just tried it. The remaining part is that I should好好 digest and study these codes well to modify them into something really suitable for myself. Also, can these 10 numbers be automatically imported into a specified position of an Excel sheet? And then, all the txt original data in a folder can be automatically imported into Excel one by one like this one.
|
|
2009-9-18 03:33 |
|
|
Hanyeguxing
银牌会员
     正在学习中的菜鸟...
积分 1039
发帖 897
注册 2009-3-1 来自 在地狱中仰望天堂
状态 离线
|
|
2009-9-18 06:56 |
|
|
anykill
新手上路

积分 6
发帖 6
注册 2009-9-16
状态 离线
|
『第 10 楼』:
使用 LLM 解释/回答一下
恩,很多问题还需要处理,我刚接触这个,代码看起来好火星。我慢慢消化一下再问吧,最后还是得靠自己的努力。不能有什么不懂的就开问,多谢大家带我走进这扇大门啊。温暖的社会主义大家庭
Well, there are still many issues that need to be addressed. I just started getting into this, and the code looks really archaic. I'll digest it slowly and then ask questions. In the end, I still have to rely on my own efforts. I can't just ask every time I don't understand something. Thanks everyone for leading me into this door. The warm socialist big family
|
|
2009-9-18 08:56 |
|
|
iqplhui
新手上路

积分 1
发帖 1
注册 2009-10-1
状态 离线
|
|
2009-10-2 10:12 |
|
|