'Excel: Text -to-columns via C#

Im making application where I need to separate some string from one column to others. Delimiter is Space. I tried to google some solutions, but I found almost nothing. Just one script here on stackoverflow seems to work for me.. I also tried to record macro and those two scripts put together. But i got error "SafeArrayTypeMismatchException".. Program dont accept true value on Space delimiter and also Array is not set as correct type. Do you have any idea what can help? Here is my code: (Copying last two blocks from N column to columns O and P)

    private void text_to_columns()
    {
        int[][] field_info = { new int[] { 1, 9 }, new int[] { 2, 9 }, new int[] { 3, 9 }, new int[] { 4, 9 }, new int[] { 5, 1 }, new int[] { 6, 1 } };

        Excel.Range rng_Status = xlWorkSheet.get_Range("N:N",Type.Missing);
        rng_Status.TextToColumns(xlWorkSheet.get_Range("O:O",Type.Missing), Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, true, false, false, false, true, false, false, (object)field_info, false, false, true);
    }

Thank you very much for your answer and ideas. Alan

c++sharp.netexcelfield


Solution 1:[1]

This should work:

Excel.Application oXL;
Excel._Workbook dataB;
Excel._Worksheet dataS;
Excel.Range oResizeRange;

string path = pathToCSV;


oXL = new Excel.Application();
dataB = oXL.Workbooks.Open(path, 0,
    false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value,
    false, false,Missing.Value, false, false, false);
dataB.Application.DisplayAlerts = false;
oXL.Visible = true;
dataS = (Excel._Worksheet)dataB.ActiveSheet;



dataS.get_Range("A1",("A" +   
    dataS.UsedRange.Rows.Count)).TextToColumns(Type.Missing, 
    Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, 
    Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone, true,
    Type.Missing,Type.Missing, false, true, Type.Missing,
    " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Solution 2:[2]

This will delimit any text in the column you choose.
Here I am delimiting by comma.

public static void SetDelimited(string MyRange) // MyRange = "A:A" or whatever.
{
    sheet.Range[range].TextToColumns(sheet.get_Range(MyRange , Type.Missing),
    XlTextParsingType.xlDelimited, 
    XlTextQualifier.xlTextQualifierDoubleQuote, 
    true,        // Consecutive Delimiter
    Type.Missing,// Tab
    Type.Missing,// Semicolon
    true,        // Comma
    false,       // Space
    Type.Missing,// Other
    ",",         // Other Char
    Type.Missing,// Field Info
    Type.Missing,// Decimal Separator
    Type.Missing,// Thousands Separator
    Type.Missing);/ Trailing Minus Numbers
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 javon27
Solution 2 user2673536

Related Questions