Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production?
Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files.
So here’s a simple Java Utility class that can be used to load CSV file into Database. Note how we used some of the best practices for loading data. The CSV file is parsed line by line and SQL insert query is created. The values in query are binded and query is added to SQL batch. Create a table in sql with same column name as there were in.csv file. Create table Billing ( iocl_id char(10), iocl_consumer_id char(10)); Create a Control file that contains sql*loder script. In notepad type the script as below and save this with.ctl extension, in selecting file type as All Types(*). Hello Sarathy, You can do that in 2 ways. 1) Create a.csv file out of your excel sheet & load that data using the SQL. LOADER WIZARD which is under the DBA tab. Of TOAD (Version7.6) 2) You can import the excel data directly using the IMPORT option under the. DATABASE tab of TOAD (Version7.6). As either of them is a wizard.
So I’ve come up with an alternative solution that I would like to share with you.
When implemented, the process of importing flat files with changing metadata is handled in a structured, and most important, resiliant way. Even if the columns change order or existing columns are missing.
Background
When importing flat files to SQL server almost every standard integration tool (including TSQL bulkload) requires fixed metadata from the files in order to work with them.
This is quite understandable, as the process of data transportation from the source to the destination needs to know where to map every column from the source to the defined destination.
Let me make an example:
A source flat file table like below needs to be imported to a SQL server database.
This file could be imported to a SQL Server database (in this example named FlatFileImport) with below script:
2 4 6 8 10 12 14 16 18 20 22 | create table dbo.personlist( [gender]varchar(10), [city]varchar(20), ); BULK INSERT dbo.personlist WITH FIRSTROW=2, ROWTERMINATOR='n',--Usetoshift the control tonext row CODEPAGE='ACP' |
The result:
If the column ‘Country’ would be removed from the file after the import has been setup, the process of importing the file would either break or be wrong (depending on the tool used to import the file) The metadata of the file has changed.
2 4 6 8 10 12 14 16 | --import data from file with missing column(Country) FROM'c:sourcepersonlistmissingcolumn.csv' ( FIELDTERMINATOR=';',--CSV field delimiter ROWTERMINATOR='n',--Usetoshift the control tonext row CODEPAGE='ACP' |
With this example, the import seems to go well, but upon browsing the data, you’ll see that only one row is imported and the data is wrong.
The same would happen if the columns ‘Gender’ and ‘Age’ where to switch places. Maybe the import would not break, but the mapping of the columns to the destination would be wrong, as the ‘Age’ column would go to the ‘Gender’ column in the destination and vice versa. This due to the order and datatype of the columns. If the columns had the same datatype and data could fit in the columns, the import would go fine – but the data would still be wrong.
2 4 6 8 10 12 14 | --import data from file with switched columns(Age andGender) FROM'c:sourcepersonlistswitchedcolumns.csv' ( FIELDTERMINATOR=';',--CSV field delimiter ROWTERMINATOR='n',--Usetoshift the control tonext row CODEPAGE='ACP' |
When importing the same file, but this time with an extra column (Married) – the result would also be wrong:
2 4 6 8 10 12 14 16 | --import data from file with newextra column(Married) FROM'c:sourcepersonlistextracolumn.csv' ( FIELDTERMINATOR=';',--CSV field delimiter ROWTERMINATOR='n',--Usetoshift the control tonext row CODEPAGE='ACP' |
The result:
Import Csv File To Yahoo Calendar
The above examples are made with pure TSQL code. If it was to be made with an integration tool like SQL Server Integration Services, the errors would be different and the SSIS package would throw more errors and not be able to execute the data transfer.
The cure
When using the above BULK INSERT functionality from TSQL the import process often goes well, but the data is wrong with the source file is changed.
Import Csv File To Outlook
There is another way to import flat files. This is using the OPENROWSET functionality from TSQL.
In section E of the example scripts from MSDN, it is described how to use a format file. A format file is a simple XML file that contains information of the source files structure – including columns, datatypes, row terminator and collation.
Feb 07, 2016 mix - refx nexus free expansion - guitar xp free download+tutorial youtube A SEAL Team SIX Member Reveals How To Escape A Kidnapping - Duration: 8:25. BuzzFeed Multiplayer Recommended for you. Refx nexus guitar expansion download google chrome.
Generation of the initial format file for a curtain source is rather easy when setting up the import.
But what if the generation of the format file could be done automatically and the import process would be more streamlined and manageable – even if the structure of the source file changes?
From my GitHub project you can download a home brewed .NET console application that solves just that.
If you are unsure of the .EXE files content and origin, you can download the code and build your own version of the GenerateFormatFile.exe application.
Another note is that I’m not hard core .Net developer, so someone might have another way of doing this. You are very welcome to contribute to the GitHub project in that case.
The application demands inputs as below:
Example usage:
Tom Clancy's Rainbow Six: Vegas 2 v1.03 ENG. More Rainbow Six: Vegas 2 Fixes. Battery No CD/Public server fix Tom Clancy 's Rainbow Six: Vegas 2 v1.03 ENG Tom Clancy's Rainbow Six: Vegas 2 v1.01 All Tom Clancy's Rainbow Six: Vegas 2 v1.02 All. https://zegol.netlify.app/rainbow-six-vegas-2-crack-download.html. Tom Clancys Rainbow Six Vegas 2 Free Download PC game setup in signle direct link for windows. It’s an action game which is based on war. Tom Clancy’s Rainbow Six Vegas 2 PC Game Overview. Tom Clancy’s Rainbow Six Vegas 2 is a very interesting and exciting shooting game. This game belongs from the series of Tom Clancys Rainbow Six Vegas.
generateformatfile.exe -p c:source -f personlist.csv -o personlistformatfile.xml -d ;
The above script generates a format file in the directory c:source and names it personlistFormatFile.xml.
The content of the format file is as follows:
The console application can also be called from TSQL like this:
2 4 6 | --generate format file set@cmdshell='c:sourcegenerateformatfile.exe -p c:source -f personlist.csv -o personlistformatfile.xml -d ;' |
If by any chance the xp_cmdshell feature is not enabled on your local machine – then please refer to this post from Microsoft: Enable xp_cmdshell
Zz huawei advanced tool descargar. You can use this tool for several devices. The developers of this amazing and powerful ZZKey have release this tool recently and allow ist users to download this undated free from the link shared below. However you can use many other tools for unlocking or flashing your phones. In addition to Qualcomm, HiSilicon and Confined this must-have dongle fully covers the entire MTK platform.
Using the format file
After generation of the format file, it can be used in TSQL script with OPENROWSET.
Example script for importing the ‘personlist.csv’
2 4 6 8 10 12 | --import file using format file into dbo.personlist_bulk bulk'c:sourcepersonlist.csv', firstrow=2 |
This loads the data from the source file to a new table called ‘personlist_bulk’.
From here the load from ‘personlist_bulk’ to ‘personlist’ is straight forward:
2 4 6 8 10 | --load data from personlist_bulk topersonlist insert into dbo.personlist(name,gender,age,city,country) |
Load data even if source changes
The above approach works if the source is the same every time it loads. But with a dynamic approach to the load from the bulk table to the destination table it can be assured that it works even if the source table is changed in both width (number of columns) and column order.
For some the script might seem cryptic – but it is only a matter of generating a list of column names from the source table that corresponds with the column names in the destination table.
2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 | --import file with different structure ifexists(select OBJECT_ID('personlist_bulk'))drop table dbo.personlist_bulk declare@cmdshell varchar(8000); set@cmdshell='c:sourcegenerateformatfile.exe -p c:source -f personlistmissingcolumn.csv -o personlistmissingcolumnformatfile.xml -d ;' --import file using format file into dbo.personlist_bulk bulk'c:sourcepersonlistmissingcolumn.csv', formatfile='c:sourcepersonlistmissingcolumnformatfile.xml', )ast; --dynamic load data from bulk todestination declare@sql nvarchar(4000); select@fieldlist= ','+QUOTENAME(r.column_name) select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='personlist' join( select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='personlist_bulk' onb.COLUMN_NAME=r.COLUMN_NAME set@sql='truncate table dbo.personlist;'+CHAR(10); set@sql=@sql+'insert into dbo.personlist ('+@fieldlist+')'+CHAR(10); set@sql=@sql+'select '+@fieldlist+' from dbo.personlist_bulk;'; exec sp_executesql@sql |
The result is a TSQL statement what looks like this:
2 4 | truncate table dbo.personlist; insert into dbo.personlist([age],[city],[gender],[name]) select[age],[city],[gender],[name]from dbo.personlist_bulk; |
The exact same thing would be able to be used with the other source files in this demo. The result is that the destination table is correct and loaded with the right data every time – and only with the data that corresponds with the source. No errors will be thrown.
From here there are some remarks to be taken into account:
- As no errors are thrown, the source files could be empty and the data updated could be blank in the destination table. This is to be handled by processed outside this demo.
Further work
As this demo and post shows it is possible to handle dynamic changing flat source files. Changing columns, column order and other changes, can be handled in an easy way with a few lines of code.
Going from here, a suggestion could be to set up processes that compared the two tables (bulk and destination) and throws an error if X amount of the columns are not present in the bulk table or X amount of columns are new.
It is also possible to auto generate missing columns in the destination table based on columns from the bulk table.
The only boundaries are set by limits to your imagination
Summary
With this blogpost I hope to have given you inspiration to build your own import structure of flat files in those cases where the structure might change.
As seen above the approach needs some .NET programming skills – but when it is done and the console application has been built, it is simply a matter of reusing the same application around the different integration solutions in your environment.
Happy coding 🙂
See more
Consider these free tools for SQL Server that improve database developer productivity.
External links:
Import Csv File Yahoo
Brian Bønk Rueløkke
His work spans from the small tasks to the biggest projects. Engaging all the roles from manual developer to architect in his 11 years experience with the Microsoft Business Intelligence stack. With his two certifications MSCE Business Intelligence and MCSE Data Platform, he can play with many cards in the advisory and development of Business Intelligence solutions. The BIML technology has become a bigger part of Brians approach to deliver fast-track BI projects with a higher focus on the business needs.
View all posts by Brian Bønk Rueløkke
Latest posts by Brian Bønk Rueløkke (see all)
T Sql Import From Csv
- How to import flat files with a varying number of columns in SQL Server - February 22, 2017
- Ready, SET, go – How does SQL Server handle recursive CTE’s - August 19, 2016
- Use of hierarchyid in SQL Server - July 29, 2016