Reading from a text file

Question

Reading from a text file

Bakr Ben Ayad

Old Hand

Points: 325
More actions
March 6, 2007 at 2:25 pm

#117769

hi,
I'm using sql server 2000. I would like to read data from a text file, massage it, and store it to a different file.

In a stored procedure, can you please show me how to read from a text file, line by line.

Thanks

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

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

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

I would bulk insert it into a table, massage it, then use bcp to write to the file. Search the forums here or look in BOL for some useful information.

Luck, Dave

There is no "i" in team, but idiot has two.

Lowell SSC Guru Points: 323518 More actions · Answer 2

you might want to consider writing a program to do the work(open file, manipulate, save file) instead of in TSQL; most languages are more robust in their ability to edit files than SQL; vb/vb.net can bring the whole file into a single variable greater than 8000 chars and manipulate it; you can certainly do it in TSQL, but if a better tool is available, consider using it.

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!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Text files of this nature are easy to load and you don't need BCP or any of that so long as you have the right privs...

Let's say you have a file called MyTest.txt on the server's hard drive in a directory called "C:\Web". The following will load that file right the heck now...

DROP TABLE #MyHead

CREATE TABLE #MyHead(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Content VARCHAR(8000), ContinuationRow INT)

INSERT INTO #MyHead (Content,ContinuationRow)

EXEC sp_ReadErrorLog 1,'C:\Web\HTMLTest.html'

SELECT * FROM #MyHead

The neat thing is that it supposedly (haven't tested it fully) splits real long lines and marks continuation rows...

You will need to use either BCP or OSQL or xp_CmdShell to get the text file back out...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 4

Thank you very much guys. That was really helpful. I really appreciate all your help.

Don Urquhart SSCrazy Points: 2423 More actions · Answer 5

I have tried Jeff's method of using sp_ReadErrorLog on our SQL2000 and ran into the following:

When the records shorter than the Content field it works no problem.

When the records were longer initially I got an error:

Server: Msg 8152, Level 16, State 2, Procedure xp_readerrorlog, Line 19
String or binary data would be truncated.

and I had to set Ansi_Warnings off. But when I did this it only truncated the records. It did not do anything with the continuation.

Is there some trick I need to do to get that part to work?

If you don't stand for something, you'll fall for anything!,

Don Urquhart

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

I sure missed something, there... sorry. I've not had the same problem with it... lemme dig a little deeper...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 7

Jeff,

I have used your method and it worked perfect. I massaged the data, but I'm having some trouble writing that created table into a text file with putting back the lines together.

please help.

Thanks

Bakr

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Sure, Bakr... but I need a wee bit more information...

Did you end up with any continuation lines?
Can you use xp_CmdShell?
Can you post the DDL for the table (be sure to indicate the primary key) you loaded and the first, say, 10 or 20 rows of data from the table NOT INCLUDING the actual text you imported (would be too long to post here). And, please make sure that you do NOT post any personal information for individuals/businesses or company proprietary info.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 9

Thank you for replying.

Yes, I did end up with continuation lines for every line, because the file that I'm reading from is huge.

Yes I can use the xp_CmdShell.

I used exactly the same structure as you instructed me.

CREATE TABLE #MyHead(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Content VARCHAR(8000), ContinuationRow INT)

Sorry I can not post any of the data that I have

Thank you for your help and let me know if you need any more information.

Bakr