Insert data from a text file document into a SQL table

• Jnrstevej

  SSCrazy

  Points: 2343

  More actions

  August 19, 2011 at 8:25 am

  #390227

  Hi

  Does anyone know how to insert a a text file document into a SQL Server table please could you advise

  __________________________________________________________________________________
  Steve J
  Jnr Developer
  BSc(Hon)

• Jnrstevej

  SSCrazy

  Points: 2343

  More actions

  August 19, 2011 at 8:39 am

  #1370778

  I'm not to sure how you go about using bulk insert or OPENROWSET. Do you have any info for a beginner on this??

  __________________________________________________________________________________
  Steve J
  Jnr Developer
  BSc(Hon)

• Jnrstevej

  SSCrazy

  Points: 2343

  More actions

  August 19, 2011 at 9:19 am

  #1370828

  Thanks i tried to do a dummy bulk insert but i cant seem to get it to work. Can you have a look at let me know where I'm going wrong

  USE Test

  GO

  CREATE TABLE CSVTest1

  (ID INT,

  FirstName VARCHAR(40),

  LastName VARCHAR(40),

  BirthDate SMALLDATETIME)

  GO

  BULK

  INSERT CSVTest

  FROM 'C:\csvtest.txt'

  WITH

  (

  FIELDTERMINATOR = ',',

  ROWTERMINATOR = ''

  )

  GO

  __________________________________________________________________________________
  Steve J
  Jnr Developer
  BSc(Hon)

• SQLRegex

  Right there with Babe

  Points: 777

  More actions

  August 19, 2011 at 9:34 am

  #1370838

  Two things.

  1. Your table create is referencing a different table from your insert.

  2. You don't define a row terminator. I created a file and defined the row terminator as a CR. Ran your script and everything was fine.

  I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

• Jnrstevej

  SSCrazy

  Points: 2343

  More actions

  August 19, 2011 at 9:57 am

  #1370863

  I'm not too sure what you mean by your second point, are you saying i should remove that line

  BULK

  INSERT Test1

  FROM 'c:\csvtest.txt'

  WITH

  (

  FIELDTERMINATOR = ','

  )

  GO

  __________________________________________________________________________________
  Steve J
  Jnr Developer
  BSc(Hon)

• SQLRegex

  Right there with Babe

  Points: 777

  More actions

  August 19, 2011 at 10:22 am

  #1370882

  Sorry, what I meant was you aren't defining your rowterminator. Use a \ n (remove space between slash and n)

  I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 19, 2011 at 10:24 am

  #1370884

  JT-328471 (8/19/2011)

  ---

  Sorry, what I meant was you aren't defining your rowterminator. Use a \ n (remove spaces)

  he probably pasted it right...the forum here strips out \n unless you html-ize with the ampersanding stuff.

  the forum auto html-izes greater than adn less than, but not slash-n.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jnrstevej

  SSCrazy

  Points: 2343

  More actions

  August 21, 2011 at 1:49 pm

  #1371193

  Hey SQL Ninja thanks for are help and all who replied

  I used the following which has worked perfectly

  BULK INSERT Test.dbo.CSVTest

  FROM 'C:\csvtest.txt'

  WITH

  (

  FIELDTERMINATOR = ',',

  ROWTERMINATOR = '/n'

  )

  GO

  __________________________________________________________________________________
  Steve J
  Jnr Developer
  BSc(Hon)