help for loading data

• jzhou

  SSCommitted

  Points: 1863

  More actions

  August 29, 2002 at 1:56 am

  #158767

  Here is my format file current_client_scenario.fmt:

  8.0

  5

  1 SQLINT 1 4 ","

  1 clientid ""

  2 SQLCHAR 2 50 ","

  2 scenarioname SQL_Latin1_General_CP1_CI_AS

  3 SQLINT 0 4 ","

  3 scenario_id ""

  4 SQLINT 1 4 ","

  4 base_scenario_id ""

  5 SQLCHAR 2 40 "\r\n"

  5 clientname

  SQL_Latin1_General_CP1_CI_AS

  and here is my data file test.txt:

  1,test,2,1,jenny

  When I run the following in the SQL Analyzer as the follows:

  bulk insert chemsysdev.dbo.current_client_scenario

  FROM 'E:\test.txt' WITH (FORMATFILE='E:\current_client_scenario.fmt')

  I got the following error:

  Server: Msg 4866, Level 17, State 66, Line 1

  Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

  Server: Msg 7399, Level 16, State 1, Line 1

  OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

  The statement has been terminated.

  So I modify my query as the follows and run again but still get the same error:

  bulk insert chemsysdev.dbo.current_client_scenario

  FROM 'E:\test.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n',FORMATFILE='E:\current_client_scenario.fmt')

  any idea?

• crosspatch

  Hall of Fame

  Points: 3168

  More actions

  August 29, 2002 at 3:15 am

  #435604

  Just a thought. Would DTS be a better method for importing the text-file?

  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

  
  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

• NPeeters

  SSChampion

  Points: 12535

  More actions

  August 29, 2002 at 4:00 am

  #435611

  You should use SQLCHAR as the host file data type for all fields if it concerns an ASCII file.

  If not, it will think you are importing some kind of binary format, in which integers are stored in binary format of course.

• jzhou

  SSCommitted

  Points: 1863

  More actions

  August 29, 2002 at 11:29 am

  #435684

  Just try to change all fields to SQLCHAR, does not work. Here is my new format file:

  8.0

  5

  1 SQLCHAR 1 4 ","

  1 clientid SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 2 50 ","

  2 scenarioname SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 0 4 ","

  3 scenario_id SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 1 4 ","

  4 base_scenario_id SQL_Latin1_General_CP1_CI_AS

  5 SQLCHAR 2 40 "\r\n"

  5 clientname

  SQL_Latin1_General_CP1_CI_AS

  any more idea?

• jzhou

  SSCommitted

  Points: 1863

  More actions

  August 29, 2002 at 11:31 am

  #435686

  Sorry, post the wrong format file. Here is the right one:

  8.0

  5

  1 SQLCHAR 2 4 ","

  1 clientid SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 2 50 ","

  2 scenarioname SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 2 4 ","

  3 scenario_id SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 2 4 ","

  4 base_scenario_id SQL_Latin1_General_CP1_CI_AS

  5 SQLCHAR 2 40 "\r\n"

  5 clientname

  SQL_Latin1_General_CP1_CI_AS

  Thanks.