Bulk insert with text qualifier in SQL Server

by

You need to use a ‘format file’ to implement a text qualifier for bulk insert. Essentially, you will need to teach the bulk insert that there’s potentially different delimiters in each field.

Create a text file called “level_2.fmt” and save it.

11.0
2
1   SQLCHAR   0  8000   "\","      1     wkt         SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0  40   "\r\n"      2     area         SQL_Latin1_General_CP1_CI_AS

The first line, “11.0” refers to your version of SQL. The second line shows that your table, [level2_import], has two columns. Each line after that will describe a column, and obeys the following format:

[Source Column Number][DataType][Min Size][Max Size][Delimiter pattern][Destination Column Number][Destination Column Name][Case sensitivity of database]

Once you’ve created that file, you can read in your data with the following bulk insert statement:

BULK INSERT level2_import
FROM 'D:\test.csv'
WITH 
(
  FIRSTROW = 2,
  FORMATFILE='D:\level_2.fmt'
);

Refer to this blog for a detailed explanation of the format file.

More Related Contents:

  1. Bulk Insert Partially Quoted CSV File in SQL Server
  2. SQL Server Bulk insert of CSV file with inconsistent quotes
  3. How to write UTF-8 characters using bulk insert in SQL Server?
  4. BULK INSERT with identity (auto-increment) column
  5. What’s the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?
  6. Cannot bulk load. Operating system error code 5 (Access is denied.)
  7. sql geography to dbgeography?
  8. How to Bulk Insert from XLSX file extension?
  9. SQL Server 2008 Spatial: find a point in polygon
  10. If condition in SQL Server 2008
  11. Comma separated results in SQL
  12. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  13. Which are more performant, CTE or temporary tables?
  14. Preferred Method of Storing Passwords In Database
  15. SQL WHERE.. IN clause multiple columns
  16. Query to list all stored procedures
  17. How do I search an SQL Server database for a string?
  18. SQL Server – parameter sniffing
  19. Does the order of columns in a WHERE clause matter?
  20. How can I select from list of values in SQL Server
  21. Configure Lucene.Net with SQL Server
  22. SQL Server tables: what is the difference between @, # and ##?
  23. How to safely use reserved SQL names?
  24. Comparing dates stored as varchar
  25. SQL Server: the maximum number of rows in table [closed]
  26. SSRS report definition is newer than Server
  27. Joining multiple tables returns NULL value
  28. How to maintain the order of insertion in SQL Server
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment