Page 1 of 1
FTP Excel File
Posted: Thu Sep 27, 2012 4:13 am
by sathya_anjan
Hello,
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
Code: Select all
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
Sathya
Posted: Thu Sep 27, 2012 5:06 am
by ArndW
You need to FTP the Excel file in binary mode, have you done that?
Posted: Thu Sep 27, 2012 5:41 am
by sathya_anjan
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?
Thanks
Sathya
Posted: Thu Sep 27, 2012 5:57 am
by ArndW
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?
Posted: Thu Sep 27, 2012 7:03 am
by sathya_anjan
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
Thanks
Sathya
Posted: Thu Sep 27, 2012 7:39 am
by chulett
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.
Posted: Thu Sep 27, 2012 7:42 am
by ArndW
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.
Posted: Thu Sep 27, 2012 8:18 am
by sathya_anjan
Thanks for the clarification Craig
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?
Posted: Thu Sep 27, 2012 8:20 am
by ArndW
You need to FTP the Excel file using normal FTP and not the FTP stage. As said earlier, the FTP in DataStage is for processing remote flat files.
Posted: Thu Sep 27, 2012 8:32 am
by sathya_anjan
Alright!!
Well, I already have a Unix Script in place as a workaround, but wanted to achieve this via the Datastage FTP Stage
Thanks for your time and help
Cheers!!!
Sathya
Posted: Thu Sep 27, 2012 8:34 am
by BI-RMA
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.
Posted: Thu Sep 27, 2012 8:40 am
by sathya_anjan
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
Posted: Thu Sep 27, 2012 9:25 am
by ArndW
No, ASCII mode does not work when copying an excel file, you need to specify BINARY mode.
Posted: Thu Sep 27, 2012 10:17 am
by sathya_anjan
It does with a Unix Script
Posted: Thu Sep 27, 2012 10:35 am
by ArndW
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.