adamfoneil / excel2sqlserver.library Goto Github PK
View Code? Open in Web Editor NEWLibrary for importing Excel spreadsheets into SQL Server tables
License: MIT License
Library for importing Excel spreadsheets into SQL Server tables
License: MIT License
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.
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);
}
I'm uploading a large spreadsheet, and it's taking forever
Could I use BulkInsert?
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
Id
{TableName}Id
any name you like
Something like that
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.
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.
Ability to call LTRIM(RTRIM())
on incoming data to remove spaces that are (as far as I can imagine) always unwanted.
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]+
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.