Board logo

标题: [问题求助] [已解决]VBS怎样实现电话簿转换,坚的表格变成横的表格,谢谢。 [打印本页]

作者: zhangop9    时间: 2011-10-29 23:24     标题: [已解决]VBS怎样实现电话簿转换,坚的表格变成横的表格,谢谢。

原文件的内容如下,想转换为表格文件,冒号后是数据,请转为表格来编辑,编辑好后还原成这个样,这只有两个人,实际上有100多条。(说明白一点就是坚的表格变成横的表格)

myText.txt
----------
AccountName:
Anniversary:
AssistantName:
AssistantTelephoneNumber:
Birthday:
Body:
Business2TelephoneNumber:
BusinessAddressCity:
BusinessAddressCountry:
BusinessAddressPostalCode:
BusinessAddressState:
BusinessAddressStreet:
BusinessFaxNumber:
BusinessTelephoneNumber:
CarTelephoneNumber:
Categories:其他
Children:
CompanyName:M.其他
CompanyTelephoneNumber:
CustomerId:
Department:
Email1Address:
Email2Address:
Email3Address:
FileAs:139邮箱
FirstName:139邮箱
GovernmentId:
Home2TelephoneNumber:
HomeAddressCity:
HomeAddressCountry:
HomeAddressPostalCode:
HomeAddressState:
HomeAddressStreet:
HomeFaxNumber:
HomeTelephoneNumber:
IM1Address:
IM2Address:
IM3Address:
ItemId:-2147481866
JobTitle:
LastName:
Manager:
MiddleName:
MobileTelephoneNumber:58139
Nickname:
OfficeLocation:
OtherAddressCity:
OtherAddressCountry:
OtherAddressPostalCode:
OtherAddressState:
OtherAddressStreet:
PagerNumber:
RadioTelephoneNumber:
RingTone:\Extended_Rom\LS\HELLO moto.mp3
Spouse:
Suffix:
Title:
YomiCompanyName:
YomiFirstName:
YomiLastName:
----------
AccountName:
Anniversary:
AssistantName:
AssistantTelephoneNumber:
Birthday:
Body:
Business2TelephoneNumber:
BusinessAddressCity:
BusinessAddressCountry:
BusinessAddressPostalCode:
BusinessAddressState:
BusinessAddressStreet:
BusinessFaxNumber:
BusinessTelephoneNumber:
CarTelephoneNumber:
Categories:卫22
Children:
CompanyName:卫22
CompanyTelephoneNumber:
CustomerId:
Department:
Email1Address:
Email2Address:
Email3Address:
FileAs:艾小
FirstName:艾小
GovernmentId:
Home2TelephoneNumber:
HomeAddressCity:
HomeAddressCountry:
HomeAddressPostalCode:
HomeAddressState:
HomeAddressStreet:
HomeFaxNumber:
HomeTelephoneNumber:
IM1Address:
IM2Address:
IM3Address:
ItemId:-2147481593
JobTitle:
LastName:
Manager:
MiddleName:
MobileTelephoneNumber:1388600000
Nickname:
OfficeLocation:
OtherAddressCity:
OtherAddressCountry:
OtherAddressPostalCode:
OtherAddressState:
OtherAddressStreet:
PagerNumber:
RadioTelephoneNumber:
RingTone:\Extended_Rom\LS\疯狂笑蛙.mp3
Spouse:
Suffix:
Title:
YomiCompanyName:
YomiFirstName:
YomiLastName:



转换为:myText.csv
AccountName,Anniversary,AssistantName,AssistantTelephoneNumber,Birthday,Body,Business2TelephoneNumber,BusinessAddressCity,BusinessAddressCountry,BusinessAddressPostalCode,BusinessAddressState,BusinessAddressStreet,BusinessFaxNumber,BusinessTelephoneNumber,CarTelephoneNumber,Categories,Children,CompanyName,CompanyTelephoneNumber,CustomerId,Department,Email1Address,Email2Address,Email3Address,FileAs,FirstName,GovernmentId,Home2TelephoneNumber,HomeAddressCity,HomeAddressCountry,HomeAddressPostalCode,HomeAddressState,HomeAddressStreet,HomeFaxNumber,HomeTelephoneNumber,IM1Address,IM2Address,IM3Address,ItemId,JobTitle,LastName,Manager,MiddleName,MobileTelephoneNumber,Nickname,OfficeLocation,OtherAddressCity,OtherAddressCountry,OtherAddressPostalCode,OtherAddressState,OtherAddressStreet,PagerNumber,RadioTelephoneNumber,RingTone,Spouse,Suffix,Title,YomiCompanyName,YomiFirstName,YomiLastName
,,,,,,,,,,,,,,,其他,,M.其他,,,,,,,139邮箱,139邮箱,,,,,,,,,,,,,-2147481866,,,,,58139,,,,,,,,,,\Extended_Rom\LS\HELLO moto.mp3,,,,,,
,,,,,,,,,,,,,,,卫22,,卫22,,,,,,,艾小,艾小,,,,,,,,,,,,,-2147481593,,,,,13886000000,,,,,,,,,,\Extended_Rom\LS\疯狂笑蛙.mp3,,,,,,
最后完美解决,谢谢版主搞了一晚上,前后改了5次。
  1. @echo off
  2. ::文件名:telconvert.bat
  3. ::用法:
  4. ::文本文件扩展名固定为.txt,表格文件扩展名固定为.csv
  5. ::1.文本转换为表格
  6. ::  在命令行下输入:telconvert.bat 文本文件名 表格文件名
  7. ::  如:telconvert.bat contact.txt contact.csv
  8. ::  也可直接将文本文件拖至bat文件上,此时将自动生成同名表格文件(仅扩展名不同)
  9. ::2.表格转换为文本
  10. ::  在命令行下输入:telconvert.bat 表格文件名 文本文件名
  11. ::  如:telconvert.bat contact.csv contact.txt
  12. ::  也可直接将表格文件拖至bat文件上,此时将自动生成同名文本文件(仅扩展名不同)
  13. setlocal enabledelayedexpansion
  14. set "tab= "
  15. if "%1"=="" (set/p source=请输入要转换的文件名:) else set "source=%1"
  16. if "%source:~-4%"==".txt" (set destex=.csv&set go=1) else (
  17.   if "%source:~-4%"==".csv" (set destex=.txt&set go=2) else (
  18.     echo,源文件格式不正确!&pause>nul&exit/b
  19. ))
  20. if "%2"=="" (set "dest=%~n1%destex%") else set "dest=%2"
  21. if not %dest:~-4%==%destex% echo,目标文件格式不正确!&pause>nul&exit/b
  22. goto convert%go%
  23. :convert1
  24. echo,正在转换,请稍后...
  25. set n=
  26. (for /f "usebackq skip=1 tokens=1* delims=:" %%a in ("%source%") do (
  27.   if "%%a"=="----------" set/a n+=1
  28.   if !n! equ 0 (set/p=%%a,<nul) else goto :next1
  29. ))>%dest%
  30. :next1
  31. (for /f "usebackq tokens=1* delims=:" %%i in ("%source%") do (
  32.   if "%%j"=="" (set two=#) else set "two=%%j"
  33.   if not "%%i"=="----------" (set/p=!two!,<nul) else echo,
  34. ))>>%dest%
  35. start "" "%dest%"
  36. goto :eof
  37. :convert2
  38. echo,正在转换,请稍后...
  39. set/p head=<"%source%"
  40. set n=
  41. for %%a in (%head%) do set/a n+=1&set "head!n!=%%a"
  42. (for /f "usebackq skip=1 delims=" %%i in ("%source%") do (
  43.   echo,----------
  44.   set str="%%i"&set "str=!str:,=" "!"&set m=
  45.   for %%m in (!str!) do (
  46.     set/a m+=1
  47.     if "%%~m"=="#" (set show=) else set "show=%%~m"
  48.     if !m! leq %n% for %%n in (!m!) do echo,!head%%n!:!show!
  49.   )
  50. ))>%dest%
  51. echo,---------->>"%dest%"
  52. start "" "%dest%"
复制代码

作者: sxw    时间: 2011-10-29 23:43

请转为表格来编辑,编辑好后还原成这个样

是什么意思?
作者: broly    时间: 2011-10-30 02:26

  1. 'By Broly
  2. 'From http://bbs.bathome.net/
  3. Dim fso,f,choice
  4. Dim xlsPath,txtPath,strText,target
  5. xlsPath = "d:\myExcel.xls"
  6. txtPath = "d:\myText.txt"
  7. target = "d:\tatget.txt"
  8. choice = InputBox("1.TXT -> Excel" & vbCrLf & "2.Excel -> TXT","请选择")
  9. Set fso = CreateObject("Scripting.FileSystemObject")
  10. Select Case choice
  11. Case 1:
  12. If Not fso.FileExists(xlsPath) Then CreateXlsFile(xlsPath)
  13. strText = GetText(txtPath)
  14. WriteExcel xlsPath,strText
  15. MsgBox "Succeed."
  16. Case 2:
  17. Set f = fso.OpenTextFile(target,8,True)
  18. f.Write GetExcelInfo(xlsPath)
  19. f.Close
  20. Set f = Nothing
  21. MsgBox "Succeed."
  22. Case Else:
  23. MsgBox "Wrong choice."
  24. End Select
  25. Set fso = Nothing
  26. Function CreateXlsFile(Path)
  27. Dim objExcel
  28. Set objExcel = CreateObject("Excel.Application")
  29. objExcel.Workbooks.Add()
  30. objExcel.ActiveWorkbook.SaveAs Path
  31. objExcel.Quit
  32. Set objExcel = Nothing
  33. End Function
  34. Function GetText(Path)
  35. Set f = fso.OpenTextFile(Path,1,False)
  36. If Not f.AtEndOfStream Then
  37. GetText = f.ReadAll
  38. End If
  39. f.Close
  40. Set f = Nothing
  41. End Function
  42. Function WriteExcel(Path,strText)
  43. Dim oExcel,oWorkBooks,oWorkSheet
  44. Dim arr,i,arr2
  45. arr = Split(strText,vbCrLf,-1,1)
  46. Set oExcel = CreateObject("Excel.Application")
  47. Set oWorkBooks = oExcel.Workbooks.Open(Path)
  48. Set oWorkSheet = oWorkBooks.Sheets(1)
  49. oWorkSheet.Activate
  50. oWorkSheet.Columns(1).ColumnWidth = 45
  51. oWorkSheet.Columns(2).ColumnWidth = 35
  52. For i = 0 To UBound(arr)
  53. arr2 = Split(arr(i),":",-1,1)
  54. ReDim Preserve arr2(2)
  55. If InStr(arr2(0),"-----") = 0 And _
  56. arr2(0) <> "" _
  57. Then
  58. oWorkSheet.Cells(i+1,1).Value = arr2(0) & ":"
  59. Else
  60. oWorkSheet.Cells(i+1,1).Value = arr2(0)
  61. End If
  62. oWorkSheet.Cells(i+1,2).Value = arr2(1)
  63. Next
  64. oWorkBooks.Save
  65. oWorkBooks.Close
  66. oExcel.Quit
  67. Set oExcel = Nothing
  68. Set oWorkBooks = Nothing
  69. Set oWorkSheet = Nothing
  70. End Function
  71. Function GetExcelInfo(Path)
  72. Dim oExcel,oWorkBooks,oWorkSheet
  73. Dim i,strText
  74. Set oExcel = CreateObject("Excel.Application")
  75. Set oWorkBooks = oExcel.Workbooks.Open(Path)
  76. Set oWorkSheet = oWorkBooks.Sheets(1)
  77. oWorkSheet.Activate
  78. i = 1
  79. Do While oWorkSheet.Cells(i,1).Value <> ""
  80. strText = strText & vbCrLf _
  81. & oWorkSheet.Cells(i,1).Value _
  82. & oWorkSheet.Cells(i,2).Value
  83. i = i + 1
  84. Loop
  85. oWorkBooks.Close
  86. oExcel.Quit
  87. GetExcelInfo = strText
  88. Set oExcel = Nothing
  89. Set oWorkBooks = Nothing
  90. Set oWorkSheet = Nothing
  91. End Function
复制代码

作者: weichenxiehou    时间: 2011-10-30 09:19

回复 1# zhangop9
代码以“----------”作为判断标准,所以不要告诉我这个是你为了表达而刻意添加的。。。
  1. @echo off
  2. ::文件名:telconvert.bat
  3. ::用法:
  4. ::文本文件扩展名固定为.txt,表格文件扩展名固定为.xls
  5. ::1.文本转换为表格
  6. ::  在命令行下输入:telconvert.bat 文本文件名 表格文件名
  7. ::  如:telconvert.bat contact.txt contact.xls
  8. ::  也可直接将文本文件拖至bat文件上,此时将自动生成同名表格文件(仅扩展名不同)
  9. ::2.表格转换为文本
  10. ::  在命令行下输入:telconvert.bat 表格文件名 文本文件名
  11. ::  如:telconvert.bat contact.xls contact.txt
  12. ::  也可直接将表格文件拖至bat文件上,此时将自动生成同名文本文件(仅扩展名不同)
  13. setlocal enabledelayedexpansion
  14. set "tab= "
  15. if "%1"=="" (set/p source=请输入要转换的文件名:) else set "source=%1"
  16. if "%source:~-4%"==".txt" (set destex=.xls&set go=1) else (
  17.   if "%source:~-4%"==".xls" (set destex=.txt&set go=2) else (
  18.     echo,源文件格式不正确!&pause>nul&exit/b
  19. ))
  20. if "%2"=="" (set "dest=%~n1%destex%") else set "dest=%2"
  21. if not %dest:~-4%==%destex% echo,目标文件格式不正确!&pause>nul&exit/b
  22. goto convert%go%
  23. :convert1
  24. echo,正在转换,请稍后...
  25. set n=
  26. (for /f "usebackq skip=1 tokens=1* delims=:" %%a in ("%source%") do (
  27.   if "%%a"=="----------" set/a n+=1
  28.   if !n! equ 0 (set/p=%%a%tab%<nul) else goto :next1
  29. ))>%dest%
  30. :next1
  31. (for /f "usebackq tokens=1* delims=:" %%i in ("%source%") do (
  32.   if not "%%i"=="----------" (set/p=%%j%tab%<nul) else echo,
  33. ))>>%dest%
  34. start "" "%dest%"
  35. goto :eof
  36. :convert2
  37. set/p head=<"%source%"
  38. set n=
  39. for %%a in (%head%) do set/a n+=1&set "head!n!=%%a"
  40. (for /f "usebackq skip=1 delims=" %%i in ("%source%") do (
  41.   echo,----------
  42.   set str="%%i"&set "str=!str:%tab%=" "!"&set m=
  43.   for %%m in (!str!) do (
  44.     set/a m+=1
  45.     if !m! leq %n% for %%n in (!m!) do echo,!head%%n!:%%~m
  46.   )
  47. ))>%dest%
  48. echo,---------->>"%dest%"
  49. start "" "%dest%"
复制代码

作者: zhangop9    时间: 2011-10-31 16:11

回复 2# sxw


    就是在表格中编辑,编辑完后,还原成原来的坚表格。
作者: zhangop9    时间: 2020-2-29 19:41

回复 3# broly


以前看不明白,现在看明白了




欢迎光临 批处理之家 (http://bbs.bathome.net/) Powered by Discuz! 7.2