Read from Text file

Question

Post reply

Read from Text file

psteja2000

Mr or Mrs. 500

Points: 586
More actions
April 30, 2009 at 3:22 pm

#137218

I have a folder drop where apache server logs files are dropped. I need to periodically read the files and parse the lines and insert the info into a table.
What is the best approach? Should I parse the files in a sp ?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

I'm not sure if this is the best approach but it's definitely a geek-toy:

Copied and pasted from BOL (search for sp_addlinkedserver):

--Create a linked server.

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'c:\data\distqry',

NULL,

'Text'

GO

--Set up login mappings.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

GO

--List the tables in the linked server.

EXEC sp_tables_ex txtsrv

GO

--Query one of the tables: file1#txt

--using a four-part name.

SELECT *

FROM txtsrv...[file1#txt]

Another good way might be BCP.

Greets

Flo

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I'd probably use SSIS and execute the package in job.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

psteja2000 Mr or Mrs. 500 Points: 586 More actions · Answer 3

psteja2000

Mr or Mrs. 500

Points: 586

May 1, 2009 at 7:32 am

#987286

Great! Thanks..

steveb. SSC-Forever Points: 46733 More actions · Answer 4

I did something like this recently and tried a few approaches but eded up using LogParser, its a free tool from microsoft that reads log files and can output them to the database.

One advantage of log parser is that it can mantain its own checkpoints, so if you have a directory full of files log parser can remember what files it has processed and only process the new files.

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

steveb (5/1/2009)
I did something like this recently and tried a few approaches but eded up using LogParser, its a free tool from microsoft that reads log files and can output them to the database.
One advantage of log parser is that it can mantain its own checkpoints, so if you have a directory full of files log parser can remember what files it has processed and only process the new files.
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Did you use it to import data into a table? If so, can you explain a bit more about how you did it? Thanks.

--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)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

I would go with SSIS and the following link should get to started

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

--Ramesh

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Ramesh (5/4/2009)
I would go with SSIS and the following link should get to started
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Not a bad recommendation... I just do it all from T-SQL. I hope to never learn how to even spell "SSIS". 🙂

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Jeff Moden (5/4/2009)
Ramesh (5/4/2009)
I would go with SSIS and the following link should get to started
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Not a bad recommendation... I just do it all from T-SQL. I hope to never learn how to even spell "SSIS". 🙂

C'mon Jeff, SSIS is a nice tool, when used correctly, just like every other tool.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

psteja2000 Mr or Mrs. 500 Points: 586 More actions · Answer 9

I ended up hacking what I wanted from here:

http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

psteja2000 (5/4/2009)
I ended up hacking what I wanted from here:
http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Not bad... just be advised that there is absolutely no need to resort to the likes of xp_CmdShell or the horribly slow sp_OA* sprocs unless you need a whole lot of detail about the files. Try the following command and you'll see what I mean... you can probably guess its function once you see what it returns...

EXEC Master.dbo.xp_DirTree 'C:\',1,1

You can also capture the output from that in a table thusly...

[font="Courier New"] CREATE TABLE #MyDirectory

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

ObjectName SYSNAME,

Depth TINYINT,

IsFile TINYINT

)

INSERT INTO #MyDirectory

(ObjectName, Depth, IsFile)

EXEC Master.dbo.xp_DirTree 'C:\',1,1

SELECT * FROM #MyDirectory[/font]

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Jack Corbett (5/4/2009)
Jeff Moden (5/4/2009)
Ramesh (5/4/2009)
I would go with SSIS and the following link should get to started
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Not a bad recommendation... I just do it all from T-SQL. I hope to never learn how to even spell "SSIS". 🙂
C'mon Jeff, SSIS is a nice tool, when used correctly, just like every other tool.

Heh... Yep... I know. That's why I put a nice glass case around it and hung it on the wall instead of using it. 😛 🙂

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Jeff Moden (5/4/2009)
psteja2000 (5/4/2009)
I ended up hacking what I wanted from here:
http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/
Not bad... just be advised that there is absolutely no need to resort to the likes of xp_CmdShell or the horribly slow sp_OA* sprocs unless you need a whole lot of detail about the files. Try the following command and you'll see what I mean... you can probably guess its function once you see what it returns...
EXEC Master.dbo.xp_DirTree 'C:\',1,1
You can also capture the output from that in a table thusly...
[font="Courier New"] CREATE TABLE #MyDirectory
        (
        RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        ObjectName SYSNAME,
        Depth TINYINT,
        IsFile TINYINT
        )

INSERT INTO #MyDirectory
        (ObjectName, Depth, IsFile)
   EXEC Master.dbo.xp_DirTree 'C:\',1,1

SELECT * FROM #MyDirectory[/font]

And, sorry. Forgot to mention what the operands of that command are...

The first is obviously the top level directory you want to look at as if it were "level 1". It can either be a drive local to the server (as in my example) or it can contain a UNC (provided that the server is setup to actually "see" it).

The second operand is the number of levels deep you want to go in the directory structure. Considering the very limited data that is returned, it's usually a futile task to use anything other than "1" which is the current directory listed in the first operand.

The 3rd operand will return the "IsFile" column for any non-zero value. It's the operand that most "cheat sheets" on undocumented stored procedures usually miss.

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Like Jackie Chan's Uncle says... "And one more thing..."

If the second operand evaluates to "0" (zero), you get the whole directory structure from the current directory mentioned in the first operand to the bottom of the well. And, if the server cannot see the directory in the first operand or it doesn't exist, no rows will be returned and no "Does Not Exist" warning will be given. The xp_DirTree command does not return the "." or ".." "files" either so you can't differentiate between and empty found directory and a not-found directory.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (5/4/2009)
Ramesh (5/4/2009)
I would go with SSIS and the following link should get to started
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Not a bad recommendation... I just do it all from T-SQL. I hope to never learn how to even spell "SSIS". 🙂

Too late, you already have! 😉