Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Saving Binary Database field to file.
Topic Summary: Save file from a binary database field to flat file.
Created On: 10/09/2014 03:37 PM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by Leonard Amabile, on Sunday, October 26, 2014 4:35 PM

Answer:
You would reference them in the same fashion that you would reference a VB script variable.
 10/09/2014 03:37 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Hi there

I have a SQL Server database that stores Tax Invoice PDFs in an image field.  I am trying to use automate to save the files to a flat file.

If I manually save the file it is 5K.  If I use write file step to save the file it comes out to 1K and is currpted according to my PDF Viewer.

I have tried converting it from image to varbinary(max) but it has the same issue.

Is there something that I am missing?

Cheers



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003


Edited: 10/09/2014 at 05:48 PM by PhillipWilkie
 10/10/2014 09:35 AM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hi Phillip,

Would it be possible to post the file so that we can try reproducing your issue internally?

 Category Survey


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
 10/12/2014 12:29 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Please find attached the source document and the Task file.  The source document is stored ina SQL Server image field.  This is converted to a varbinary(max) in a view.

As can be seen in the task Steps.  The view data is held in a dataset.

Cheers

Phillip



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003



 10/14/2014 03:52 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Phillip,

Are you attempting to save the actual binary *.pdf file as a flat ASCII file? That is not possible.
Or are you attempting to read the contents of the *.pdf, store it in a variable and write the contents of the variable to a flat ASCII file?

There is an OCR action but that is only available in version 9 and higher.

Try the attached task I've created. Perhaps this will help.
Simply have the 'TaxInvoice_TEST_GEN_20140815.pdf' file present on your Desktop
The output file is created at C\:temp\




 Category Survey
AutoMate 7 version:
Windows version:


PDF to TXT.aml
PDF to TXT.aml  (1 KB)



-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665


Edited: 10/14/2014 at 04:23 PM by Valentineo McGowan
 10/14/2014 04:23 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

I am trying to work out how to save the binary data from the database to a flat file so that it can be opened by a PDF viewer and sent via FTP. 

I tried using the WriteToFile step with no success

I also tried writing from a variable with the same result.

The only resason that I am writing it to a file is so that I can verify the data.  If I can send the binary directly from the dataset via ftp the would be acceptable as I could send it to a test server and verify it on the server.

Thanks
Phillip



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 10/14/2014 04:31 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Phillip,

My apologies I'm not clear. In the first post you mentioned you wanted to save the pdf to text. Is this not the case?
Did you see the attached task on my last post?
Does the %TaxInvoiceData.CsvFileName% include a*.csv extension on the end of the file during creation? If not, trying editing step 20 to include .csv after the %



 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665
 10/15/2014 12:22 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Valentineo

I did not say I wanted to save a PDF to Text.  I said I want to save a PDF which is stored in a database field to a flat file.

I did see you attachment however this simply copies the test from a PDF file to a text file.  I want to use automate to save the file from the database to the file system.

Yes.  the filename is stored in a field in the database which is retreived into the dataset field.

I am wondering if the issue is caused by a % character in the binary file data?

Your Thoughts?



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003


Edited: 10/15/2014 at 12:31 PM by PhillipWilkie
 10/15/2014 12:24 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

My description must not have been clear.  I am not trying to copy a PDF to txt .  I am trying to save a PDF from a database binary field to a file and open it in a PDF viewer.



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 10/15/2014 05:09 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Phillip,

You may not be able to do this in v7.
Also if you're storing the binary data from a previously read PDF file then you will have to store it within a variable so it can be written to a flat txt file so that file can then be later read and a PDF file can be created from that. We have a new PDF action in our version 10 product that creates PDF's.

Otherwise your task is attempting to write binary data to a flat file which can not be read by Adobe Reader because the outputted file is not formatted properly to be read by Adobe Reader.


 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665
 10/15/2014 05:19 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Let me rephrase the question.  Can you read a binary data from a database and write the bytes to the file system.  Then open it in whatever application would normally read it?

 

This could be an image, document, spreadsheet, pdf or a porpriety binary file format.

 

Similar to a filestream write in VB.



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 10/16/2014 04:54 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Phillip,

If we to use AutoMate's Write To File action and create a file with a txt or csv extension that contained simply:
1, 2 (<--- a one a comma a space and a two)
Theoretically Excel would be able to open both files and read their contents and display them accordingly on A1 & B1 within the worksheet.
Because Microsoft Excel is able to read properly formatted 'text' and 'comma separated value' files.

AutoMate 7 currently as no way to output to a file that is properly formatted or converted in such a way that Adobe Reader will be able to read the file and display as visual text as seen normally within 'Portable Document Format' files.

As Ive stated previously our latest version 10 product creates PDF's.

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665
 10/19/2014 08:18 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

OK... Since binary files are not a option in V7.  I have written a script to do the same thing.  This works fine.  Is there a way to pass in a paramater to the script or access a variable from the task that is hosting the script step.

I would like to Pass in the FullFilePath and Invoice No to the script.



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 10/20/2014 04:25 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Phillip,

Would you like AutoMate to amend or parse your script and place the path of the file, invoice number and/or anything else that is contained within the dataset? If so you could use a write to file action.
Or you can write all the pertinent data to a separate flat ASCII file then have a script read it and amend your script accordingly.

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665
 10/20/2014 04:39 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

This is extremely inefficent.  If I have 5000 tax invoices (not an extreme case) to send then I would need to write the invoice number to a file 5000 times, and then read it just as many.  Not good at all. 

 

I assume from your response parameters cannot be passed into a script even though its VB.



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 10/23/2014 03:26 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Originally posted by: PhillipWilkie

OK... Since binary files are not a option in V7.  I have written a script to do the same thing.  This works fine.  Is there a way to pass in a paramater to the script or access a variable from the task that is hosting the script step.


I would like to Pass in the FullFilePath and Invoice No to the script.


Phillip,

Yes you can. You can create AutoMate variables with the parameters you want and then reference them within the VB script. I'm assuming you are using our BASIC script action to embed to invoke the VB script?

We have also had other users use the decode64() and encode64() functions in automate to create .pdf files from base 64 encoding/decoding algorithm.


 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665


Edited: 10/23/2014 at 03:33 PM by Leonard Amabile
 10/23/2014 03:47 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Yes I am using Basic Script.

I have a variable called CurrentInvoiceNo and FullFilePath create and populated before the script step.  How would I reference them in the script itself?

Thanks.

 



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 10/23/2014 03:54 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Answer Answer
You would reference them in the same fashion that you would reference a VB script variable.

 Category Survey
AutoMate 7 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 10/26/2014 04:35 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

Leonard,

 

I appears that I was doing things as per your instruction but the debug.print as not outputting the variable as I expected.  as soon as I changed it to a MsgBox all was good.  Have now substituted all of the values and working like a dream.

 

Thanks a lot



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 04/09/2015 03:21 PM
User is offline View Users Profile Print this message

Author Icon
PhillipWilkie
Apprentice (20-49)

Posts: 29
Joined: 10/09/2014

My solution is quite simple.  I have sritten a script that uses the dataset results to call a script that connects to the db and dosnloads the actual document.  The issue is that script cannot be put inside a look or it gets an already definied error.  So I have scheduled the task to run every second this appears to be the only workaround to the situation.



 Category Survey
AutoMate 7 version: 7.1.2
Windows version: Windows 2003
 04/10/2015 11:58 AM
User is offline View Users Profile Print this message

Author Icon
Alex Escalante
HelpSystems

Posts: 871
Joined: 02/26/2014

Hello Phillip,

Thank you for posting your workaround/solution.

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Alex Escalante | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 2108 guests browsing this forum, which makes a total of 2108 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog