Transforming Excel data can be a complex task this article describes how the Advanced ETL Processor works with Excel data.
Data can be extracted from the entire Sheet or just from the data range.Advanced ETL Processor assumes that "Sheet name" and "Range name" a delimited by a dot.
Users can still call Sheets as a "Great.Victory"
The logic as follows:
Advanced ETL Processor will check if Great.Victory sheet exits then it will read itIf it does not exist it will check if the Great sheet exitsIf it does exists it will check if Victory range exists if it does It will read data from just Victory rangeIf it does not it will read the data from Sheet "Great"
Please make sure that the range names are not duplicated within the file
Advanced ETL Processor gives the user various options to generate Excel files.
Common problem:trying to load the data from an Excel file half of the data is coming as nulls, or columns with more than 255 characters are truncated
As partially explained here
Initializing the Microsoft Excel driver
ODBC/MS Jet scans first TypeGuessRowsto determine field type
In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters
Setting IMEX=1 tells the driver to use the Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight-row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
Nobody wants to load half of the data, everybody wants to load data as it is
According to Wikipedia "FTP was not designed to be a secure protocol, and has many security weaknesses" and yet it the only protocol supported by SSIS. This fact alone makes this SSIS component practically useless
The new version of Advanced ETL Processor is available for download
This version addresses well-known problems related to working with Excel files
The sad story is: what ever you use: Excel ODBC, MS Jet, Ole DB, .NET sooner or later you will hit the brick wall.You can not load the data correctly without modifying excel file. We receive thousands of excel files every day. I mean do you seriously expect us to modify them manually every day? What kind of automation is that? We tried DTS it did not work tried SSIS it did work either, so we wrote vb macros to extract the data. We do not do it any more since we have discovered Advanced ETL processor It saved us thousands of hours.John Spoon.
Also Data grids modifications:
Advanced ETL Processor is true real alternative to SSIS. It's highly advanced capabilities makes SSIS look like a toy.
Text, XML, Excel, QVX, QVD, MS Access Database, DBF, Clarion, ODBC, JDBC, OleDB, MongoDB, MS SQL Server, MS SQL Server CE, Oracle, MySQL, PostgreSQL, Interbase/Firebird, SQLite, File System, POP3, IMAP4, HL7, Google SpreadSheet, RSS Feed, SalesForce, Brightpearl, BDE, HTTP, FTP, Microsoft Message Queue, Windows Event Log, Active Directory, Tableau Hyper and JSON
Performance depends on the data and complexity of the transformation
Advanced ETL Processor has more than 500 transformation and validation functions. if something is missing we will add for you. Data can be sorted grouped or filtered, the possibilities are limitless.
Here is SSIS Ftp task editor. There is no much to change or see here. Plus FTP is not a secure protocol so it makes it useless in the corporate environment.
Here is Advanced ETL Processor FTP Action Properties dialogue. The users can see the files and perform necessary FTP actions.
Unlike SSIS, Advanced ETL Processor avoids having different connectors, many of which are very subtle variations on similar components. Instead, Advanced ETL Processor uses only one universal Data writer and Data reader component but each one is highly configurable and includes all of the parameters needed to get your data in and out of almost any data source. This approach allows the end-user to design mapping once and use it with any database or file.
The monitor is a windows service waiting for an event to happen. When it does it executes the predefined action. we offer the following
Please also watch the repository videos. Understanding working with the repository is extremely important.
Download Free Trial of Advanced ETL Processor
Direct link, no registration required.
We have the following situation:
Imagine that you are working for a large financial organization and would like to load the year-end balances into the SQL Server database. The excel file looks like this.
So we create a simple SSIS package using Excel connector and Ole DB destination, before loading the data we hit the preview button.
For some reason last 3 lines have null values, This is because our connection string has not got IMEX=1
Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES";
This can be easily corrected by adding IMEX=1
Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
In this example, we are using Microsoft Access Engine 2016, the same applies to the older versions
That looks much better so we load the data. Later the same day, the finance manager says that the balance is wrong so you look at the data and there are some null values again.
One of our Insurance customers has contacted us recently and asked for immediate assistance. They were trying to implement XML files processing for a number of weeks using SSIS.Our consultant was able to deliver a working solution within one day using Advanced ETL Processor.
The XML files show up every day. The client places them on FTP server (which means that the file is locally found on our network via a UNC path. )Each nested XML tag should be a new “related” table.Main XML tag is "Contracts", Other relevant tags are Billing, Coverage, Person, Risk and Underwriting Question.There are a couple of data issues as bad dates from the customer. They should be ignored and inserted into the table as a Null. The process should be scheduled to check a UNC folder on the network at least 3 times per day. If one or more XML files exist, they need to be loaded into the server. Once loaded an Email should be sent to me indicating success, failure or nothing to do. There are some duplicated data due to the relationships of the data. For example, each contract has Agency information. So any contract MAY use a previously inserted Agency In this case Update would be appropriate. Updating data, if the same contract is loaded the second time all relevant data must be deleted first
For every nested XML tag, a relevant data transformation was created.One of the main problems was that XML format was not stable, some of the files have missing tags or additional tags.That was addressed by using custom XSLT transformations
Then the package was created to combine all those transformations together
We have been fighting this one with SSIS for about 2 weeks and got really behind schedule.
Then I remembered we own Advanced ETL Processor so I thought we should give it a shot. We are just so far behind that we need this ASAP and I did not want to mess it up. The result was far beyond our expectations. They were able to do more in one day than the entire team in two weeks.
In order to speed up the process please provide as much information as you can.