setting schema to nullable

[dkekish]

hoping to get a little help, because I'm really confusing myself on some of this.

The library is great and fast, however I'm seeing the error:
The input string '' was not in a correct format

which i think is due to the fact that the csv file I'm importing, will represent empty null numeric values as an empty string.

how can I easily and quickly set those numeric fields to accept null values for an empty string?

the code is

                var cmd = conn.CreateCommand();
                cmd.CommandText =
                    $"select top 0 {schemaSql} from {destinationCatalog}.{destinationSchemaName}.{destinationTableName}";
                var reader = await cmd.ExecuteReaderAsync(_cancellationToken);
                var tableSchema = await reader.GetColumnSchemaAsync(_cancellationToken);

                await reader.CloseAsync();
                await conn.CloseAsync();

                var pool = new Sylvan.StringPool();
                var options =
                    new CsvDataReaderOptions
                    {
                        Schema = new CsvSchema(schema),
                        BufferSize = 128000,
                        HasHeaders = true,
                        Culture = CultureInfo.InvariantCulture,

[MarkPflug]

Can you create github gist that contains a full repro? It isn't entirely clear to me from your description what the issue is. I suspect that the SQL result set that you are using to define the schema is expecting some numeric column to be non-nullable, where the csv data contains null (empty) values.

[dkekish]

doing this as a gist would be a large undertaking so I'll try and explain it better. Here is a larger code segment

            // Get the schema for the target table
            var cmd = conn.CreateCommand();
            cmd.CommandText =
                $"select top 0 {schemaSql} from {destinationCatalog}.{destinationSchemaName}.{destinationTableName}";
            var reader = await cmd.ExecuteReaderAsync(_cancellationToken);
            var tableSchema = await reader.GetColumnSchemaAsync(_cancellationToken);

            await reader.CloseAsync();
            await conn.CloseAsync();

            var pool = new Sylvan.StringPool();
            var options =
                new CsvDataReaderOptions
                {
                    Schema = new CsvSchema(tableSchema),
                    BufferSize = 128000,
                    HasHeaders = true,
                    Culture = CultureInfo.InvariantCulture,
                    CsvStyle = CsvStyle.Standard,
                    HeaderComparer = StringComparer.CurrentCultureIgnoreCase,
                    OwnsReader= true,
                    StringFactory = pool.GetString,
                };
            string file = string.Empty;
            try
            {
                file = GetFileFromPath(importMessage.FilePath, importMessage.FileName);
                if (file == string.Empty)
                    throw new Exception($"File not found for processing {importMessage.FilePath}, {importMessage.FileName}");

                await using var csv = await CsvDataReader.CreateAsync(file, options);

                //bulk copy does not have an option to set a tablelock with out a transaction by passing a connection object
                //so we get the connection string from it,  it should point currently at the stage database.
                var bcp = new SqlBulkCopy(_configuration.GetConnectionString("Staging"), SqlBulkCopyOptions.TableLock)
                {
                    BatchSize = 50000,
                    BulkCopyTimeout = 0,
                    DestinationTableName = $"{DestinationDataSchema}.{destinationTableName}",
                    EnableStreaming = true,
                    NotifyAfter = 100000,
                };
                bcp.SqlRowsCopied += OnSqlRowsCopied;
                foreach (var column in tableSchema)
                    bcp.ColumnMappings.Add(column.ColumnName, column.ColumnName);

                await bcp.WriteToServerAsync(csv, _cancellationToken));

                bcp.Close();

The table its going into looks like
CREATE TABLE [stage].[myTable]
(
[Column1] [BIGINT] NULL,
[Column2] NCHAR NULL,
[Column3] [BIGINT] NULL,
[Column4] [BIGINT] NULL,
[Column5] NVARCHAR NULL,
[Column6] DATETIME2 NULL,
[Column7] NCHAR NULL,
[Column8] NCHAR NULL,
[Column9] NCHAR NULL,
[Column10] DATETIME2 NULL,
[Column11] NVARCHAR NULL,
)

and the datafile looks like this
COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6,COLUMN7,COLUMN8,COLUMN9,COLUMN10,COLUMN11
"300000024000001","R","","2037","User1","6/7/19 14:02","Y","Y","N","","Alphanumeric Value 1"
"300000024000002","A","","2037","User1","","Y","Y","Y","","Alphanumeric Value 2"
"300000024000003","L","","","User2","6/10/19 14:20","Y","Y","Y","","Alphanumeric Value 3"

What I think is occurring is that Column3 is coming in with a "" and its throwing a "The input string '' was not in a correct format" error, but I may be mistaken.

[MarkPflug]

Got it, thanks. I think I'd made a philosophical decision that a quoted field should never be identified as null. However, I now think that only makes sense when dealing with strings. If the schema indicates that the column is a non-string type, then identifying an empty quoted field as null seems reasonable. I should be able to release an update with this change in the next few days.

[dkekish]

that would be fantastic, Thank you very much.

I have one more issue, I will occasionally (Lord I wish I had control on the CSV creation process), get a value with an Exponent, i.e. I'll get "1.3199920E6", which of course is really just 131999.20. Any chance this is supported? I'd like to have any data conversion handled on the bulk insert if possible.

[MarkPflug]

If you're trying to process it as a floating point number (double), then it shouldn't be a problem. However, integer processing doesn't currently allow this format.

[dkekish]

I see, I have a small test app and had the value as a decimal, and it didn't like it, as a double however it does work. Thanks again!

[MarkPflug]

Pushed Sylvan.Data.Csv 1.2.5 to nuget which should fix this issue.

[dkekish]

I pulled the nuget yesterday and imported about 38 million rows with it, no errors. Thank you very much!