-
Notifications
You must be signed in to change notification settings - Fork 1
/
README.rzm
73 lines (59 loc) · 4.91 KB
/
README.rzm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
[![Nuget](https://img.shields.io/nuget/v/Excel2SqlServer)](https://www.nuget.org/packages/Excel2SqlServer/)
This is a library for importing Excel spreadsheets into SQL Server tables using [Excel Data Reader](https://github.com/ExcelDataReader/ExcelDataReader).
Nuget package: **Excel2SqlServer**
In a nutshell, use the [ExcelLoader](https://github.com/adamosoftware/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs) class and call one of the `SaveAsync` [overloads](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L108-L130). You can use a local filename or a stream as input. Here's a simple example that loads a single table from a local file:
```csharp
using (var cn = GetConnection())
{
var loader = new ExcelLoader();
await loader.SaveAsync("MyFile.xlsx", cn, "dbo", "MyTable");
}
```
This will save an Excel file called `MyFile.xlsx` to a database table `dbo.MyTable`. The table is created if it doesn't exist. Note also there is an `int identity(1,1)` column created called `Id` if it doesn't already exist in the spreadsheet.
If a spreadsheet has multiple sheets and you want to import all the sheets into multiple tables, omit the schema and table name from the `SaveAsync` call. `ExcelLoader` will use the sheet names in the spreadsheet to build the table names. If you need to customize the table names, you can pass a `Dictionary<string, ObjectName>` where the key represents the sheet name, and the `ObjectName` is the schema + object of the resulting table.
By default, data is always appended to existing data. You can pass an optional [Options](https://github.com/adamosoftware/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/Options.cs) object to customize the load behavior. For example:
```csharp
using (var stream = await blob.OpenReadAsync())
{
using (var cn = GetConnection())
{
var loader = new ExcelLoader();
int rows = await loader.SaveAsync(stream, cn, "dbo", "MyTable", new Options()
{
TruncateFirst = true,
AutoTrimStrings = true,
RemoveNonPrintingChars = true,
CustomColumns = new string[]
{
"[IsProcessed] bit NOT NULL DEFAULT (0)",
"[DateUploaded] datetime NOT NULL DEFAULT getdate()"
}
});
}
}
```
This will append some extra columns to the table when it's created `IsProcessed` and `DateUploaded`.
## An encoding error you might see
Note, if you see an error like this...
![img](https://adamosoftware.blob.core.windows.net:443/images/encoding-error.png)
...try adding this line before you use `ExcelLoader`:
```csharp
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
```
## Reference
- Task [CreateTableAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L18)
(string fileName, SqlConnection connection, string schemaName, string tableName, [ IEnumerable<string> customColumns ])
- Task [CreateTableAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L24)
(Stream stream, SqlConnection connection, string schemaName, string tableName, [ IEnumerable<string> customColumns ])
- Task\<int\> [SaveAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L108)
(string fileName, SqlConnection connection, [ Dictionary<string, ObjectName> tableNames ], [ [Options](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/Options.cs#L5) options ])
- Task\<int\> [SaveAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L114)
(string fileName, SqlConnection connection, string schemaName, string tableName, [ [Options](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/Options.cs#L5) options ])
- Task\<int\> [SaveAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L120)
(Stream stream, SqlConnection connection, [ Dictionary<string, ObjectName> tableNames ], [ [Options](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/Options.cs#L5) options ])
- Task\<int\> [SaveAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L126)
(Stream stream, SqlConnection connection, string schemaName, string tableName, [ [Options](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/Options.cs#L5) options ])
- Task\<DataSet\> [ReadAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L240)
(string fileName)
- Task\<DataSet\> [ReadAsync](https://github.com/adamfoneil/Excel2SqlServer.Library/blob/master/Excel2SqlServer.Library/ExcelLoader.cs#L248)
(Stream stream)