Need Help Declaring Array(s) for Workbooks.OpenText(Fieldinfo)

NCGordon

I wish to open ".csv" files using a VSTO Excel 2003 template. All the files have 4 columns each, and all should be set as "XlColumnDataType.xlTextFormat.

I'm stumbling with the FieldInfo parameter of the Workbooks.OpenText method. The documentation says:

FieldInfo: Optional XlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

(http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext(VS.80).aspx)

1: How does one do that (I can't find an example of code for this.)

Here's what I have so far:

If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

'Other code goes here ...

Dim strCsvFullName As String = OpenFileDialog1.FileName

Dim xlCsvText As Excel.XlTextParsingType = _

Excel.XlTextParsingType.xlDelimited

Dim xlText As Excel.XlColumnDataType = _

Excel.XlColumnDataType.xlTextFormat

Globals.ThisWorkbook.Application.Workbooks.OpenText( _

Filename:=strCsvFullName, _

StartRow:=1, _

DataType:=xlCsvText, _

ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, _

Comma:=True, Space:=False, Other:=False, _

FieldInfo:=xlText )

End If

2: If the number of columns weren't known ahead of time, how would one write a function to find the number of "two-element arrays"

For reference, here's what the code looks like in VBA:

Workbooks.OpenText(Filename:= _

vrtSelectedItem, Origin:=-535, StartRow:=1, _

DataType:=xlDelimited, _

TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, _

Comma:=True, Space:=False, _

Other:=False, _

FieldInfo:=Array(Array(1, 2), Array(2, 2), _

Array(3, 2), Array(4, 2)), TrailingMinusNumbers:=True)

Thanks,

Noel C. Gordon.

Dennis Wallentin

Hi,

Although I'm not a friend of the built-in macro recorder in Excel it can help You with the parameters to open CSV files. However, see if the following snippet VB code can solve it:

Globals.ThisWorkbook.Application.Workbooks.Open(Filename:="C:\Data.csv", Format:=6, Delimiter:=",")

NCGordon

Thank you, Dennis, for the suggestion.

I did finally figure out the answer to the FieldInfo problem, but only by specifying the number of columns. It's not the most efficient approach, but it works.

I'd like to make it more generic: If anyone has a suggestion on how to programmatically find the upperbound of the number of comma-delimited columns in the .csv file, please let me know.

Here's the solution:

Code Snippet

Dim xlCsvText As Excel.XlTextParsingType = _
Excel.XlTextParsingType.xlDelimited

Dim xlText As Excel.XlColumnDataType = _
Excel.XlColumnDataType.xlTextFormat
Dim myinfo(,) As Object = New Object(,) _
{{1, xlText}, {2, xlText}, {3, xlText}, {4, xlText}}
Globals.ThisWorkbook.Application.Workbooks.OpenText( _
Filename:=strFullPathToFile, _
StartRow:=1, _
DataType:=xlCsvText, _
ConsecutiveDelimiter:=False, _
Comma:=True, _
FieldInfo:=myinfo)

P.S.: Why was it necessary Excel insisted on importing legislative bill numbers as dates, so "05-1" became "May 01", and so forth.

Thanks,

Noel C. Gordon

Dennis Wallentin

Hi Noel,

Excel interpretate "05-1" as a date and therefore gives "May 01". Would an ADO approach be of interest Instead of opening the file in Excel we treat the CSV file as a "database" source and query it via SQL and in return retrieve it's data that is "dumped" into a worksheet.

An alternative approach would also be to open / close the CSV file (in an old fashion DOS way), grab the number of columns and use the number as a parameter to Your present code.

NCGordon

Hi Dennis:

Another good suggestion. In the long run, I think ADO is the better way to go. That way I can provide the editing interface for the data inside a related Windows app I wrote that currently offers textual editing for the data, but would benefit from a database approach, tabular/grid presentation and editing interface.

Unfortunately, that will have to wait a bit as I have a flood of new requests this week. (RAD sometimes means 'Yikes!')

Regarding the solution I created for the FieldInfo problem: I had searched for clues and found a number of posts in various fora/forums asking the same or similar question. Do you think my solution might be useful for other folks Would you change it in any way

Dennis Wallentin

Hi Noel,

Sharing solutions is always welcoming

I've added CSV files & VSTO to my "To post" List for my blog which explicit works with VSTO & Excel. So when it has been posted there I will drop a note in this thread.

NCGordon

Please note the changes I made to my code snippet above to make the solution more generic and complete for use as a model. I added

• one variable definition/initialization: "xlCsvText", and
• one modified variable name for the full file path string: "strFullPathToFile"

Thanks,

Dennis Wallentin

Hi Noel,

I've made a post at my blog that shows how to work with CSV files with ListObject Control in VSTO: CSV, ListObject and VSTO

Let me know if it workable for You.

NCGordon

Hi Dennis:

I'll check it out. If possible, would you provide the source code in text format

Thanks, Noel.

Dennis Wallentin

Noel - It's now available.