Writing txt file into SQL table

Downforce

Hi,

My situation is:

For example i have txt file called serverlog.txt

txtfile contains:

serverid 3

Last log on 19/3/2007

Linkstatus OK

I have a table created with the following values

Table: serverlog

serverid(primarykey)

LastLogOn

LinkStatus

So my question is how do i get my txt file variables into the sql table.

Is there no import function in SQL

Ive tried to do it with VB but thats doesnt work.

Greetings Sheila

NigelRivett

something like

create table #a (s varchar(100))

bulk insert #a from 'c:\myfile.txt'
insert serverlog (select right(s, len(s) - charindex(' ',s)) from #a where s like 'serverid%'),
(select select right(s, len(s) - charindex(' ',s)) from #a where s like 'Last log on%'),
(select select right(s, len(s) - charindex(' ',s)) from #a where s like 'Linkstatus%')
drop table #a

Why doesn't it work with VB

Downforce

Really tnx for the answer!

But it is not working....

I m getting the following error:

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ')'.

I tried with openfile but i dont get te selected text that i want in VB.

I hope someone can help me

-X- Sheila

Peter J

Hi Sheila,

I believe the previous post had a few typo's in it here is the query as intended.

create table #a (s varchar(100))

bulk insert #a from 'c:\myfile.txt'
insert into serverlog (select right(s, len(s) - charindex(' ',s)) from #a where s like 'serverid%'),
(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Last log on%'),
(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Linkstatus%')
drop table #a

Downforce

It is still not working for me.

The names are correct

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ')'.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ','.

What means (select right(s,len(s)

That section gives me te error

I hope to hear some of you guys.

-X- greetings Sheila B.

Peter J

I'll walk you through the query

#a is a temporary table, which you should create before running the query and drop afterwards.

bulk insert #a from 'c:\serverlog.txt'

bulk insert loads the data from the serverlog file into the temporary table, note sql server assumes that each row is a new record.

select right(s, len(s) - charindex(' ',s))

this is doing the following

Select Right, is grabbing the characters from the right hand side of the string

len(s) is getting the length of the string

charindex(' ',s) is performing a lookup for the first instance of the space ' ' character in the string.

So when combined they are grabbing the right hand side of the string, from the first space in the string to the end of the string

So for the record (or line in the file)

serverid 3

len(s) = 10

charindex(' ',s)) = 9

so right(s, len(s) - charindex(' ',s)) = 3

or right(s, 10 - 9)

The correct query is

CREATE TABLE #a(s nvarchar(100))

bulk insert #a from 'c:\serverlog.txt'

insert serverlog

SELECT (select right(s, len(s) - charindex(' ',s)) from #a where s like 'serverid%') as ServerID,

(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Last log on%') as LastLogOn,

(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Linkstatus%') as LinkStatus

drop table #a

My apologies for my earlier statement as I was up late and not on my development machine at the time.

One other thing which I can't tell you without seeing the exact table structure you are trying to insert into is that the insert may fail, if you have more columns in the table. If you need help post the table structure and i'll take a look at it.

Try running the following so you can see what the queries do and how they work, it pretty much re-creates my explanation above only you will see the effect of each function.

CREATE TABLE #a(s nvarchar(100))

bulk insert #a from 'c:\serverlog.txt'

SELECT (select right(s, len(s) - charindex(' ',s)) from #a where s like 'serverid%') as ServerID,

(select len(s) from #a where s like 'serverid%') as LengthOfServerIDRow,

(Select charindex(' ', s) from #a where s like 'serverid%') as IndexOfSpaceChar,

(select right(s, 10 - 9) from #a where s like 'serverid%') as RightHandCharacter

drop table #a

Each line of the query shows what each function is doing and should help your understanding of the query.

I hope this helps

Downforce

Really tnx for the answer!

I am really happy with the result

-X- Sheila

Peter J

Excellent,

I'm glad we helped you out.

Now you just need to accept the answer to finish the thread.

Pete

Downforce

Peter when i have multiple items in my serverlog with the same name it isnt still working

My logfile

Serverid: 3
Last log on : 13/23/2007
Linkstatus: Good
Serverid: 4
Last log on : 23/23/2007
Linkstatus: Good
Serverid: 5
Last log on : 03/23/2007
Linkstatus: Worse

M getting the following error

(12 row(s) affected)

Msg 512, Level 16, State 1, Line 5

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Peter J

Ah ok,

What this means is that the sections of the query that are sub queries, see below in yellow.

SELECT (select right(s, len(s) - charindex(' ',s)) from #a where s like 'serverid%') as ServerID,

(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Last log on%') as LastLogOn,

(select right(s, len(s) - charindex(' ',s)) from #a where s like 'Linkstatus%') as LinkStatus

these are returning multiple values, which if there was only one server log in the file they would be fine, but because there are multiples it means the select statement cannot compile correctly.

So some questions for you,

Does the file always get built like the following

Serverid: 3
Last log on : 13/23/2007
Linkstatus: Good
Serverid: 4
Last log on : 23/23/2007
Linkstatus: Good
Serverid: 5
Last log on : 03/23/2007
Linkstatus: Worse

Is there ever a case where the file could be in a different format

e.g.

Last log on : 13/23/2007

Serverid: 3

Linkstatus: Good

Last log on : 23/23/2007

Serverid: 4

Linkstatus: Good

Last log on : 03/23/2007

Serverid: 5

Linkstatus: Worse

I'm asking these questions now as I believe we could use a Cursor to resolve your issue. I won't have time to put together something until tonight as I am at work right now. but the pseudo code is below.

Basically if the file is always done in that way you could use a cursor to loop through the temporary table three rows at a time, with each loop you would set one of the relevant variables,

So the following pseudo code would be the

CREATE TABLE #a(s nvarchar(100))

bulk insert #a from 'c:\serverlog.txt'

Declare Cursor

Open Cursor with Select * From #a

Declare serverID, lastLogon, Status

Declare @Count INT

SET @Count = 0

While @@FetchStatus <> -1

IF @Count = 1

Set Server ID Variable

Select @Count = @Count + 1

if @Count = 2

Set Last Logon Variable

Select @Count = @Count + 1

if @Count = 3

Set Link Status Variable

Insert Record into Table using all three variables

Set the Variables to be null

SELECT @Count = 0

Move to next row

Close Cursor

Deallocate cursor

drop table #a

Forgive me I can't think of the correct cursor syntax but if you press F1 in sql management studio then lookup cursors you will find a lot of help on them in there, when I get home in 4 hrs time i'll do it properly, unless you post and say you've got there on your own.

The close and deallocate cursor statements are important if you know .Net programming they are basically the equivalent of Dispose Methods, they cleanup the cursors resources.

Hope this helps

Pete

Peter J

Oh could you post the tables schema, which holds all of these records.

that way I can make sure the statement I send you is correct.

Peter J

Hi Sheila,

This script will handle multiple server status records. I've commented each line as best as I can do, if you get stuck, try looking at the sql server help files or just post another question on here.

DECLARE @FileLine NVARCHAR(100) -- We will use this to store each row of the file as we loop through

-- These variables are going to be used to store each of the three rows variables to flatten the data from 3 rows of 1 column into 1 row of 3 columns.

Declare @serverID INT, @lastLogon NVARCHAR(12), @Status nvarchar(30)

Declare @Count INT -- We will use this to count to three (3 lines per record as per your original msg)

-- create the table as before

CREATE TABLE #a(s nvarchar(100))

-- retrieve the rows from the database

bulk insert #a from 'c:\serverlog.txt'

-- declare the cursor with the select command selecting all the rows from the file into a dataset.

DECLARE server_Log_Cursor CURSOR

FOR SELECT s FROM #a

-- Open the cursor to enable actively retrieving of the records.

OPEN server_Log_Cursor

-- set the count to zero as we need to count upto three.

SET @Count = 0

-- Select the first record from the file.

FETCH NEXT FROM server_Log_Cursor INTO @FileLine

-- This is checking that the fetch status is 0, if it is zero it means another row exists in the dataset

WHILE @@Fetch_Status = 0

BEGIN

SELECT @Count = @Count + 1

-- IF Statements to see which row we are parsing, we do it in blocks of threes as per original msg.

IF @Count = 1

BEGIN

SELECT @serverID = CAST(right(@FileLine, len(@FileLine) - charindex(' ',@FileLine)) AS INT) -- Selecting the server ID as before

END

ELSE IF @Count = 2

BEGIN

SELECT @lastLogon = right(@FileLine, len(@FileLine) - charindex(' ',@FileLine)) -- Selecting the LastLogon ID as Before

END

ELSE IF @Count = 3

BEGIN

SELECT @Status = right(@FileLine, len(@FileLine) - charindex(' ',@FileLine)) -- Selecting The status as before.

-- This is the last row of this block of three, so we insert the record into the table

INSERT INTO ServerLog (ServerID, LastLogon, ServerStatus)

VALUES (@ServerID, @LastLogon, @Status)

-- We blank out the variables so they are fresh for the next set.

SELECT @ServerID = NULL, @LastLogon = NULL, @Status = NULL

-- We reset our row counter.

SELECT @Count = 0

END

-- We fetch the next record before the end of while loop, otherwise the cursor wouldn't move next.

FETCH NEXT FROM server_Log_Cursor INTO @FileLine

END

-- Closing the cursor.

CLOSE server_Log_Cursor

-- DEALLOCATE to stop the cursors dataset being held in memory to stop the cursors dataset being held in memory.

DEALLOCATE server_Log_Cursor

-- Drop the table as before

DROP TABLE #a

I hope this helps and will solve your issue. As you can see SQL Server Management studio has nicely coloured the keywords in blue for you and the @@Fetch_Status in pink

Good luck!

Downforce

Hi Peter m really glad you are explaining everything it helped me alot!!!!!

But i got some other question aswell i know im a bit dumb.

Now i have a logfile in a different format, like this:

Datum Tijd computernaam Username,Action
01-02-2007, 07:34:23,PC2737, User, Logon

I hope to hear a answer from you soon.

-xXx- Sheila from Netherlands.

Peter J

Hi Sheila,

This can be done using the Bulk Copy Protocol or BCP.

You will be able to read about this in the sql server help files.

do a search for "bulk copy [SQL Server]" without quotes in the help files for the correct usage of the bcp utility.

Or alternatively you could look into SQL Server integration services SSIS which would allow you to use a text datasource to pull in the data.

Hope this helps

Thanks

Pete

Downforce

I got all the data on 1 line

Can you help me again Peter

Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table

Re: Writing txt file into SQL table