Importing DTS files

Question

Post reply

Importing DTS files

Vítor Nuno Viana Ferreira

SSC Veteran

Points: 225
More actions
July 25, 2005 at 2:46 am

#60855

HI,
I'm trying to import some files via DTS, but first i need to remove 3 line's from the bottom of it. I thought in use ActiveX Task to remove this line's. How can i do it in a fast way, some of this files can be real big.

Thank's,
Vítor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sara karasik SSCommitted Points: 1563 More actions · Answer 1

sara karasik

SSCommitted

Points: 1563

July 25, 2005 at 8:48 am

#576825

what type of files are you importing?

Vítor Nuno Viana Ferreira SSC Veteran Points: 225 More actions · Answer 2

Sorry,

I'm talking of flat files (csv), i'm loading Flat files into tables. But this flat files are bad formated. I have to remove the first 5 rows (easy) and the last 3 (?)!

Thanks,

Vítor

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

It is Monday morning and my brain isn't functioning fully yet so be warned!

You could use the filesystemobject along with a counter to open each file and count the rows then rewrite the file stopping at counter - 3.

Good luck,

Darrell

Vítor Nuno Viana Ferreira SSC Veteran Points: 225 More actions · Answer 4

I normaly use filesystemobjects to read files, rename files but never did this. How can i know how many rows a file has and save this file removing the last 3 rows? Is there any attribute to do it or do i have to read row by row and then write it to another file until «row» > «number of rows - 3 ».

Thanks'

Vítor

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

Unfortunately the only way I know of would be to do a readline, increment counter and then one more loop through using your example of "write it to another file until «row» > «number of rows - 3 ».".

Good Luck,

Darrell

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 6

if you read it into a string, you could do an instrrev for the 4th row delimiter, assuming there is a row delimiter after every single row, that would be your file endpoint.

you could also perform a regex for anything that has 1 row delimiter before it three row delimeters in it but nothing else after it.

if speed was really a big issue, you might try something like.... using tail (the unix utility ported) to read the last 3 lines into a var and then removed that from the end, but if they match.... you could also connect to the file as a text source via ado and issue a query where you limit the response based on a fake rowid field and max - 3.

only great thing about performing a string or regex replacement vs a loop is that i think you *could* load the file into memory faster then do your manipulation there, but it may be just as fast. you'd have to do some benchmarking.

it's way too early for me to post stuff like this =P

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 7

Easiest way would be

1. Import the Entire File to TEMP SQL Table (Table to have Identity Column)

2.Delete TOP <nn> rows based on Identity Value

3. Delete BOTTOM <nn> rows based on Identity Value

Hope this helps

Thanks

PP

paul

Hans Lindgren SSChampion Points: 10494 More actions · Answer 8

Well, it wouldn't be that easy. Vítor Nuno Viana Ferreira expressedly stated that the files were malformed. I.e not possible to Bulk Insert.