BCP import using a format file

Question

BCP import using a format file

Blake Duffey 41

I have a simple test database - 4 columns, each nchar(20).

I generate a BCP format file via: bcp dbo.Table_2 format nul -x -n -f "C:\Table_2_format.xml" -d test -S sqldev -T

When I run: bcp dbo.Table_2 IN "C:\input2.csv" -f "C:\Table_2_format.xml" -d test -S sqldev -T

I get: Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A required attribute ID is missing in xml format file for the field at line 5 column 102.

So I generate a non-XML format file via: bcp dbo.Table_2 format nul -n -f "C:\Table_2_format.fmt" -d test -S sqldev -T

When I run the bcp import using this file, I get:

Starting copy...

0 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 47

Since the format file is being generated by bcp, I'm confused why the XML would not be valid. I'm then confused why the non-XML file wouldn't import.

Yitzhak Khabinsky 26,586 Reputation points

2023-07-25T20:36:23.58+00:00
@Blake Duffey,

Please attach to your question the following:

Table_2_format.xml

input2.csv
Yitzhak Khabinsky 26,586 Reputation points

2023-07-26T02:00:13.7966667+00:00

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are missing completely.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

Accepted answer

2 additional answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2023-07-25T20:36:23.58+00:00

@Blake Duffey,

Please attach to your question the following:

Table_2_format.xml

input2.csv
Yitzhak Khabinsky 26,586 Reputation points

2023-07-26T02:00:13.7966667+00:00

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are missing completely.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

I did this on SQL 2019:

CREATE TABLE bcptest (col1 nchar(20) NULL,
                      col2 nchar(20) NULL,
                      col3 nchar(20) NULL,
                      col4 nchar(20) NULL)
INSERT bcptest(col1, col2, col3, col4)
   VALUES('Stravinsky', 'Chaikovsky', 'Rimsy-Korsakov', 'Prokofieff'),
         ('Debussy', 'Ravel', 'Saint-Saëns', 'Chopin')

Then I ran in a command-line window:

bcp tempdb.dbo.bcptest format nul -x -f slask.fmt -n -S .\DIXNEUF -T
bcp tempdb.dbo.bcptest format out -f slask.fmt  -S .\DIXNEUF -T

This completed normally. Then I ran in SQL:

TRUNCATE TABLE bcptest

And back in the command-line window I ran:

bcp tempdb.dbo.bcptest in slask.bcp -f slask.fmt  -S .\DIXNEUF -T

This completed successfully.

Now there are some details missing from your post:

The actual CREATE TABLE statement.
Which version of bcp you are using. (bcp -v will tell you.)
The actual format file generated.

There is also one thing that strikes me as funny. The file has a suffix of .csv, but you use the option -n for native format. CSV is text format, but native format is binary. In a native file there are no delimiters. Integer values are always four bytes. And nchar(20) values are prefixed by two bytes giving the length of the values.

Also, you would use the option to generate a format file, if you are exporting a table to import it elsewhere. If you have a file you want to import, you will have to craft the format file by hand (if format cannot be expressed with help of the -t and -r options.)

Of the two formats for the format file, I much prefer the old one. The XML format does not give any extra powers, but is only bulky.

Blake Duffey 41 Reputation points

2023-07-25T19:04:32.9733333+00:00

The input data is just text created in notepad. I gave it .csv extension because it is comma delimited. :)

I'm trying to re-familiarize myself with bcp after a long hiatus. I'm not sure of the actual data types in question. My test is just goofy fields.

I'm using bcp 15.0.4928.1

Let me try your sample - and I can provide the format files after. Thanks much.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-25T19:07:54.86+00:00

For a plain comma-separated file, you would use -t , rather than -n.

If you want to learn more about BCP and BULK INSERT, I have an article on my web site, Using the Bulk-Load Tools in SQL Server that you may find interesting.
Blake Duffey 41 Reputation points

2023-07-25T20:31:47.23+00:00

The actual import data is a comma delimited file with various data types (char, date/time, etc). Additionally, a number of the char data fields are enclosed with "" (which of course I don't want in the database).

When i use -t the date/time values do not transfer properly. I would think that using a format file would be the proper approach (since it would establish the column types). Would appreciate any additional advice.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-25T20:36:55.16+00:00

The data type you specify in the format file, is the type of the data in the file. And for a text file that is always SQLCHAR or SQLNCHAR. (And always the same for all fields.) SQLDATETIME etc refer to binary formats.

It is possible to deal with the quotes, if they are applied consistently. That is, a field must always be quoted, or never quoted. But if it's only quoted on needed, BCP will not work for you. For how to set up the format file in this case, I refer to my article.

If you use BULK INSERT on the other hand, there is a specific option for CSV that makes this a whole lot easier.
Blake Duffey 41 Reputation points

2023-07-25T20:50:18.53+00:00

"The data type you specify in the format file, is the type of the data in the file. And for a text file that is always SQLCHAR or SQLNCHAR. (And always the same for all fields.)"

If that is the case, and I have date/time values in the source file:

01/12/2023 for example

Can that be imported properly into a datetime field via bcp?

Apologies for the series of questions and many thanks for your feedback.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-25T21:03:56.2966667+00:00
Depends on what you mean with "properly". There are two possible outcomes for that string. The interpretation of that string is mainly subject to the language and datetime settings in SQL Server. Which you cannot control directly from BCP, so it would be your default language that controls this. However, you can use the -R option to override with your regional settings.

I create this table:

CREATE TABLE OneCol(d datetime)

And I put 01/12/2023 in a file.

When I run:

bcp tempdb.dbo.OneCol in slask.csv -S .\DIXNEUF -T -c

I get 2023-01-12 00:00:00.000 in the table, since my language setting in SQL Server is us_english. When I add the -R option I get 2023-12-01 00:00:00.000, since my Swedish settings are in force.

If you have dates on this format in the file, don't be surprised that there are rows that errors out no matter which setting you do, because there is bad data in the file.

A common strategy is load the data into a staging table with permitting types (i.e. strings) and deal with the conversions in the next step (where you can use try_convert to ignore bad data).
Blake Duffey 41 Reputation points

2023-07-26T00:03:23.4766667+00:00

@Erland Sommarskog - thanks so much for your insight on this. I believe I found the issue (as usual, it's inconsistent data). While the data is 'comma delimited', the 'text' fields are wrapped with double-quotes - and sometimes those text field contains a comma. My issue with the date is related to those records (it throws off the columns). The non-text fields (dates, ints, etc) have no quotes at all. Tools like Excel can 'figure it out' (and obscures the fact that the double-quotes are there unless you open it in a less discerning tool).
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-26T08:06:11.68+00:00
To repeat what I said yesterday: If your data looks something like this:

123,"Text",2022-12-12 123,"Text, with comma",2002-09-14

It is possible to load this with BCP, provided that you craft the format file correctly. As for how,On the other hand, if the data looks like this:

123,Text,2022-12-12 123,"Text, with comma",2002-09-14

That is, quotes are only there when needed. BCP cannot deal with this file, but BULK INSERT can, because you can use the option FORMAT = 'CSV'.
Blake Duffey 41 Reputation points

2023-07-26T13:38:40.4+00:00

Thanks again for your assistance - extremely helpful!

Answer 2

Konstantinos Passadis 19,586 MVP

Removed ,

Apologies

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-25T18:58:58.5466667+00:00

To be honest, Chat GPT is quite off the mark here....

Answer 3

Vahid Ghafarpour 23,385 Volunteer Moderator

First of all, make sure that the path is correct "C:\intel\Table_2_format.xml" and "C:\Table_2_format.xml"

And next, ensure that the XML file is well-formed and contains all the required attributes for each field. One missing or incorrectly formatted attribute can lead to this error.

Blake Duffey 41 Reputation points

2023-07-25T18:32:05.7433333+00:00

I've updated the path in the original post. Wouldn't the original BCP output a valid XML file?

Share via

BCP import using a format file

2 additional answers

Your answer