标题: [求助]txt转csv的问题 [打印本页]
作者: hgx126 时间: 2008-10-22 13:31 标题: [求助]txt转csv的问题
一目录下有许多文本文件，格式相同，内容如下：
5411|54110000377889|0|30000.0|2006/09/29|88130095863129|2008/09/27|0.0|0.0|罗宗良|
54110000410360|5411|88130114562095|0|敬永猛|60000.0|2007/05/31|2009/05/29|0.0|0.0|
5411|88130122241160|0|30000.0|2007/08/28|2008/08/27|0.0|54110000744667|798.53|李国富|
要将此文本文件转换为Excel的csv.文件，我写如下代码：54110000410360|5411|88130114562095|0|敬永猛|60000.0|2007/05/31|2009/05/29|0.0|0.0|
5411|88130122241160|0|30000.0|2007/08/28|2008/08/27|0.0|54110000744667|798.53|李国富|
for %%a in (*.txt) do (
for /f "delims=" %%h in (%%a) do (
set var=%%h
setlocal enabledelayedexpansion
set var=!var:^|=,!
echo.!var!>>"%%~na_new%%.csv"
endlocal
)
)
pause
转换成功了，可打开csv文件后，凡单元格内数字位数超过11位的数字全部成了科学计数。for /f "delims=" %%h in (%%a) do (
set var=%%h
setlocal enabledelayedexpansion
set var=!var:^|=,!
echo.!var!>>"%%~na_new%%.csv"
endlocal
)
)
pause
我现在要做的是在数字位数超过11位的数字前加一个单引号“'”,转换成如下：
5411,'54110000377889,0,30000.0,2006/09/29,'88130095863129,2008/09/27,0.0,0.0,罗宗良,
'54110000410360,5411,'88130114562095,0,敬永猛,60000.0,2007/05/31,2009/05/29,0.0,0.0,
5411,'88130122241160,0,30000.0,2007/08/28,2008/08/27,0.0,'54110000744667,798.53,李国富,
请教代码如何写？'54110000410360,5411,'88130114562095,0,敬永猛,60000.0,2007/05/31,2009/05/29,0.0,0.0,
5411,'88130122241160,0,30000.0,2007/08/28,2008/08/27,0.0,'54110000744667,798.53,李国富,
作者: HAT 时间: 2008-10-22 13:46
这样能满足要求么？
@echo off
for %%a in (*.txt) do (
for /f "delims=" %%h in (%%a) do (
set var=%%h
setlocal enabledelayedexpansion
set var=!var:^|=,'!
echo.'!var!>>"%%~na_new%%.csv"
endlocal
)
)
for %%a in (*.txt) do (
for /f "delims=" %%h in (%%a) do (
set var=%%h
setlocal enabledelayedexpansion
set var=!var:^|=,'!
echo.'!var!>>"%%~na_new%%.csv"
endlocal
)
)
作者: hgx126 时间: 2008-10-22 14:41
你这个是把所有的数字和汉字前都加了一个单引号。
我希望只是在数字位数超过11位的数字加引号，能行吗？
作者: HAT 时间: 2008-10-22 15:08 标题: Re 3楼
批处理计算字符串的长度是一件比较浪费资源的操作，当数据量比较大的时候尤为明显。
如果一定要这样，可在本版搜索字符串长度
作者: hgx126 时间: 2008-10-22 15:22
那能不能这样：只给第二列、第三列和第五列的字符前加单引号？
例将下面：
5426|47960000132016|88130097408157|0|88130140097408134|45000.0|兰林|
5426|48320000038190|88130097413071|0|88130140097413048|10000.0|王培杰|
5426|48360000105672|88130097505153|0|88130140097505130|2220.0|张勇|
转换成：
5426,'47960000132016,'88130097408157,0,'88130140097408134,45000.0,兰林,
5426,'48320000038190,'88130097413071,0,'88130140097413048,10000.0,王培杰,
5426,'48360000105672,'88130097505153,0,'88130140097505130,2220.0,张勇,
[ Last edited by hgx126 on 2008-10-22 at 15:35 ]5426|47960000132016|88130097408157|0|88130140097408134|45000.0|兰林|
5426|48320000038190|88130097413071|0|88130140097413048|10000.0|王培杰|
5426|48360000105672|88130097505153|0|88130140097505130|2220.0|张勇|
转换成：
5426,'47960000132016,'88130097408157,0,'88130140097408134,45000.0,兰林,
5426,'48320000038190,'88130097413071,0,'88130140097413048,10000.0,王培杰,
5426,'48360000105672,'88130097505153,0,'88130140097505130,2220.0,张勇,
作者: lxmxn 时间: 2008-10-26 19:02
| Quote: | |
|
作者: longge0000 时间: 2008-10-26 21:51 标题: Re 5楼
| Quote: | |
|
比较笨的方法,有没有高手来简化下:
echo off
for %%a in (*.txt) do (
for /f "delims=| tokens=1,2,3,4,5,*" %%b in (%%a) do (
setlocal enabledelayedexpansion
set var2=%%c & set var2=!var2:~12!
set var3=%%d & set var3=!var3:~12!
set var5=%%f & set var5=!var5:~12!
set var6=%%g
if defined var2 (set var2='%%c) else (set var2=%%c)
if defined var3 (set var3='%%d) else (set var3=%%d)
if defined var5 (set var5='%%f) else (set var5=%%f)
set var6=!var6:^|=,!
echo %%b,!var2!,!var3!,%%e,!var5!,!var6!>>"%%~na_new%%.csv"
endlocal
)
)
pause
作者: hgx126 时间: 2008-10-29 23:56
| Quote: | |
|
利用的是外部命令吗？看不懂能不能解释一下？
作者: HAT 时间: 2008-10-30 00:13 标题: Re 8楼
gawk.exe可以去这里下载：批处理室附件收集专用帖
这个命令比较复杂，靠别人讲是学不会的，自己先看看帮助吧。
作者: yishanju 时间: 2008-10-30 01:26
搜索gawk http://www.cn-dos.net/forum/sear ... mp;searchsubmit=yes
作者: terse 时间: 2008-10-30 02:28
基本满足(没特殊字符的话) 但文件大的话 BAT的效率还是问题
@echo off
for %%i in (*.txt) do (
for /f "usebackq delims=" %%a in ("%%i") do (
set var=%%a
setlocal enabledelayedexpansion
set var=!var:^|= !
for %%b in (!var!) do (
for /f "skip=1 delims=:" %%c in ('^(echo "%%b"^&echo.^)^|findstr /o ".*"') do (
set/a n=%%c-5
if !n! gtr 11 (set "str=!str!'%%b,")else set "str=!str!%%b,"
))
>>"%%~ni_new.csv" echo !str!
endlocal
)
)
pause
for %%i in (*.txt) do (
for /f "usebackq delims=" %%a in ("%%i") do (
set var=%%a
setlocal enabledelayedexpansion
set var=!var:^|= !
for %%b in (!var!) do (
for /f "skip=1 delims=:" %%c in ('^(echo "%%b"^&echo.^)^|findstr /o ".*"') do (
set/a n=%%c-5
if !n! gtr 11 (set "str=!str!'%%b,")else set "str=!str!%%b,"
))
>>"%%~ni_new.csv" echo !str!
endlocal
)
)
pause