This means that you need pull far less data into SQL because you can do a lot of selection and pre-processing before the data gets anywhere near SQL server.
There are some features missing, of course, but you can do joins between tables, filter rows to taste, do aggregations and some string manipulations. Worksheets, or areas within worksheets, become tables. If you use the ODBC driver, then your Excel workbook becomes a little SQL-based relational database. It is a Once and Future technology, developed before its time, but now showing its value for processing large volumes of data, despite its quirks, poor documentation and lackluster support. I always feel slightly awkward in talking about ODBC. This article will aim to show how this is done. It is possible to do a lot of filtering and aggregation of data before it ever gets to SQL Server, since you can turn an existing Excel Workbook into a poor-man’s relational database, or even create one. The most important thing, though, is that you can aggregate before you send the data. It is neater than SSIS too, and more versatile. It is quicker than automating Excel and you can do it without requiring a copy of Excel. The most important direction is from Excel to SQL Server, of course. It is reasonably easy to insert data from Excel into SQL Server, or the reverse, from any other ODBC database to any other, using PowerShell. Getting Data between Excel and SQL Server using ODBC - Simple Talk
0 kommentar(er)
