中国DOS联盟论坛 - Powered by Discuz! Board

标题: [求助]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.文件，我写如下代码：

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位的数字全部成了科学计数。我现在要做的是在数字位数超过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,李国富,

请教代码如何写？

作者: 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   
  )
)

作者: 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 ]

作者: lxmxn 时间: 2008-10-26 19:02

E:\>gawk --posix --re-interval -F"|" -v OFS=, "{for(i=1;i<=NF;i++)if($i~/^[0-9.]{9,}/)$i=\"'\"$i;print}" your_file.txt 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,李国富, E:\>

作者: longge0000 时间: 2008-10-26 21:51 标题: Re 5楼

Originally posted by hgx126 at 2008-10-22 15:22: 那能不能这样：只给第二列、第三列和第五列的字符前加单引号？ [code] 例将下面： 5426|47960000132016|88130097408157|0|88130140097408134|45000.0|兰林| 5426|4832 ...

比较笨的方法,有没有高手来简化下: 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

E:\>gawk --posix --re-interval -F"|" -v OFS=, "{for(i=1;i<=NF;i++)if($i~/^[0-9.]{9,}/)$i=\"'\"$i;print}" your_file.txt 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,李国富, E:\>

利用的是外部命令吗？看不懂能不能解释一下？

作者: HAT 时间: 2008-10-30 00:13 标题: Re 8楼

gawk.exe可以去这里下载：批处理室附件收集专用帖这个命令比较复杂，靠别人讲是学不会的，自己先看看帮助吧。

作者: yishanju 时间: 2008-10-30 01:26

搜索gawk www.cn-dos.net/forum ...

作者: 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