Automatically generate Format File for Bulk inserting?

techresearch7777777 1,796 Reputation points
2022-09-23T19:12:41.687+00:00

Hello, I have a very large flat file trying to import using either BULK INSERT or bcp (preferably would like to use BULK INSERT) which contains the double quotes symbol " surrounding each columned data and is delimited by the vertical bar symbol |

Believe using a Format File can overcome this situation to import.

This flat file has about 80 columns...is there a way or tool to automatically generate the proper Format File for this flat file instead of manually typing up each item in Format File?

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Accepted answer
  1. Erland Sommarskog 101.8K Reputation points MVP
    2022-09-23T21:41:41.463+00:00

    If you are on SQL 2017 or later, you can use the FORMAT option: 

       BULK INSERT csvtable FROM 'C:\Temp\YourFile.txt'  
   WITH (FORMAT = 'CSV', FIELDTERMINATOR="|")

    For older version, you will indeed need a format file. And you will most likely to craft it by hand. If all columns are quoted, it's a fairly simple copy-paste affair. The trick is that you will need 81 fields in your format file, where the first is terminated by a double quote only This field needs 0 for the target column.

    If only text columns are quoted, you will need to go through the format file more closely.

    If text columns are quoted only when needed, you lose.

    For more details, with examples how deal with quoted input, see this article on my web site about bulk load: https://www.sommarskog.se/bulkload.html.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-09-23T19:20:00.513+00:00

    You would be better off using SSIS. You do not need a format file.

    0 comments
  2. Tom Phillips 17,716 Reputation points
    2022-09-23T19:29:07.43+00:00

    In current versions of BULK INSERT, you do not need a format file to do a pipe delimited, quoted fields.

    0 comments
  3. Olaf Helper 41,001 Reputation points
    2022-09-26T05:17:18.927+00:00

    is there a way or tool to automatically generate the proper Format File for this flat file

    Such a tool would have to know or guess in an intelligent way the file structure and the approbiate data type for each column, which should change over time; how should that work?
    If it would be that easy, an tool would exists; it don't.

    0 comments
  4. techresearch7777777 1,796 Reputation points
    2022-09-26T08:05:45.967+00:00

    Thanks everyone for all the replies, much appreciated.

    Erland, your example confirmed was very helpful similar what am testing :)

    0 comments