BULK INSERT to skip last x rows 1

[MKVAB]

Hello all!
I am writing a BULK INSERT statement that loads a variable length file that I receive daily. However, the last lines of this file contain a few lines of HTML that is causing the BULK INSERT to fail. Is there a way I can have BULK INSERT load all records and stop when it sees a certain string. Or, is there anyway to strip these few lines out of the file automatically?

The section that is causing it to fail looks like this:


<br>
<h3>Summary:</h3>
<table border=1 cellspacing=0 cellpadding=2>
</table>
<br><b>Total Successful</b><br>


Any ideas are greatly appreciated!
Thank you!
-MK

 

[philhege]

I'd write a preprocessor that strips the HTML from the file.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[MKVAB]

Thanks Phil!
Not to sound too green, but what would I use to strip out lines in a file? Is there a way to do this from within SQL Server?

Thanks again!
-MK

 

[ESquared]

If you can get the number of valid lines before the operation, you could use the BATCHSIZE parameter in BULK INSERT so all the good rows get added and the remainder fails. It's not really the *best* solution, but it might work.

BATCHSIZE [ = batch_size ]

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is one batch.

 

[MKVAB]

Thanks ESquared!
I was thinking something similar to that too.
If I could get the count of the number of lines in the file, I could set LASTROW = that number - 5 (5 being the static rows at the end of the file).

But, I haven't figured out how to do that yet.

I'm going to try to get one of these two to work.

Again, thank you!
-MK

 

[ESquared]

You can manipulate files with shell routines from inside SQL server...

But probably the best method is just the preprocessing as someone else mentioned.

 

[philhege]

MKVAB et al.,

A present (to get you started):

'==========================================================================
'
' VBScript Source File -- Created with SAPIEN Technologies PrimalSCRIPT(TM)
'
' NAME: <filename>
'
' AUTHOR: philhege
' DATE : 6/16/2004
'
' COMMENT: Opens a text file, shows contents, alters
' contents based on the encounter with an HTML <BR> tag
'
'==========================================================================

Dim fso
Set objArgs = WScript.Arguments
' file name passed as a parameter; this assumes only one parameter
For Each strArg in objArgs
procfile = strArg
Next

' create the file system object
Set fso = CreateObject("Scripting.FileSystemObject")
' get the file to process
set workfile = fspenTextFile(procfile)

textpart = workfile.readall
newtext = mid(textpart,1,InStr(textpart,"BR")-2)
WScript.echo textpart
WScript.Echo "AFTER: "+newtext
workfile.close
set workfile = fspenTextFile(procfile,2)
workfile.write(newtext)
workfile.close
Set workfile = fspenTextFile(procfile)
newtextpart=workfile.readall
WScript.Echo newtextpart



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[ESquared]

Be careful to search for text that definitely won't be in the data... maybe search from the end of the file rather than the front. InstrRev or the equivalent logic.

 

[philhege]

Right, thanks. I flailed around with this at the end of the day, so it's definitely buyer beware.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.