将管道分隔的文件转换为.xls [英] Convert pipe-delimited files to .xls

问题描述

我正在尝试使用批处理文件和vbscript将管道分隔的文件转换为xls(Excel).不幸的是，我的"output.xls"文件仍在表中显示管道定界符，并且数据没有组织.

I'm trying to convert pipe-delimited files to xls (Excel) with batch file and vbscript. Unfortunately, my "output.xls" file is still showing the pipe delimiter in the table and the data are not organized.

srccsvfile = Wscript.Arguments(0)  
tgtxlsfile = Wscript.Arguments(1)  

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    objExcel.Columns(intColumns).AutoFit()
'Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
     .SplitColumn = 0
     .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15
'-0.249977111117893


'Save Spreadsheet, 51 = Excel 2007-2010 
objWorksheet1.SaveAs tgtxlsfile, 51

'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing

source: http://www.tek-tips.com/viewthread. cfm?qid = 1682555

推荐答案

管道不等于逗号，Excel本身知道如何处理CSV，但不知道如何处理管道.

Pipe doesn't equal Comma, Excel natively knows what to do with a CSV, but not with Pipe.

一切都不会丢失，手动记录打开文件的操作，打开突出显示的列A，然后单击数据/文本到列"，选择定界"，然后在其他"框中放一个管道，然后单击下一步，选择列格式(如果您需要喜欢邮政编码和电话号码，则可以将数字格式化为文本)，然后单击完成.

All is not lost, record your actions opening the file manually, once open highlight column A and click Data / Text To Columns, choose delimited and in the "other" box put a pipe then click next, choose the column formats (great to format numbers as text if you need to like Postcodes and phone numbers) then click finish.

现在停止记录器，并查看其生成的代码.将其移植到脚本中的Excel对象.

Now stop the recorder and look at the code it generated. Port this over to your Excel object in your script.

这篇关于将管道分隔的文件转换为.xls的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！