quoted text errors out

[BabakRabie]

I have this row in a pipe delimited txt file:

16417|S001|465378|2023-05-25|Product|CGC||"CANADIAN" CIGARETTES|EA|1.0000|20.100|20.10000||20.1|20.1|1|||16.754|16.75400|16.754|16.75400|0.00000|2023-05-24|||GC|GROCERIES|||CIGARETTES||EA|EA|20.10|16.754|EA|EA

It errors out on this field: "CANADIAN" CIGARETTES

there is only a space between "CANADIAN" and CIGARETTES

The goal is to read the file, add lineNumber and fileName at the end of each line and write it back to a txt file in another folder so it can be consumed as External Table by SQL Server. But it errors out with this:
Exception thrown: 'Sylvan.Data.Csv.CsvFormatException' in Sylvan.Data.Csv.dll

It doesn't tell me at what line in the text file it happens so had to eliminate records until I find the issue. Is there a way to find the line?

Also I don't want any escape characters to be added. Just want the fields to get written as they are. Here is my code (not adding the lineNumber and fileName yet):

` private void button1_Click(object sender, EventArgs e)
{

        var start = DateTime.Now;
        Console.WriteLine($"Start {start}");

        string[] files = Directory.GetFiles("C:\\SourceFolder\\", "*.txt");
        CsvDataReader dr;

        CsvDataReaderOptions readerOptions = new CsvDataReaderOptions();
        readerOptions.Delimiter = '|';
        readerOptions.Comment = '\0';

        CsvDataWriterOptions writerOptions = new CsvDataWriterOptions();
        writerOptions.Delimiter = '|';
        writerOptions.Comment = '\0';

        foreach (string file in files)
            {
            try
            {
                dr = CsvDataReader.Create(file, readerOptions);
            }
            catch
            {
                Console.WriteLine($"file 1: {file}");
                continue;
            }

            using (var csvWriter = CsvDataWriter.Create(file.Replace("SourceFolder", "DestFolder"),writerOptions))
                {
                    try
                    {
                        csvWriter.Write(dr);
                    }
                    catch
                    {
                        Console.WriteLine($"file 2: {file}");
                    }

                }
            }
        Console.WriteLine($"duration {DateTime.Now - start}");

        MessageBox.Show("Finished!");

    }
}

`
so 3 things:

• why does it error out on that line and how to solve it?
• how to find the line that caused the exception in the code?
• how to add line number , the quickest way, before writing it back to destination folder?

[MarkPflug]

Your file is a non-standard format and needs special configuration.

The following code should do what you want:

using Sylvan.Data;
using Sylvan.Data.Csv;
using System.Data.Common;

var filename = "MyFileName.csv";

// example data
var data = "a|b|c\nx|\"y\" y|z\n";
var opts =
    new CsvDataReaderOptions {
        CsvStyle = CsvStyle.Escaped,
        Escape = '\0',
        Quote = '\0',
    };

var r = CsvDataReader.Create(new StringReader(data), opts);

// create a new data reader with the filename and row number attached.
DbDataReader r2 = r.WithColumns(
    new CustomDataColumn<string>("Filename", _ => filename),
    new CustomDataColumn<int>("Row", _ => r.RowNumber)
    );

var wo = new CsvDataWriterOptions
{
    Style = CsvStyle.Escaped,
    Escape = '\0',
    Quote = '\0',
    Delimiter = '|',
};

using var x = CsvDataWriter.Create("dump.csv", wo);
x.Write(r2);

[BabakRabie]

Perfect! Thanks.

The only issue that I have is r.WithColumns() method is not available for me. Wondering if it has to do with the version of Sylvan I have installed (1.3.3.0). My .NET Framework is 4.7

[MarkPflug]

Make sure you are also referencing the Sylvan.Data package. That method is not CSV-specific, so they live in a different package.

[BabakRabie]

I do have Sylvan.Data; directive but it is greyed out by VS and when I hover over it it says "IDE0005: Using directive is unnecessary". This doesn't seem an error.

[MarkPflug]

Don't know what to tell you. This compiles against net47 on my machine. Just to clarify, you need to reference both the Sylvan.Data.Csv and Sylvan.Data nuget pacakges. Adding the using statement isn't enough, you need to have package references for both packages:
https://www.nuget.org/packages/Sylvan.Data
https://www.nuget.org/packages/Sylvan.Data.Csv

[BabakRabie]

you were right. I didn't have Sylvan.Data added in Nuget :-( Thank you for quick replies!