adamfoneil / excel2sqlserver.library

Library for importing Excel spreadsheets into SQL Server tables

I'm into C#, SQL Server, modern .NET, Blazor, Razor, DigitalOcean, and Azure. I love relational data access, Dapper, and I make a couple Dapper extension libraries:

• Dapper.Entities, latest repository-based library for Dapper
• Dapper.Repository, a repository pattern approach to Dapper
• Dapper.QX, for making inline SQL better and testable

Along with some SQL Server-related libraries and tools:

• BackgroundService.Extensions cronjob and queue support for .NET web apps
• Ensync, a SQL schema diff/merge command-line tool, reboot of ModelSync
• Zinger, a SQL client sort of like SSMS specifically for C# productivity. Check out its Data Migrator feature and its SqlSchema library. Zinger2 in progress
• SqlServer.LocalDb, an integration testing library for SQL Server
• Excel2SqlServer, an Excel import library
• DbCache for throttling API calls to 3rd party services
• DataTables.Library has some small ADO.NET helpers

I also work with Blazor a lot. Most of my work is closed-source, but I have a couple things in progress that are public:

• ApiAuthDemo demonstrates a Blazor WASM app using APIs that require authorization
• LiteInvoice3 is a freelancer's invoicing app, testing Blazor WASM + Server hybrid architecture
• PayPalComponenets.RCL is a miniature PayPal IPN testing dashboard with a Pay Now button component.
• UserVoice.RCL is a user feedback and acceptance test portal.
• GitHubIssues.RCL lets users see your GitHub Issues within your application. Also its standalone GitHub client

I have some closed-source products:

• Sendhook.net for capturing inbound email as HTTP posts at your own endpoints, uses Azure functions, queues, and blob storage. (Note this app is currently stopped, but feel free to ask me about it.)
• SqlChartify for writing queries and getting charts made with ChartJS.

Some other odds and ends:

• QuestPdfUtil a very lightweight HTML -> PDF converter
• RoslynMarkdowner, a utility for generating C# documentation as markdown. I use this whenever I want to provide a reference of methods and parameters for my libraries.
• In ChunkUpload, I sketched out an approach to supporting large file uploads. A NuGet package came out of this that I'm using in other projects now.

WinForms stuff:

• AzDeploy, my continuous deployment mechanism for WinForms apps
• AOLicensing for desktop app gatekeeping and licensing
• ImgCloudPaste, for pasting images and getting screenshot URLs using Azure storage

excel2sqlserver.library's Issues

ability to override/modify generated identity column

There's an identity column with hardcoded name Id that's added to the import table. It would be nice, I believe, if you could modify this or perhaps suppress it entirely. The options maybe should be

• default: Id
• include table name: {TableName}Id
• arbitrary name: any name you like

Something like that

auto trim feature

Ability to call LTRIM(RTRIM()) on incoming data to remove spaces that are (as far as I can imagine) always unwanted.

async support would be nice

ability to reference specific worksheets in an import would be nice

Will you ever make this transactional?

auto trim feature doesn't address non-printing characters

Today I noticed that my trim logic LTRIM(RTRIM()) isn't working in the one case where I needed it to because the unwanted chars are actually non-printing. SQL Server doesn't seem to support regex directly

[^\x00-\x7F]+

surface validation features better

There are some interesting data validation features in this library that aren't publicized or surfaced very well: ValidateColumnTypes and ValidateSqlServerTypeConversionAsync.

Not sure if simple readme inclusion is enough or integrating into the core Save methods will work.

large file download abstract class

Create some kind of abstract wrapper class that encapsulates behaviors you'd need for reliable large spreadsheet downloads, following a begin, continue, complete pattern. This would use segmented temp storage of some kind, and zip file creation to prevent buffering a huge data set in memory.

inline lookup feature

When populating tables with key values, it's hard for users to have access to those keys and build spreadsheets manually with them. It would be a lot easier for the user to upload spreadsheets that have text values that we can automatically, silently convert to matching key values.

The idea here is to have an InlineLookup object. You'd use this after a normal upload, so there's no change to the core upload behavior.

You new up an InlineLookup<T> where T is the key type (typically int). You also specify the raw upload (source) table along with the output table that will store the results of all your attempted mappings. You also pass one or more Lookup objects that describe the mappings in use. We create blank rows in the output table to match the input, then update the result based on your defined joins:

var inlineLookup = new InlineLookup<int>("excel.RawSalesData", "excel.SalesDataKeyColumns", new Lookup[] 
{
    new Lookup("RegionName", "RegionId", "dbo.Region", "Name", "Id")
});

using (var cn = GetConnection())
{
    await inlineLookup.ExecuteAsync(cn);
}

implement BulkInsert?

I'm uploading a large spreadsheet, and it's taking forever
Could I use BulkInsert?

column length validation feature

When uploading a spreadsheet, we're usually doing that to stage an insert into another table.
That uploaded spreadsheet uses varchar(max) everywhere, so it allows things that are likely to fail when you insert into the destination table.
What I picture therefore is a new way to compare the lengths of varchar columns in the upload table with target columns they intend to map to, and to surface that as error info prior to attempting a real data load.