bulkinsert - Not able to import large flat file into SQL Server 2008 table -

I have a 18 GB flat file (40,000,000 records), there is no fixed column width (no field terminator), I want to read in a SQL Server 2008 R2 table. In addition to the text file with the data, I was given an Excel document with field name and length. There are 270 fields with 465 total characters per line (per line), I have created an FMT file which I like after using BCP

  10.0 270 1 SQLKR2 1 "1 titlecode SQL_Letin 1_General_CP1_CI_AS 2 SQLHR2 12 "" 2 Family ID SQL_Litin 1_General_CP1_CI_AS 3 SQLKR2 12 "" 3 spaceIIDLLtin1_gearial_CP1_CI_AS  

etc.

In SQL Server I read data in the table Use of this fmt file for I want to do this:

  bulk INSERT dbo.Customer2_noId FROM with 'C: \ Uploads dataFile_MICX \ DataFile_MICX_Copy.txt' (FORMATFILE = 'C: \ user \ kriss \ SqlScripts \ Customer2_noId-n .fmt '); GO  

Error message from SQL Server:

Message 4866, level 16, state7, line 1

Bulk load failed. The column in the data file for row 1, column 1 is too long.Verify that field terminator and line terminator is correctly specified. Message 739 9, Level 16, State 1, Line 1 < / P>

link A server "(null)" for Oeli DB provider "BULK" reported the error provider did not give any information about the error.

Message 7330, Level 16, State2, Line 1

The OLE DB provider for the linked server can not get a line from "BULK" (empty) ".

I have tried to change the terminator for "\ r" and "\ r \ n" for the previous field, I have tried to add an additional field below: / P>

  271 SQLHar 0 "\ r \ n" 271 Dummy SQL_Letin1_General_CP1_CI_AS  

No matter.

I have not been able to find anything on the line that helps (an extra blank line has been suggested in the FMT file, but it does not fix it. I think the data file In-line terminators, because if I use the Excel Data Import Tool, I see a continuous-line line.

Can someone help? Thanks, Chris

< / Div>

Without M, I have only some ideas and suggestions:

First of all, you need to determine whether a line is the terminator or not - none / R / N, four (13) + Try it with a copy of the file (four) (10), zero-terminator, or file.

Try and get a copy of the file, which contains only the first 10, 100, maybe 1000 rows. By making this it will be easier to find this presence

I remember that the BCP was swept away If there is nothing matching, then it will only fail. (I want to remember that to work BCP to replicate the weird first or last line in a file For NG.)

Have you considered using Interaction Services (SSIS)? It is more flexible and adaptable, although it represents a different learning curve. Its advantage is enhanced by Microsoft in the 2005 version, where I suspect that he has also done a code review on BCP over the past six years.