Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read bottom line of text file into sproc

Posted on 2004-04-21
14
Medium Priority
?
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

660 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