Using Format Files for Bulk Load. An SQL text by Erland Sommarskog, SQL Server. Digital Living Picture Frame Manual. MVP. Most recent update 2. Copyright applies to this text. See here for font conventions used in this article. Introduction. If you want to import a file into SQL Server there are a number of options available Write an SSIS package. Use the ExportImport Wizard in SSMS which uses SSIS. Im still getting used to SQL, so before I get to using stored procedure, I would like to understand how to use BULK INSERT effectively first. I need to combine 50. Im trying to import a correctly quoted CSV file, meaning data is only quoted if it contains a comma, e. Terminator, Black 42, Monsters, Inc., Blue I observe. Importing and modifying Active Directory users with AD Bulk Users Importing users into Active Directory CSV, Excel The import file can be formatted using the CSV. Find examples of how to use the BULK INSERT statement in SQL Server 20 to bulkload data files into a database, copy data into a table and more. Use some other ETL tool like Informatica. Write a custom program for the load. Export information from a CSV file. The operation of import information from a CSV file is based on a preliminary step in which we export the required. MHenV.png' alt='Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />Use any of the three bulk load tools that come with SQL Server. BCP, which runs from the command line. BULK INSERT, which is a TSQL command. OPENROWSET BULK, which you can use in the FROM clause of a query. The topic for this article is the last three. They are far from always the best choice, but there are situations where they can fulfil your needs very well. One distinct allure they have is that they can save you from dragging in another component like SSIS into your mix only because you need to import a handful of files. The bulk load tools have been in the product for a long time and they are showing their age. When they work for you, they are powerful. But you need to understand that these tools are binary to their heart, and they have no built in rule that says that each line a file is a record they dont even think in lines. You also need to understand that there are file formats they are not able to handle. I have tried to arrange the material in this article so that if you have a simple problem, you only need to read the first two chapters after the introduction. I first introduce you them to their mindset, which is likely to be different from yours. Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />We provide database converters for MySQL, MSSQL, Oracle, MS Access, Foxpro, DBFs and CSV Fixed Width Text files. Selects data from a table or multiple tables. GROUP BY groups the the result by the given expressions. HAVING filter rows after grouping. ORDER BY sorts the result. Next I cover the basic options to use for every day work. If you have a more complex file, you will need to use a format file and the next three chapters are for you. I first describe how format files work as such, and the next two chapters show how to use format files for common cases for import and export respectively. There is a brief interlude about a new feature in SQL Server v. Next that turns the tables for CSV files before a chapter about Unicode files, including files encoded in UTF8. Then comes a chapter about advanced options, including how to load explicit values into an IDENTITY column. A short chapter covers permissions. The last chapter discusses XML format files, and I am not sorry at all if you give this chapter a blind eye I find XML format files to be of dubious value. History and Overview. The command line tool BCP has been in the product since version 1. The original purpose was surely to permit copying of data between SQL Server instances using native format which is binary, and this is what BCP was designed for. Handling text files might have been slapped on as an after thought. The command BULK INSERT was added in SQL Server 7 so that you could bulk load files from inside SQL Server. SQL 2. 00. 5 added the rowset function OPENROWSET BULK to permit you to query the data file as if it is a table. While the three offer the same basic functionality they are different implementations. BCP uses ODBC, while the other two rely on OLE DB and the three dont behave identically. Many of the differences are with nitty gritty details that you may never encounter or notice. But as a general tip if you run into problem with one of them that you cant understand or you dont seem to able to work around, try one of the other in trio maybe it works with that guy. To muddle the waters further, OPENROWSETBULK works differently depending on how you use it. It seems that if you say INSERT tbl. SELECT FROM OPENROWSET BULK. AS tbl. The behaviour is more or less identical to BULK INSERT. But if you replace the star with an explicit column list or add a WHERE clause, there are changes in the behaviour. I will mention some of these differences in this article, but Im not making any attempt to include a complete catalogue. Note there are also APIs available for bulk load. For a native application you would use the ODBC API, which is very close to what the BCP command offers. There is also a bulk load API in OLE DB, but given that OLE DB is deprecated for access to SQL Server, this is nothing you should consider. For a. NET application there is the Sql. Bulk. Copy class which is a yet another implementation of the bulk load concept. However, I am not covering any of the APIs in this article. Some Housekeeping Notes. This article is full of small example files. The contents of the files is displayed within the article, and they are also available in the archive bulkload. If you want to play with the examples, you need to download and extract this archive. You cannot copy and paste from the text in the article, as you need to get the exact bytes. You can extract the files wherever you want, but C tempbulkload is a good choice, since all example scripts in this article use this folder. Except where otherwise noted, the examples in this article run on all versions from SQL 2. I have mainly tested on SQL 2. SQL 2. 01. 6. All CREATE TABLE scripts are intended to be executed in tempdb. Some tables are reused from previous examples, and you may have to truncate them or drop and recreate them to get the result I present in the text. This is nothing I point out in the article, but I assume that you are able to figure out that part yourself. A note about character encodings. In this text I will talk about 8 bit files as in contrast to Unicode files, although this is a bit of a misnomer. In a non Unicode file for Swedish, English, Polish or Russian each character is encoded indeed in 8 bits. However, non Unicode files for East Asian languages such as Chinese or Japanese use a multi byte character set as they are known in Windows, where only the letters from the Latin alphabet are encoded in 8 bits. Suspecting that many readers of this article will come from countries where the Latin and Cyrillic scripts dominate, I have opted overlook this fact. I hope that East Asian readers can accept my apology for this simplification. The Conflicting Mindsets. Before we start to look at commands, options, format files etc, I want to directly go to the point which causes the most confusion and frustration when the nave user tries to use the bulk load tools the conflicting mindsets. As humans when we look at a text file with data, we think of every line as a record to import, and since the bulk load tool appears to have a concept of a record terminator, we expect the bulk load tool to have the same perception. But this is an illusion. As I mentioned, the initial purpose of BCP was to permit copying data between different SQL Server instances using a binary format. And while BCP, BULK INSERT and OPENROWSETBULK can work with text files, they still look at these text files as binary streams. When they read a file, they look at the definition for the first field, whereupon they read bytes until they have come to the end of that field. The second field is handled the same, and they jog along that way in a round robin fashion. The only way they can register that they have reached the end of the record is by reading the last field. But if something that you think of as a record terminator appears in another field, the tools just take it to be part of the data. Here is an example to illustrate how things can go wrong. Consider this table created in tempdb CREATE TABLE x A varchar3. NULL. B varchar3. NULL. C varchar3. NULL. D varchar3. NULLWe have the data file data. Alpha,beta,gamma.