How to use Workbooks.OpenText in the xlwings API way

The OpenText member of the Workbooks object in the Excel object model is a method used to import and parse a text file into a new Excel workbook. This is particularly useful for automating the loading of data from delimited text files (like CSV or TSV) or fixed-width text files directly into Excel without manual intervention. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing precise control over the import process.

Syntax in xlwings:
The xlwings API call follows the pattern: xlwings.Book.api.OpenText(...). However, since OpenText is a method of the Workbooks collection, it is typically used to create a new workbook. In xlwings, you can access it via the Excel application object. The general syntax is:

app = xw.App(visible=False) # Create an invisible Excel instance
app.api.Workbooks.OpenText(Filename, ...)

The OpenText method has numerous parameters to customize the import. Key parameters include:

• Filename (required, String): The full path and name of the text file to import.
• Origin: Specifies the file origin (e.g., xlWindows for Windows or xlMacintosh for Mac). Often set to xlWindows (value 437) by default.
• StartRow (Long): The starting row for parsing (default is 1).
• DataType (XlTextParsingType): Sets how columns are parsed. Use xlDelimited (value 1) for delimited files (like CSV) or xlFixedWidth (value 2) for fixed-width files.
• TextQualifier (XlTextQualifier): Specifies the text qualifier character, such as xlTextQualifierDoubleQuote (value 1) for double quotes.
• ConsecutiveDelimiter (Boolean): True to treat consecutive delimiters as one.
• Tab, Semicolon, Comma, Space, Other, OtherChar: Boolean parameters to set delimiters. For example, set Comma=True for CSV files. If Other=True, specify the character in OtherChar.
• FieldInfo (Array): An array of arrays specifying the data type and width for each column. For delimited files, it often uses xlGeneralFormat (value 1). Example: [[1, 1], [2, 1]] sets the first two columns to general format.

Example:
Here is an xlwings API code example that imports a comma-delimited CSV file, treating consecutive commas as one delimiter, and starting from the first row:

import xlwings as xw

# Start Excel in the background
app = xw.App(visible=False)

# Define the text file path
file_path = r'C:\Data\sales.csv'

# Open the text file using OpenText
# Parameters: Filename, StartRow=1, DataType=xlDelimited, Comma=True, ConsecutiveDelimiter=True
workbook = app.api.Workbooks.OpenText(
Filename=file_path,
Origin=437, # xlWindows
StartRow=1,
DataType=1, # xlDelimited
TextQualifier=1, # xlTextQualifierDoubleQuote
ConsecutiveDelimiter=True,
Comma=True,
FieldInfo=[[1, 1], [2, 1], [3, 1]] # Set first three columns to general format
)

# Save the workbook as an Excel file
workbook.SaveAs(r'C:\Data\sales_imported.xlsx')
workbook.Close()
app.quit()