For example let us have date cell with format mm.YYYY or mmm.YYYY of value 1st january 2014
When i save my document it displays 01.2014 or Янв.2014
Anyway when I read string values at the same(!) machine i get 01/2014 or Jan/2014
What can affect this and how can I vary this regardless of any setting except those set in the graph?
I had an idead about targer string field locale, but it seems not to help me.
When i save my document it displays 01.2014 or Янв.2014
Anyway when I read string values at the same(!) machine i get 01/2014 or Jan/2014
What can affect this and how can I vary this regardless of any setting except those set in the graph?
I had an idead about targer string field locale, but it seems not to help me.
-
Hi golomeen,
You should use date type in CloverETL metadata instead of string. Format of the field would be again MM.yyyy. If you insist on having the date in a string variable, you can retype it using Reformat with CTL function string date2str(date input, string pattern). More information on this topic is here: http://doc.cloveretl.com/documentation/ ... ormat.html
Regards, -
Thank you for reply. It is an obvious workaround.
But the users that fill the data in MS Excel do not have the option to code Reformat :) and I cannot make them use some complex cell tuning instead of simple and well-known cell formatting. In my case it can be date, integer, float, formula etc and I should not care what type the data is at all. It is preferrable for people to see common string data they enter and format. If they saved 01.2014 and then they see 01/2014, it is strange for them. -
I think there is still some misunderstanding in our posts. I thought that you just read the Excel files but now it seems that you both read them and write into them. Could you please provide more details about your task?
Do you read and write into the same file? Do you use template file in your Writer? Do you use SpreadsheetDataWriter or XLSDataWriter? Any further information would be appreciated.
Thanks. -
ОК, I will do my best.
Our task is data visualizing. The data is entered by the users via Excel files and then prepared by Clover
Sheet structure is
Title Var1 Var2 etc
Title can be any string while VarN is number, but Vars do not matter in this topic. As everyone got used with Excel, there are different ways to enter data there. You can write char by char 'January 2014', 'February 2014' and so on. And you can enter '1.14', then drag the cross to autofill cells, then set the format to get those Jans and Febs. It is easier in Excel. And sometimes Excel even autodetects format but it can be transparent to user - if the value remained unchanged.
My goal is not to care what type remained in the cell, but to satisfy user and display the same string/text/title he's seen in the cell before saving. Still not restricting users in the ways of supplying the data. -
I have just tried to solve this task in my graph. I created a new Excel spreadsheet:
cell A1 with value February 2014 formatted as text cell
cell A2 with value 4.14 formatted in custom cell format MMMM yyy to value April 2014
cell A3 with value 1.7.2014 formatted in the same custom cell format to value July 2014
Then I read this file with my graph containing just SpreadsheetDataReader and Trash with metadata containing nothing else but one string field. The result was:
February 2014
April 2014
July 2014
This is exactly what I expected to get and if I understand correctly, it is what you want to get as well. Am I missing something? -
Thank you again.
I guess you will get the difference if you set system locale to Russian during working with the sheet in Excel. Now you have the locale that is much like English.
Russian name of January is "Январь". Set any locale to see anything except "January" in the cell, "Январь" for example. Then execute the graph and get January anyway. I need my Январь still :)
Let's post a screenshot
http://savepic.ru/5774877m.png -
Locale can be set in a CloverETL metadata field too, of course. For example if I set ru.RU to my only string field, A1 value does not change as it is a text cell but A2 changes to Апрель 2014 and A3 changes to Июль 2014.
http://doc.cloveretl.com/documentation/ ... ocale.html
I hope this helps.
Please sign in to leave a comment.
Comments 8