Could Not Bulk Insert. Error Reading Destination Table Column Name
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » Administration » Bulk insert formatfile problem Bulk insert formatfile problem Rate Topic Display Mode Topic Options Author Message Harry DouglassHarry Douglass Posted Tuesday, December 21, 2004 2:57 PM Forum Newbie Group: General Forum Members Last Login: Tuesday, February 17, 2009 12:13 PM Points: 6, Visits: 7 Hello, All.Trying to use a bulk insert and getting the following message:Server: Msg 4829, Level 16, State 1, Line 1Could not bulk insert. Error reading destination table column name for source column 23 in format file 'E:\Inetpub\wwwroot\RiskWeb\bool.fmt'.Here is my bool.fmt file7.02301SQLCHAR09","0a02SQLCHAR09","0b03SQLCHAR09","0c04SQLCHAR09","0d05SQLCHAR09","1BooleanID06SQLCHAR09","0f07SQLCHAR09","0g08SQLCHAR09","0h09SQLCHAR09","0i10SQLCHAR09","0j11SQLCHAR09","0k12SQLCHAR09","0l13SQLCHAR09","0m14SQLCHAR09","0n15SQLCHAR09","0o16SQLCHAR09","0p17SQLCHAR09","0q18SQLCHAR09","0r19SQLCHAR09","0s20SQLCHAR09","0t21SQLCHAR09","0u22SQLCHAR080","2BooleanDesc23SQLCHAR0255"\r"3BooleanExpI only want to import the 5th, 22nd and 23rd columns, but something is not right. Here is my stored procedure:CREATE PROCEDURE BooleanIDsFMT( @FilePathName varchar(100))ASTRUNCATE TABLE BooleanTestDECLARE @SQL varchar(1000)BEGIN SET @SQL = "BULK INSERT BooleanTest FROM '" +@FilePathName+ "' WITH (FORMATFILE = 'E:\Inetpub\wwwroot\RiskWeb\bool.fmt')"ENDEXEC(@SQL)GOAny help is appreciated!Harry Post #152079 Site OwnersSite Owners Posted Friday, December 24, 2004 8:00 AM SSChampion Group: General Forum Members Last Login: Friday, May 18, 2007 3:36 PM Points: 10,041, Visits: 1 No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have! Post #152461 THANG HOANGTHANG HOANG Posted Tuesday, December 28, 2004 12:52 PM SSC Rookie Group: General Forum Members Last
van GoogleInloggenVerborgen veldenZoeken naar groepen of berichten
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site http://stackoverflow.com/questions/2601941/bulk-insert-with-inconsistent-number-of-columns About Us Learn more about Stack Overflow the company Business Learn more http://www.dbforums.com/showthread.php?1005730-BCP-Utility about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up BULK INSERT with could not inconsistent number of columns up vote 5 down vote favorite 3 I am trying to load a large amount data in SQL server from a flat file using BULK INSERT. However, my file has varying number of columns, for instance the first row contains 14 and the second contains 4. That is OK, I just want to make a table with the max number of could not bulk columns and load the file into it with NULLs for the missing columns. I can play with it from that point. But it seems that SQL Server, when reaching the end of the line and having more columns to fill for that same row in the destination table, just moves on to the next line and attempts to put the data on that line to the wrong column of the table. Is there a way to get the behavior that I am looking for? Is there an option that I can use to specify this? Has anyone run into this before? Here is the code BULK INSERT #t FROM '
check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 9 of 9 Thread: BCP Utility Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 07-27-04,11:38 #1 mmcdonald View Profile View Forum Posts Registered User Join Date Mar 2004 Location Glasgow, Scotland Posts 107 Unanswered: BCP Utility Hi all, I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields: AppointmentKey SurgerySlotKey PatientKey Cancelled Continuation Deleted TimeArrived I would like to import only two of these fields into a table called tbl_Appointments e.g. AppointmentKey TimeArrived I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible? Thanks Reply With Quote 07-27-04,12:17 #2 rdjabarov View Profile View Forum Posts Registered User Join Date Jul 2003 Location San Antonio, TX Posts 3,662 Replace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement. Open your favorite text editor and modify the format file to look something like this: Code: 8.0 7 1 SQLINT 0 0 "\t" 1 AppointmentKey "" 2 SQLCHAR 0 255 "\t" 0 SurgerySlotKey SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 255 "\t" 0 PatientKey SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 255 "\t" 0 Cancelled SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 255 "\t" 0 Continuation SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 255 "\t" 0 Deleted SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 255 "\r\n" 7 TimeArrived SQL_Latin1_General_CP1_CI_AS "The data in a record depends on