How to write C# script to handle double quotes in csv fields in SSIS

Question

My requirement is that I am getting a CSV source file like this:

ID,[%age],[name]
75,8,"Ander,Pat"

I want to create a dynamic SQL table from this csv like below:

ID,[%age],[name]
75,8,Ander,Pat (this should come in a single column)

The problem is that my C# code is reading the [name] as two different columns i.e Ander and Pat

Because File Delimiter = ','

Can you please help with what should I include in the c# code to handle this?

EDIT: Here is my code:

                string TableName = "";
                int counter = 0;
                string line;
                string ColumnList = "";
        

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {
                    if (counter == 0)
                    {
                        ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                        TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
                        string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]')";
                        CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]  Create Table " + SchemaName + ".[" + TableName + "]";
                        CreateTableStatement += "([" + line.Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
                        SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
                        CreateTableCmd.ExecuteNonQuery();

                        

                    }
                    else
                    {
                        string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
                        query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

Is there setting for TextQualifier? In your data you have " around the name, but if your code does not say the quotes are text qualifiers (surround the data and ignores commas in the middle) it will split them. If you identify the text qualifier, it should work (if that setting exists in the code you setup) — Brad, Dec 10 '20 at 21:05

apocalypse · Answer 1 · 2020-12-09T00:34:59.543

1

You need to use some library, like CsvHelper

Dealing with CSV files using string.Replace is just nonsense.

If you can't install 3rd party lib, maybe try this (I didn't tested it): StackOverflow Link

var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(file);
parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
parser.SetDelimiters(new string[] { "," });

while (!parser.EndOfData)
{
    string[] row = parser.ReadFields();
    /* do something */
}

edited Dec 09 '20 at 00:34

answered Dec 07 '20 at 20:09

apocalypse

5,764
9
47
95

What if I don't have the option to install any library like this ? I am mainly looking for a solution from coding perspective – sword_s Dec 08 '20 at 16:51

How to write C# script to handle double quotes in csv fields in SSIS

1 Answers1