T-sql Bcp Excel Column Headers

-->

Create Column Headers In Excel
Bcp Export With Column Headers

Here Mudassar Ahmed Khan has explained with an example, how to export SQL data to Excel file with Column Headers (Column Names) in C# and VB.Net. The records from the SQL Server database Table are fetched into a DataTable and then the DataTable will be exported to Excel file using the ClosedXml library in C# and VB.Net. TAGs: ASP.Net, Excel, SQL Server.

APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

This article describes how to use a format file to skip importing a table column when the data for the skipped column does not exist in the source data file. A data file can contain fewer fields than the number of columns in the destination table - that is, you can skip importing a column - only if at least one of the following two conditions is true in the destination table:

The skipped column is nullable.
The skipped column has a default value.

Sample table and data file

The examples in this article expect a table named myTestSkipCol under the dbo schema. You can create this table in a sample database such as WideWorldImporters or AdventureWorks or in any other database. Create this table as follows:

The examples in this article also use a sample data file, myTestSkipCol2.dat. This data file contains only two fields, although the destination table contains three columns.

Basic steps

You can use a non-XML format file or an XML format file to skip a table column. In both cases, there are two steps:

Use the bcp command-line utility to create a default format file.
Modify the default format file in a text editor.

We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. You can click the This page button in the Feedback section at the bottom of this page. We read every item of feedback about SQL, typically the next day. Thanks.

The modified format file must map each existing field to its corresponding column in the destination table. It must also indicate which table column or columns to skip.

For example, to bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, skip Col2, and map the second field to Col3.

Option #1 - Use a non-XML format file

Step #1 - Create a default non-XML format file

Create a default non-XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

Important

You might have to specify the name of the server instance to which you are connecting with the -S argument. Also, you might have to specify the user name and password with the -U and -P arguments. For more information, see bcp Utility.

The previous command creates a non-XML format file, myTestSkipCol_Default.fmt. This format file is called a default format file because it is the form generated by bcp. A default format file describes a one-to-one correspondence between value of each format-file row that follows a deleted row. The goal is sequential 'Host file field order' values, 1 through n, that reflect the actual position of each data field in the data file.

Finally, reduce the value in the 'Number of columns' field to reflect the actual number of fields in the data file.

The following example is based on the default format file for the myTestSkipCol table. This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. The row for Col2Asus p5vdc-mx drivers download for windows 7 1. has been deleted. The delimiter after the first field has also been changed from t to ,.

Option #2 - Modify the row definition

Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. In this format-file row, the 'prefix length,' 'host file data length,' and 'server column order' values must be set to 0. Also, the 'terminator' and 'column collation' fields must be set to ' (that is, to an empty or NULL value). The 'server column name' value requires a non-blank string, though the actual column name is not necessary. The remaining format fields require their default values.

The following example is also derived from the default format file for the myTestSkipCol table.

Examples with a non-XML format file

The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

Using BULK INSERT

This example works by using either of the modified non-XML format files created as described in the preceding section. In this example, the modified format file is named myTestSkipCol2.fmt. To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in SSMS, run the following code. Update the file system paths for the location of the sample files on your computer.

Option #2 - Use an XML format file

Step #1 - Create a default XML format file

Create a default XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

Important

The previous command creates an XML format file, myTestSkipCol_Default.xml. This format file is called a default format file because it is the form generated by bcp. A default format file describes a one-to-one correspondence between data-file fields and table columns.

Note

For information about the structure of XML format files, see XML Format Files (SQL Server).

Step #2 - Modify an XML format file

Here is the modified XML format file, myTestSkipCol2.xml, which skips Col2. The FIELD and ROW entries for Col2 have been removed and the entries have been renumbered. The delimiter after the first field has also been changed from t to ,.

Examples with an XML format file

The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

To import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the modified XML format file, myTestSkipCol2.xml.

Using BULK INSERT with a view

With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file. Then, you can bulk import data from that file into the view.

The following example creates the v_myTestSkipCol view on the myTestSkipCol table. This view skips the second table column, Col2. The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

In SSMS, run the following code. Update the file system paths for the location of the sample files on your computer.

Using OPENROWSET(BULK..)

To use an XML format file to skip a table column by using OPENROWSET(BULK..), you have to provide an explicit list of columns in the select list and also in the target table, as follows:

Create Column Headers In Excel

The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. The statement contains an explicit list of columns in the select list and also in the target table, as required.

In SSMS, run the following code. Update the file system paths for the location of the sample files on your computer.

1 Answer

You should do a union with your headers just like this

ThomasThomas

2,8192 gold badges8 silver badges21 bronze badges

Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.

mobilogoboss