Hello,
I try to import txt file with separator
Workbook workbook = new Workbook(strFile, opt);
I use TxtLoadOptions with PreferredParsers
My text file has header colums in the first row
How to indicate do not parse the first row ?
Thank you
Xavier
Could you please share your sample text file and sample code that you are using? We will check your issue soon. May be you could include double quotes around header (row) text in the file and then re-save the file before loading it into Aspose.Cells object model.
My text file : test1.txt
NOM;DATE;VALUE
Xavier;18/06/2023;test
Eric;25/07/2023;test2
My code :
string strFicSrc = @“C:\temp\test1.txt”;
string strFicDest = @“C:\temp\test1.xlsx”;
TxtLoadOptions opt = new TxtLoadOptions();
opt.Separator = ‘;’;
opt.ConvertNumericData = false;
opt.ConvertDateTimeData = true;
opt.PreferredParsers = new ICustomParser[]
{ new TextParser1(),
new DateParser1(),
new TextParser1()
};
opt.Encoding = System.Text.Encoding.UTF8;
opt.LanguageCode = CountryCode.France;
Workbook workbook = new Workbook(strFicSrc, opt);
workbook.Save(strFicDest, SaveFormat.Xlsx);
workbook.Dispose();
class DateParser1 : ICustomParser
{
public object ParseObject(string value)
{
DateTime myDate = DateTime.ParseExact(value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
return myDate;
}
public string GetFormat()
{
return "dd/MM/yyyy";
}
}
class TextParser1 : ICustomParser
{
public object ParseObject(string value)
{
return value;
}
public string GetFormat()
{
return "";
}
}
I have an exception because the first row is parsed
The value “DATE” (name of the second column) is not a valid DateTime on DateTime.ParseExact in DateParser1
There are some solutions for your situation:
- If there are no other special scenarios to be processed for your data and business, you need not use custom parser. Our built-in parse is enough for you to get the expected result:
TxtLoadOptions opt = new TxtLoadOptions();
opt.Separator = ';';
opt.Encoding = System.Text.Encoding.UTF8;
Workbook workbook = new Workbook(strFicSrc, opt);
- You may add one flag in your custom parser for the first line so you can skip it:
class DateParser1 : ICustomParser
{
private bool flag = true;
public object ParseObject(string value)
{
if(flag)
{
flag = false;
return value;
}
DateTime myDate = DateTime.ParseExact(value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
return myDate;
}
public string GetFormat()
{
return "dd/MM/yyyy";
}
}
- Enhance your custom parser to handle invalid data, you may check the value by optimised logic, or just catch the exception:
class DateParser1 : ICustomParser
{
public object ParseObject(string value)
{
try
{
DateTime myDate = DateTime.ParseExact(value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
return myDate;
catch
{
return value;
}
}
public string GetFormat()
{
return "dd/MM/yyyy";
}
}
- To manipulate your data with more flexibility, you may use LightCellsDataHandler instead of the ICustomParser:
TxtLoadOptions opt = new TxtLoadOptions();
opt.Separator = ';';
opt.Encoding = System.Text.Encoding.UTF8;
opt.ConvertDateTimeData = false;
opt.LightCellsDataHandler = new CsvParser();
Workbook workbook = new Workbook(strFicSrc, opt);
private class CsvParser : LightCellsDataHandler
{
public bool StartSheet(Worksheet sheet)
{
return true;
}
public bool StartRow(int row)
{
return true;
}
public bool ProcessRow(Row row)
{
return true;
}
public bool StartCell(int col)
{
return true;
}
public bool ProcessCell(Cell cell)
{
if (cell.Row > 0)
{
if (cell.Column == 1)
{
Style style = cell.GetStyle();
style.Custom = "dd/mm/yyyy";
cell.SetStyle(style);
cell.PutValue(DateTime.ParseExact(cell.StringValue,
"dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture));
}
}
return true;
}
}
With LightCellsDataHandler, you can manipulate every cell according to your special requirement. And you may visit here for more examples about using LightCells.
I understand
Thank you very much for your answer
Xavier