I have an automation script setup that pulls a report and pre-formats it for me and outputs the contents as a CSV file.
The downstream application that I import this file into tells me that the file is in binary format - I have to open the file in excel and resave over itself in order for the application to accept it.
Is there a workaround for this?
What automation activities are you using? Can you just export to excel in the first place?
Debbie
Set Variables to Date & Time
Select to File then email.
I could, but, the report is submitted to another system to process changes, and it must be in CSV format. Saving as excel, emailing, saving the file again as CSV and finally uploading is a few too many steps.
Then when I got the processed csv back from the other system I would import it into a temparary table and export to Excel. You can use the ToadSample Access database if you don’t have another database to do this with.
Debbie
I think I may have confused my question 
TDA is generating a report, that I then have to import into another system for it to do it’s processing - I don’t have a choice what format that system uses, it only takes CSV files through a web interface.
The issue is that the CSV file that Automation is producing is being interpreted as a binary file - however, if I open the report in Excel and then save it over itself, it’s recognized as a text file.
What I’m trying to figure out is if there’s a reason why the Automated report is being considered a binary file, and if there’s a way to work around that.
That is different. What is the other system? We would have to know why it was determining it was a binary. Does it take any other format such as Tab Delimited text? Does it state exactly what format it wants the file in? If we started with what type of file it does accept, then maybe we can work backwords.
Debbie
It’s an internal application that manages aspects of our billing system enterprise-wide - specifically address and serviceability information. It does not contain any client or billing information - just addresses.
The file being produced from TDA is a listing of accounts that need to have certain flags changed based on customer information in the billing system. I’ve set up my format to be the same as that needed by the other system - so it should be a simple extract, then upload and done.
I don’t know why it was determining it was binary - it just gave me a message that the file was in binary format. When I opened the file - Excel recognized as CSV - and re-saved it over itself, the system took it just fine.
The only type of file it accepts is a comma delimited text file.
The automation is set to run again at 5am tomorrow morning - I’ll see what happens then as well.
There were no results yesterday, so I couldn’t test.
The results from this morning’s run were the same - the file that the Automation script produced were considered “binary” until I opened them in Excel and manually resaved as CSV formatted files.
I see that there is an option for using the Export Wizard, which will allow me to export Text files from a query - the question is, can I use variables (for the file name) within the export wizard?
I had to do some digging, but I figured out what the problem is.
The “Select to File” function in TDA 3.0 defaults to using UTF-8 encoding, even when creating a CSV file. UTF-8 encoding makes the file a binary file. There are no options when selecting “Comma separated delimited file” for any type of encoding (perhaps a future enhancement?).
I was forced to switch to using an Export template which does allow me to force a specific file encoding. Using the Export template and exporting to a comma separated text file forced to US-ASCII resolved the problem - the file is now in ANSI format by default, which is a text format.
The only issue that I’m having is that the Export Template is: A) Taking longer with the automation; and B) creates a bigger hit against the system resources.
If an option could be added to the “Select to File” when using a text file format (as CSV should be considered) that allows you to force the file encoding, that would be awesome.
I don’t remembe on this one. I suspect not. You can always do the export to a static filename and then use the copyfile to change the name to a file name that includes a variable name.
Debbie
Glad you figured out the issue. I would not have thought about the encoding. We can add your request. I entered CR96677 for this. It won’t be in the TDA 3.1 or 3.2 release but we can schedule for 3.5.
Debbie
Well, as it turns out - yes, you can use variables in the Export template. Just thought I’d share
Debbie Peabody wrote:
I don’t remembe on this one. I suspect not. You can always do the export to a static filename and then use the copyfile to change the name to a file name that includes a variable name.
Debbie