C# Excel Text to Column Problem

Question

Hi I've called the text to column function in C# but it didn't turned out the way I wanted it to be.

I have this data in the first cell of my worksheet.

Guest;0;12/10/2010 03:46:34 PM;66082

If I run Text to Column manually from Excel, I get.

Guest   0   12/10/2010 15:46    66082

However if I ran through my C# code, I get.

Guest   0   10/12/2010 15:46    66082

The Date format have switched from "DD/MM/YYYY" to "MM/DD/YYYY"

Here's my C# Code

((Range)ws.Cells[1,1]).EntireColumn.TextToColumns(
Type.Missing, Excel.XlTextParsingType.xlDelimited,
 Excel.XlTextQualifier.xlTextQualifierNone, Type.Missing,
 Type.Missing, true, Type.Missing,
 Type.Missing, Type.Missing,
 Type.Missing, Type.Missing,
 Type.Missing, Type.Missing);

I even recorded the Macro from Excel.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))

The only thing that I couldn't try out would be FieldInfo. Had no idea how to convert it to C#. Not much help from Google either. I'm kinda suspecting it is the xlGeneralFormat.

Any Ideas? Thanks.

object fieldInfo = new int[4,2] {{1,1},{2,1},{3,1},{4,1}};

Ok I realised that the problem does not lies with the FieldInfo, it runs fine even if I omit it out.

The problem lies with this.

wb.Close(true, saveFileDialog1.FileName, Type.Missing);

I saved it into another file. And found that the original file was in the correct format I wanted. But the newly saved file was in the wrong format.

I commented out the close line, and saved it manually. The old file is still in delimited form while the new file is still in the wrong format.

Apparently object FileName only works if it's "saveFileDialog1.FileName". In other words it works only if it's the same file name. I tried Type.Missing and @"C:\lalala.xls" and it didn't text to column.

I just realise the format in Text to column doesn't even work -.- Ok I give up. I'm going to convert DateTime to Excel Serial DateTime. An interesting problem nonetheless.

Answer 1

Re-recording your macro and setting the format of the column in question in the 3 step of the wizard gives you the correct values of the array.Using those values, I think the call should look like this:

int[][] fieldInfoArray = { new int[] { 1, 1 }, new int[] { 2, 1 }, new int[] { 3, 3 }, new int[] { 4, 1 } };    
((Range)ws.Cells[1,1]).EntireColumn.TextToColumns(
     Type.Missing, Excel.XlTextParsingType.xlDelimited,
     Excel.XlTextQualifier.xlTextQualifierNone, Type.Missing,
     Type.Missing, true, Type.Missing,
     Type.Missing, Type.Missing,
     Type.Missing,
     (object)fieldInfoArray,
     Type.Missing, Type.Missing);

Answer 2

It ask for an array and you have to send an array.

Array fieldInfoArray = new int[,] { { 1, 1 },  { 2, 1 }, { 3, 3 }, { 4, 1 } };

((Range)ws.Cells[1,1]).EntireColumn.TextToColumns(
Type.Missing, Excel.XlTextParsingType.xlDelimited,
 Excel.XlTextQualifier.xlTextQualifierNone, Type.Missing,
 Type.Missing, true, Type.Missing,
 Type.Missing, Type.Missing,
 FieldInfoArray, Type.Missing,
 Type.Missing, Type.Missing);

Optional you can use just the columns you want to change the format, the other ones will stay in "general". The first is the column number, the second the format.

Array fieldInfoArray = new int[,] { { 3, 3 } };

Answer 3

The book I have (Programming Excel with VBA and .NET, Webb & Saunders) simply describes FieldInfo as "An array that describes the data types of fields in the text". So it does appear teh solution should be there, but the macro does not include any date formatting.

The MSDN ref docs are more useful:

http://msdn.microsoft.com/en-us/library/04b02wh9%28v=VS.80%29.aspx

Yes it is an array: actually an array of value pairs (hence the array of array in your macro). The first value is the column number. The second value is XlColumnDataType constant. The definitions are here:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlcolumndatatype.aspx

Note the list of date formats. Looks like you need a xlDMYFormat or xlMDYFormat value for the third column?

If you know which column(s) contain the date (as the above assumes), then you could also explicitly set the format afterwards. Saves the faffing around with arrays although it does turn it into a two stage process.