There is an input text file (download from CRM). In the body there are 55 columns (separated by tabular), the first row with the title has 54 columns. By idea code

BULK INSERT dbo.dbname FROM 'C:\all_Crm\2014\august\data_dbname.txt' WITH ( CODEPAGE = '1251', FIELDTERMINATOR = '\t', FIRSTROW = 2, ROWTERMINATOR = '\n', MAXERRORS = 10, );

Should add all lines starting from the second, but adding from the third, if you delete the first line with the FIRSTROW = 1 and replace FIRSTROW = 1 , then everything is added correctly. Those. FIRSTROW triggered not by the end of the line, but by 55 columns (54 + 1 of the next) and the end of the line (that is, the next line). It is not possible to delete a line with a header in advance (file size 500mb-2.5GB). How can I make it work right?

  • And in on bulk insert what is written? - maxleo
  • About this situation - nothing (MSDN is a bad shit help) - DiegoTM
  • Normal in MSDN help: The FIRSTROW attribute is not intended to skip column headers. Header skipping is not supported by the BULK INSERT statement. When lines are skipped, the SQL Server Database Engine component searches only for end-of-field signs and does not check the data in the missing row fields. msdn.microsoft.com/ru-ru/library/ms188365.aspx . You are trying to MS SQL "feed" incorrect data - and expect that they will be processed correctly. - Zufir
  • By the way, about deleting a line. Did you think to dynamically form a request for bulk insert? Read the title bar, see the number \ t, if it matches the required one - take firstrow = 2, if less - take firstrow = 1? And the file will not need to be shoveled - Zufir

1 answer 1

Good day.

And tried to use FORMATFILE? And in the formatting file, specify that the 55th column is not described in the header line (see here: Creating a formatting file (SQL Server) ).

FORMATFILE = 'format_file_path' Specifies the full path to the format file. This format file contains a description of the data file — information obtained by applying the bcp program to the same table or view. And it is intended for cases when:

  • the data file contains more or less columns than the table or view;

  • columns are in a different order;

  • column delimiters differ;

  • there are any other changes in the data format. Format files are usually created using the bcp program and then, if necessary, modified in a text editor. For more information, see the bcp program.