I am using DS in order to create a job which includes an Excel worsheet and simply create a table within my repository.
When I set up the workbook the schema I create is that the fields are ALL varchar with respective sizes.
But when I run the job although it creates the table and rows are added correctly it padded one of the columns with “.000000”
This column has mostly numeric values aside from 3 rows being a 5 character field and the column is set to varchar ??!!
I mean come on. You have a field or cell in an excel spreadsheet whose contents are predominantly numeric. A few of the fields have ABCD text.
You have the format set to be TEXT
You get 12345.0000 and for the fields set to ABCD you get NULL !!!???
You need to delete the job, the workbook you created, drop and kill the table about 3-4 times.
You need to edit and resave your Excel worksheet with text format about 5+ times.
You need to create the job and run it about 7 times.
You need to put a quote on the first cell in the column that you want the numbers to be text. Then remove it.
You need to try and use the to_char function within DS and remove it abot 4 times.
Then when you did all of that and run it again it will work. It will not pad any cells with zeros and the text message ABCD will be there as ABCD instead of Null.
So, somewhere in that spaghetti garbage lies the fix. Does anyone else know of it !!?? SAP certainly does not.
Where are the old BO gurus who used to work there ? I miss them
I recall having an issue like yours some years ago whilst using 11.7, but I did not raise it with SAP.
The issue was despite manually setting the data types in the File Format schema for the Excel columns as varchar, if the first row in a column was numeric, it would cause problems when extracting the data into a table and would assume all columns values were numeric.
Workaround was to have a dummy row containing varchars under the header row as the first “data” rows and before the actual data of interest, this dummy row could be filtered using a query transform as part of the extract dataflow.
What version of Data Services are you using as you do not say?
My issue is even worse. The column is defined as Varchar(50) the data in the cell, in the 10th row is “Hello” with everything above it blank.
What happens?
Everything comes through as NULL. Because the first row is blank but formatted to cell type DATE. When it reaches the word “Hello” it sets it to NULL because it’s not a valid date, even though the input specification states it should be loaded as a Varchar.
To fix the ,000000 issue in SAP DS using excel as a data source.
Go to the project then Formats and find the excel source you created or use, right click and view the Data Type. Now see the double under data type. These need to change tp another data type, I changed mine to varchar, and then later in a transform to which ever format I need.
After amending the data types, open the job and select the view (magnifying glass) on the excel source.
The data is displayed without the ,000000 then. You might want to check all columns, if you missed one.
(BOB member since 2007-11-20)
(BOB member since 2012-03-20)