BULK INSERT

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  April 17, 2007 at 3:11 am

  #173081

  Hi All

  I'm trying to import a file into a SQL table, with the following quety:

  BULK

  INSERT DBSupport.dbo.[PerformanceStats]

  FROM 'C:\FullDaily20070202.CSV'

  WITH

  (

  FIELDTERMINATOR

  = '|',

  ROWTERMINATOR

  = '|\n'

  )

  But get the error msg:

  Msg 4860, Level 16, State 1, Line 1

  Could not bulk insert. File 'C:\FullDaily20070202.CSV' does not exist.

   

  The file does exists and the filename is correct.

  Thanks in advance...

   

  PS: THANK yOU for all your input

   

  Anchelin

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  April 17, 2007 at 3:51 am

  #700867

  does the sqlserver service account have access to c:\ ?

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  April 17, 2007 at 4:36 am

  #700874

  Hi

  Thanks for the reply... I do have found another way of importing my spreadsheet; via DTS...

  The problem I have is that my spreadsheet has 256 columns and the max num of columns to import into a table is 32. Is there a workaround for this???

  Please... Thank You

  Anchelin

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  April 17, 2007 at 5:35 am

  #700888

  this works for me ...

  I've created a table (csv_test) with 256 columns , saved the xls as csv and used this statement :

  BULK

  INSERT ddbadummy.dbo.csv_test

  FROM 'P:\Documents\Data\Excel\test.csv'

  WITH

  (CODEPAGE = 'OEM' ,

  DATAFILETYPE = 'char' ,

  FIRSTROW =2,

  FIELDTERMINATOR =';')

  1 rows imported ...

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  April 17, 2007 at 8:11 am

  #700925

  Hi

  Thanks...

  I used the wizard to completes this task. When I ran the package, it gave me the error:

  Too many fields defined.

  It copies the data(256 cols) from the Excell spreadsheet and populate the table in my database.

   

  I went through all the steps in the wizard,choose the source(excell file),choose destination(table in my db), i then uses the copy table and views from the source database. source-excell file, destination-the table, named the package,clicked finish.

  When I execute this, returns the above error

  Thanks

  Anchelin