Solved

Read bottom line of text file into sproc

Posted on 2004-04-21
14
525 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

628 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