Post reply

Parsing File Names into Data Elements

  • March 17, 2014 at 9:38 am

    #305307

    I need to import some html files and some bmp files into a database. The old legacy system which creates the files generates file names with imputed meaning. The files are depictions of invoices along with the signatures captured by touch screen at the time of delivery. The file names appear as CustomerNumber-Date-InvoiceNumber.htm for the depiction of the invoice and a seperate file named CustomerNumber-Date-InvoiceNuber.bmp that contains an image of the signature. I need to write an SSIS routine that will put these files into SQL tables.

    Inside the htm file the line of code that displays the image of the signature is written to refer to the bmp file. Here is an example:

    <IMG SRC=00013239-20140218-29900055.bmp ALT="00013239-20140218-29900055.bmp not found 2">

    Is this even possible? The end goal is to write an SSRS report that would allow a user to search for and display invoices along with the captured signature.

    Any ideas to get me started on a workable solution are appreciated.

  • March 19, 2014 at 4:12 am

    #1698293

    Since nobody else has ventured an answer, I guess I'll try it...

    Importing the HTML text shouldn't be a big deal, because it's just ASCII text. If you declare your column name as VARCHAR(MAX), you can stuff all the HTML into a single field.

    Grabbing the image file path... This is as far as I got...

    DECLARE @HTMLChunk VARCHAR(500) = '<IMG SRC=00013239-20140218-29900055.bmp ALT="00013239-20140218-29900055.bmp not found 2">';

    DECLARE @SignatureFile VARCHAR(500);

    SELECT @SignatureFile = RIGHT(LEFT(@HTMLChunk,CHARINDEX(' ',@HTMLChunk,CHARINDEX('SRC=',@HTMLChunk))),LEN(@HTMLChunk)-9);

    SELECT @SignatureFile = RIGHT(@SignatureFile,LEN(@SignatureFile)-LEN('<IMG SRC='));

    PRINT 'Signature File Name: ' + @SignatureFile;

    I would assume that if you can parse out the image file into the @SignatureFile variable, you could pass that around in your package and stuff it into a filestream or binary column for storage.

Viewing 2 posts - 1 through 1 (of 1 total)

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