[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Read bottom line of text file into sproc

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
niico
Asked:
niico
  • 6
  • 6
  • 2
1 Solution
 
arbertCommented:
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
 
danblakeCommented:
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
 
danblakeCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
niicoAuthor Commented:
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
 
arbertCommented:
"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
 
niicoAuthor Commented:
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
 
danblakeCommented:
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
 
danblakeCommented:
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
 
niicoAuthor Commented:
Having thought about it yes - the bulk insert was pretty inefficient.

Ive downloaded sed and it works well - thanks very much!
0
 
niicoAuthor Commented:
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
 
danblakeCommented:
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
 
danblakeCommented:
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
 
niicoAuthor Commented:
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
 
niicoAuthor Commented:
ahh got it

add > newfile.txt - to the end!

great - thanks again for all your help!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now