I am trying to FTP a simple Excel file from a Remote path. I have configurated the settings correct. I say that, beacause if I FTP the Same Excel file as a CSV, it FTP's without any issues
My job Design is:
FTP Plug In --> ODBC (Excel Connection)
I have verified the Excel table connectivity in the ODBC stage and it looks correct
I get the below Error Message in the job, before it Termintes
jb_FTP_XLS..ODBC_13.IDENT1: Unbalanced or unescaped quote character
At row 7, link "DSLink2", while processing column "areaname"
DSTAGE-FTP-0057`:`Unbalanced or unescaped quote character
Yes, I have tried that. That would require Fixed-Width Column property to be set to YES. The excel sheet I am trying to FTP has a defined metadata and not fixed width
Also, If i use the Binary option will it not FTP unreadable data?
Sorry, I thought you were reading the remote excel file.
If you replace your ODBC connection with a sequential file stage writing just the column "areaname" without quotes then is there a line in that file which contains quotes which may not be balanced?
Ofcourse I am trying to Read a Remote Excel file and FTP that into my System
How can I write an Excel File data into Sequential file as .xls?
I need to read a Remote .xls Excel File and FTP that into my System as .xls Excel file using FTP Plug-in Stage
I have tried reading single column before (as Ascii), I am getting the same unbalanced Error message. However, when I try your Binary approach with a Single Column and setting the Fixed Width Property set to Yes, the job runs and I get a Binary output, which is unreadable
sathya_anjan wrote:I need to read a Remote .xls Excel File and FTP that into my System as .xls Excel file using FTP Plug-in Stage
You can't. The stage is metadata driven and really only meant for flat files, which is why it works when you save the xls as a csv. Since you are on Windows, you can use ODBC to read the xls without converting it first but it still needs to be 'local' to the ETL server. If you need to FTP it first, use the command line to do it. Then there's really no need for DataStage at that point, unless you're just using it to schedule / monitor the process.
-craig
"You can never have too many knives" -- Logan Nine Fingers
As Craig has already pointed out, using FTP as a source means that the remote FTP server has to read the file, and FTP is limited to flat files.
You can FTP the Excel file (in binary mode, as mentioned earlier) to your DataStage Server and then use ODBC connectivity to read the data from the Excel file.
ArndW wrote:You can FTP the Excel file (in binary mode, as mentioned earlier) to your DataStage Server and then use ODBC connectivity to read the data from the Excel file.
When I try your Binary approach with a Single Column and setting the Fixed Width Property set to Yes, the job runs and I get a Binary output, which is unreadable. How can I read a Binary Data in a ODBC Stage?
ftp in binary-mode transfers files without trying to convert platform specific characters when moving a file from UNIX/Linux to Windows and vice versa, which ASCII-mode would do.
The result is a file that is transported unchanged from one machine to another. xls as a proprietary file-format is likely to be damaged when you try to transfer it by ftp in ASCII-mode.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
BI-RMA wrote:ftp in binary-mode transfers files without trying to convert platform specific characters when moving a file from UNIX/Linux to Windows and vice versa, which ASCII-mode would do.
I am aware about this. My FTP process is confined to Windows alone and I do not require the FTP to be set up as Binary. Ascii is working perfectly fine
Yes, it works with typical text files. But if the file contains 0x00 (nulls) then a normal FTP will interpret that as an end-of-file marker and stop. EXCEL files contain nulls and thus won't transfer correctly.
Try FTPing a .JPG or .XLS file, even between Windows or UNIX machines, and you will see corruption unless you do so in binary mode.
</a>
