+3 votes
in Databases by (4.6k points)
I want to use BULK INSERT to load the data from text/csv files to the tables in SQL server. The number of columns in the table is more than that in the text/csv files. That's why I want to specify the column names in BULK INSERT. But the problem is - BULK INSERT doesn't allow to specify the column names. I need to mention the format file that will have the name of the columns that I want to populate. Can someone help me how to create the format file? I don't think manual creation is a feasible and good option.

1 Answer

0 votes
by (13.6k points)
edited by

You need to use bcp command on Windows command line to create the format file. The generated format file (.fmt) will have all the columns from your table, so you need to delete the unwanted ones. I think deleting 2-3 columns is easier than typing 100 columns :)

Type bcp on Windows command line to see all the options available with bcp command.

C:\Users\praveen\Documents\fmtfiles>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]

Once you became familiar with all these options, now it's time to create the format file.

Type the following command on command line to create .fmt file

C:\Users\praveen\Documents\datafiles>bcp truven.native.red_book format nul -f red_book-x.fmt -n -T -S muse1\sqlexpress -r\n -t\t

The above command will create file fmtfile-n.fmt. You can view it in the notepad/any text editor. In the above command:

Database:- Mention your database name.

Schemaname:- Mention your schema name.

Tablename:- Mention your tablename for which you are creating the format file.

\t - if your input data is tab delimited. Change it as per your input data file.

Servername: Mention you database server name. You can find it in MS SQL Server Management Studio.

\n: if the rows in the input file are in newline.

fmtfile-n.fmt: your output filename.

...