Insert data from a text file document into a SQL table

Question

Post reply

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)
August 19, 2011 at 8:34 am

This was removed by the editor as SPAM
August 19, 2011 at 9:03 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jnrstevej SSCrazy Points: 2343 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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: 323504 More actions · Answer 6

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 · Answer 7

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)