Generic column mapping
-
Hi, I have been asked solution for this. I have Vendors where i get different excel file (diffrenct structure) from different vendor. Vendor1 excel file ------------------ Name, Address, Age Vendor2 Excel file ------------------ CandidateName, PhysicalAddress, Age what ever Header Names but meaning is same. Order of Headers are may vary different... but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels. Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly. Thanks, Ravikiran
-
Hi, I have been asked solution for this. I have Vendors where i get different excel file (diffrenct structure) from different vendor. Vendor1 excel file ------------------ Name, Address, Age Vendor2 Excel file ------------------ CandidateName, PhysicalAddress, Age what ever Header Names but meaning is same. Order of Headers are may vary different... but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels. Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly. Thanks, Ravikiran
Ravikiran72p wrote:
but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels.
Unless you have "something" that you can use to identify the type of data in that column, there's nothing to map. If both names and order change, then the computer will no longer be able to distinguish between a first-name (string) and a last-name (string). I'd suggest to use the table-headers to "propose" a mapping for that particular vendor, based on the current file and your best guess (based on a "contains"-call on the header to try and identify it) and to save/use that.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hi, I have been asked solution for this. I have Vendors where i get different excel file (diffrenct structure) from different vendor. Vendor1 excel file ------------------ Name, Address, Age Vendor2 Excel file ------------------ CandidateName, PhysicalAddress, Age what ever Header Names but meaning is same. Order of Headers are may vary different... but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels. Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly. Thanks, Ravikiran
Oh the joys of dealing with people who use Excel as a data source :mad:. The only reliable way will be to maintain a mapping table for each source, Load your data into staging tables (I use bulkcopy, creating a new table based on the source), then test the structure against your map, process or spit the dummy based on the structure check.
Never underestimate the power of human stupidity RAH
-
Hi, I have been asked solution for this. I have Vendors where i get different excel file (diffrenct structure) from different vendor. Vendor1 excel file ------------------ Name, Address, Age Vendor2 Excel file ------------------ CandidateName, PhysicalAddress, Age what ever Header Names but meaning is same. Order of Headers are may vary different... but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels. Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly. Thanks, Ravikiran
Ravikiran72p wrote:
Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.
The only solution I know of is to load the data excel data into raw tables - stage this data then qc it with queries etc. Once you are happy with the integrity of the data you can then load it into your database. Basically this amounts to a datawarehousing system - you will need human input between the staging and load to warehouse. This is where the qc comes into effect. All data that comes from a client needs to be qc'd with a human eye as invariably there will be data that breaks your business logic. Don't trust client data, especially if it is in Excel format! If any data is incorrect then the standard procedure is to ask the client to resend the corrected data - it should never be the software or receivers job to 'correct' raw data, since as soon as you 'correct ' the data you then become responsible for its contents.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Hi, I have been asked solution for this. I have Vendors where i get different excel file (diffrenct structure) from different vendor. Vendor1 excel file ------------------ Name, Address, Age Vendor2 Excel file ------------------ CandidateName, PhysicalAddress, Age what ever Header Names but meaning is same. Order of Headers are may vary different... but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels. Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly. Thanks, Ravikiran
Ravikiran72p wrote:
Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.
Create an "adaptor" (concept rather than a specific type of implementation) specific to each vendor. I suggest STRONGLY that you use extensive error checking on such an adaptor because that is the only way you will detect when they change the format. The vendor files are delivered to a vendor specific folder and that is used as a key to determine which adaptor is used to process them. The adaptor could be written in either SQL or C#. I would choose C# because it provides more flexibility for this sort of operation. Note that a C# solution need not go directly to the database. In can just output a file, with a fixed known format, which is then used as an import file to SQL Server. The adaptors should also have error reporting, logging is good, to report on failed conversion attempts, as well as when successful ones ran.