Progress 4GL中使用COM-Handle生成Excel时设置分隔符求助

Hey there! Let's get that comma-separated data split into proper columns instead of piling up in one. Here are two solid approaches to resolve this—one for quick fixes on existing files, and another to handle it directly in your COM-Handle code so you don't have to manually adjust later.

Option 1: Manual Text to Columns in Excel (For Existing Files) #

If you just need to fix the already-generated Excel sheet, this is the fastest way:

• Select the entire column that holds all your comma-separated content
• Go to the Data tab in Excel's ribbon, click Text to Columns
• Step 1 of the wizard: Choose Delimited and click Next
• Step 2: Check the box for Comma (double-check it's the English/half-angle comma if your data uses that) — you'll see a preview of your data splitting into columns right away. If your data has quoted fields (like "Jane Smith",29), also set the Text qualifier to " to avoid splitting content inside quotes.
• Step 3: Adjust column data formats (e.g., keep as General or set to Text for values with leading zeros) and click Finish

Option 2: Automate Splitting via COM-Handle Code (Prevent the Issue Upfront) #

To make sure your Excel sheet generates with split columns from the start, update your COM-Handle code to specify the comma delimiter when importing the .txt files. Here are examples for common scripting/languages:

VBScript Example #

' Initialize Excel objects
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

' Import the TXT file with comma delimiter configured
Set qt = objWorksheet.QueryTables.Add( _
    Connection:="TEXT;C:\Path\To\Your\File.txt", _
    Destination:=objWorksheet.Range("A1")
)
With qt
    .TextFileParseType = 1 ' xlDelimited constant (value = 1)
    .TextFileCommaDelimiter = True ' Enable comma as separator
    .TextFileTextQualifier = 1 ' xlTextQualifierDoubleQuote (handles quoted fields)
    .Refresh ' Execute the import
End With

' Save and clean up
objWorkbook.SaveAs "C:\Path\To\Your\Output.xlsx"
objExcel.Quit
Set objExcel = Nothing

Python (win32com.client) Example #

import win32com.client as win32

# Launch Excel and create a workbook
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
ws = wb.Worksheets(1)

# Set up query table to import TXT with comma delimiter
qt = ws.QueryTables.Add(
    Connection="TEXT;C:/Path/To/Your/File.txt",
    Destination=ws.Range("A1")
)
qt.TextFileParseType = 1  # xlDelimited
qt.TextFileCommaDelimiter = True
qt.TextFileTextQualifier = 1  # Use double quotes as text qualifier
qt.Refresh()

# Save and close
wb.SaveAs("C:/Path/To/Your/Output.xlsx")
excel.Quit()

Quick Notes #

• Double-check that your .txt files use half-angle (English) commas as separators—if they use full-angle (Chinese) commas, swap the delimiter setting to match in the wizard or code.
• If your data has other separators mixed in (like tabs), you can enable multiple delimiters in the Text to Columns wizard or code.

内容的提问来源于stack exchange，提问作者Nidhi