How to apply TxtLoadOptions to some certain part of incoming data?

I have a task to convert data form CSV into Excel table. Some columns of numeric data supposed to be taken normally (dates, prices etc) , other columns of mixed type data (i.e. “ABC123”, “00012345”, “6789”) supposed to be taken as text without any conversion. I know that TxtLoadOptions.ConvertNumericData could be set to false to turn off the conversion. Then object of TxtLoadOptions is applied as a parameter of the method Cells.ImportCSV(). It will have an impact on all incoming data.
Is it possible to apply TxtLoadOptions to some certain part of incoming data (i.e. a column), not to whole table?

@Arty,
Thank you for your query.
You my please try the PreferredParsers to parse different data types as per the requirement. Following article contains a working example and sample files as well for your reference. Please give it a try and share the feedback.

Opening CSV file using Preferred Parsers

@ahsaniqbalsidiqui
That’s interesting idea to use PreferredParsers for some cases of data format, but I’m looking for a way how to disable numeric data conversion in some local range of cells (i.e. in the certain column). For example, the input data like “000123” in the column A will be converted as number 123, but in the column B the same data will be taken as a string “000123”. Moreover, I cannot customize cell formatting of the column B in Excel template like “000000” or something, because the data incoming to this column might be various (i.e. “ABC”, “ABC123”, “000123”, “6789”) and they supposed to stay unchanged.

@Arty,
I think this is not possible to treat numeric string value as string in one column and treat numeric string as integer in some other column. Could you please perform the same task in MS Excel? If yes, please share the steps with us for our analysis. We will provide you assistance to achieve the same using Aspose.Cells.

@ahsaniqbalsidiqui,
I think, it is possible to customize import from CSV to Excel.
During the import process, call Power Query Editor, where you can manage incoming data for each column. Right click on the column header, then select Change Type in the context menu. Thus, some numeric data may be taken as text, other as integer (decimal, currency, binary etc.). It would be great, if Aspose let the customers to manage importing data like Excel does.

@Arty,
We have reviewed your requirements but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47307 – Option to set type of each individual column while importing data from CSV

@Arty,
About the logged ticket, we need some more details. As you have mentioned, TxtLoadOptions.PreferredParsers gives user the ability to handle different data and formatting conversions for different columns. Please elaborate bit more about the trouble to achieve your goal with TxtLoadOptions.PreferredParsers and we will look into it and provide solution if possible.

Hi @ahsaniqbalsidiqui,
Well, I’ll try to describe my case in details.
I have some given CSV data in two columns. All data in CSV file come as text strings. I need to convert these data into Excel table.

"Index","Signature"
"000123","000123"
"100","12345"
"007","ABC123"

Column A has numeric indexes, which supposed to be taken as integer numbers. Column B has mixed data, let’s call them “Signature”, which supposed to be taken as string of text. Some input data in column B might look the same as in column A (i.e. A : 000123,
B : 000123), but they should have different type and format in Excel table (i.e. A : 123, B : “000123” ). Using default parser, I will get the same result in both columns (A : 123, B : 123), which is not what I need. Could you explain to me, what a custom parser should I create to set it in TxtLoadOptions.PreferredParsers to get an acceptable result?

Expected result:
Example expected result.jpg (5.1 KB)

Actual result:
Example actual result.jpg (5.2 KB)

@Arty,
Thank you for the detailed information. We have noted it and will share our feedback after detailed analysis.

@Arty,
Please try the following sample code and share the feedback. Note that for each column you have to set the preferred parser in order of corresponding columns in the CSV file.

public static void Cells_211606_7()
{
    // Initialize Text File's LoadFormat
    LoadFormat oLoadFormat = LoadFormat.CSV;

    // Initialize Text File's Load options
    TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(oLoadFormat);
    // Specify the separatot character
    oTxtLoadOptions.Separator = Convert.ToChar(",");

    // Set the preferred parsers
    oTxtLoadOptions.PreferredParsers = new ICustomParser[] { new IndexParser(), new SignatureParser() };

    // Initialize the workbook object by passing CSV file and text load options
    Workbook oExcelWorkBook = new Aspose.Cells.Workbook("sample.csv", oTxtLoadOptions);

    // Save the workbook to disc
    oExcelWorkBook.Save("outputsamplePreferredParser.xlsx");
}


   class IndexParser : ICustomParser
    {
        public object ParseObject(string value)
        {
            if (value != "Index")
                return int.Parse(value);
            else
                return value;
        }
        public string GetFormat()
        {
            return "";
        }
    }

    class SignatureParser : ICustomParser
    {
        public object ParseObject(string value)
        {
            return value;
        }
        public string GetFormat()
        {
            return "";
        }
    }

sample.csv.zip (825 Bytes)
outputsamplePreferredParser.xlsx.zip (6.2 KB)

Hi @ahsaniqbalsidiqui,
Thank you for the explanation. Actually, I code on Java, but anyway your examples are really helpful. Now I understand, the order of elements in ICustomParser[] array should be according to the order of input CSV-file columns. Right?
One more thing. I don’t completely understand how method getFormat() works. Does it return a format which will be set up for each cell of the column in the output Excel table? Could you give me more examples with various formats please?

@Arty,
Thank you for the feedback and your understanding is right that order of elements in ICustomParser[] array should be according to the order of input CSV-file columns.

Regarding the getFormat(), we are gathering more details and will write back here soon to share our feedback.

@Arty,
About getFormat(), if you have specified that the original formatting of the parsed data also should be applied to corresponding cells (TxtLoadOptions.LoadStyleStrategy), then the string returned by ICustomParser.GetFormat() will be set to Style.Custom for the cell. So, the value of ICustomParser.GetFormat() should be the custom pattern for formatting a cell, just the same thing that you use to format one cell by setting Style.Custom for one cell in program.