Solved

Read bottom line of text file into sproc

Posted on 2004-04-21
14
520 Views
Last Modified: 2012-08-14
I have a sproc that uses DTS to read in a csv file into a table - no problems.

However, the bottom line of the file contains this:
Trailer=6 (6 being the number of records)

I need to get the number (6 above) and cross check that with the number of records imported - how can I read in the number?

I know this should be easy - having problems finding a solution for some reason...

Thanks...
0
Comment
Question by:niico
  • 6
  • 6
  • 2
14 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10883114
Actually, unless you're using an ActiveX script task in DTS with the filesystem object, it's not that easy.

Are you using a text file connection?
0
 
LVL 13

Accepted Solution

by:
danblake earned 50 total points
ID: 10887527
If this was me,
I would tail the file, grab the last line to a seperate file or pipe then input this..
You can use sed/awk/perl / etc... to grab a last line of a file quickly.

For example looking here:  http://www.student.northpark.edu/pemente/sed/ (Free SED -- for windows)

 # print the last line of a file (emulates "tail -1")
 sed '$!d'                    # method 1
 sed -n '$p'                  # method 2

(From : http://www.danielb.connectfree.co.uk/Links.htm)

once you have the last line, you can just bulk load the last line in via DTS.


You can also ensure that you only load every line but the last line, by performing a quick count of the number of lines in a file:
 # count lines (emulates "wc -l")
 sed -n '$='

With this information you can use, multiple techniques such as:

1) Bulk load all rows apart from last line
2) Bulk load all rows from lastline -1 -> lastline

this will then give you in 1) your required data.
2) the seperate table for you to run a replace on 'Trailer=' to get your rownumber and then run a compare between 1&2...

Need a working example ?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10887565
You can adjust the last line / start line of a bulk load in DTS within the options tab of the transformation.

Alternativly, you can have these as global variables to ensure automation based upon the return output from sed.
0
 

Author Comment

by:niico
ID: 10888576
Hi

Thanks for the input.

In terms of getting the data (sans last line) - DTS seems to be doing that fine and just ignoring the trailer line.

I take it I'd need to 'install' sed onto the server? Is there a way of doing this without installing additional software?

Any examples would certainly be greatly appreciated yes - thanks.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10888691
"In terms of getting the data (sans last line) - DTS seems to be doing that fine and just ignoring the trailer line."


Well, like I asked above and received no input on.  Are you using the input text file task?  If so, the last line does not match your input layout and will most likely be discarded.  About the only way around this in dts is to use ActiveX, the filesystem object and parseit.  The other way, define you input text file as one long string and parse it that way on the transform to your output.

One more option you could try is to setup the ODBC driver for text files and you can issue "queries" against the text file.
0
 

Author Comment

by:niico
ID: 10888750
hi - thank you both

arbert - yes I am - and it's disguarding it (which is obviously good in the context of the data import)

I have found the following:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=359&lngWId=5

It seems to have a very effective solution for this - importing each new line into a new field of a table. All I think need to do is select the 'last' line of the table.


Thanks...
0
 
LVL 13

Expert Comment

by:danblake
ID: 10888829
each new line into a new field of a table -- this is going to be very inefficient...
(but its down to you, I would personally test several versions then use the best implementation).
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:danblake
ID: 10888851
I take it I'd need to 'install' sed onto the server?
(Its a single .EXE executable -- pretty robust, you can always limit it to a single user to run the file/service)
Is there a way of doing this without installing additional software?
--> You could manipulate a filestream directly with C++ for example to get the last row of the file only, reading backwards from the end of the file until a LFCF was found for example.

(But this will require you to build a DLL -> xp_ within sql-server, but this may be what you want..)
0
 

Author Comment

by:niico
ID: 10892012
Having thought about it yes - the bulk insert was pretty inefficient.

Ive downloaded sed and it works well - thanks very much!
0
 

Author Comment

by:niico
ID: 10908378
hello again - just as a quick addition - how can I use sed (in a 1 line command) to add a line to the top of a file?

thanks...
0
 
LVL 13

Expert Comment

by:danblake
ID: 10908560
I can use the utility (This one isn't listed anywhere I know..)...
My view file for example: viewtext.txt
CREATE VIEW dbo.vwu_tubs  AS
SELECT     salutation, int_id, CAST(NULL AS bit) AS Proc_Type
FROM         dbo.tubsBKUP

Running the following command line on the input file:
(Where Freddy is the text I want to insert before the input file):
>sed-3.59 "1i\Freddy " viewtext.txt
This is the output from the above line:
Freddy
CREATE VIEW dbo.vwu_tubs  AS
SELECT     salutation, int_id, CAST(NULL AS bit) AS Proc_Type
FROM         dbo.tubsBKUP

Freddy is any example text, so if calling this from a xp_cmdshell.  
You can specify any line number by replacing the 1 with any other value to use a regular expression matching enclose in /patern/ format to insert a standard text when a particular expression occurs.

0
 
LVL 13

Expert Comment

by:danblake
ID: 10908567
I can use the utility (This one isn't listed anywhere I know..)...
My view file for example: viewtext.txt
CREATE VIEW dbo.vwu_tubs  AS
SELECT     salutation, int_id, CAST(NULL AS bit) AS Proc_Type
FROM         dbo.tubsBKUP

Running the following command line on the input file:
(Where Freddy is the text I want to insert before the input file):
>sed-3.59 "1i\Freddy " viewtext.txt > viewtext.txt (To place the output back into the original file and replace the original file)
This is the output from the new viewtext.txt file:
Freddy
CREATE VIEW dbo.vwu_tubs  AS
SELECT     salutation, int_id, CAST(NULL AS bit) AS Proc_Type
FROM         dbo.tubsBKUP

Freddy is any example text, so if calling this from a xp_cmdshell.  
You can specify any line number by replacing the 1 with any other value to use a regular expression matching enclose in /patern/ format to insert a standard text when a particular expression occurs.

0
 

Author Comment

by:niico
ID: 10908593
thanks again

sorry im probably being really stupid - that outputs what I need, how can I get that into a file (or add it to the existing file)?
0
 

Author Comment

by:niico
ID: 10908600
ahh got it

add > newfile.txt - to the end!

great - thanks again for all your help!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Backup Job question 4 19
search for a string in all tables 4 15
BULK INSERT most recent CSV 19 19
shrink datafile Sql server 4 14
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now