You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Paolo Spanevello <pa...@gmail.com> on 2016/01/22 15:33:15 UTC
Drill ODBC: format number on excel looks like Text
Dear All,
i'm drilling a JSON File with some fields with numbers with this format :
1s_critical_power
925.00000
I'm using ODBC Driver to connect it on excel and the result aspect is
1s_critical_power
925,00000
Do you know the right way to have it?
Best regards,
Paolo
Re: Drill ODBC: format number on excel looks like Text
Posted by Abdel Hakim Deneche <ad...@maprtech.com>.
Could this help ?
http://superuser.com/questions/385511/easy-way-to-one-off-import-data-with-different-decimal-separator-in-excel
The solution is a bit old though and newer version of Excel may not offer
the option anymore.
On Sat, Jan 23, 2016 at 3:11 PM, Paolo Spanevello <pa...@gmail.com>
wrote:
> Dear Ted,
> exactly this is the point.
>
> How could I fix this issue?
>
> Best,
> Paolo
>
> 2016-01-23 23:27 GMT+01:00 Ted Dunning <te...@gmail.com>:
>
> > Andries,
> >
> > But if you set Excel to use , as the decimal separator, then 925.0000
> could
> > be displayed as 925,0000
> >
> > Guessing by name, I suspect that Paolo is European and might have Excel
> set
> > this way.
> >
> >
> >
> > On Fri, Jan 22, 2016 at 9:26 AM, Andries Engelbrecht <
> > aengelbrecht@maprtech.com> wrote:
> >
> > > What does the JSON data look like?
> > >
> > > I did a quick test with Excel and MS Query through ODBC to connect to
> > > Drill.
> > >
> > > Selecting data as either a string or numeric value.
> > >
> > > select * from (values('925.000',925.0000))
> > >
> > > The results returned is
> > > 925.000 for the string value and 925 for the numeric value to Excel.
> > >
> > >
> > > With Drill 1.4 you can use typeof() to see what data type it being
> > > interpreted as.
> > >
> > > --Andries
> > >
> > >
> > >
> > > > On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com>
> > wrote:
> > > >
> > > > This sounds like this might be a problem of decimal point separator.
> > Can
> > > > you say what decimal point character you normally use? It might also
> be
> > > > that this is set differently on the exel machine from the machine
> where
> > > > drill is running.
> > > >
> > > > I am presuming that the value that you want to see is 925.0
> > > >
> > > > Is that correct?
> > > >
> > > >
> > > > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <
> > paolospane@gmail.com>
> > > > wrote:
> > > >
> > > >> Dear All,
> > > >> i'm drilling a JSON File with some fields with numbers with this
> > format
> > > :
> > > >>
> > > >> 1s_critical_power
> > > >> 925.00000
> > > >>
> > > >>
> > > >> I'm using ODBC Driver to connect it on excel and the result aspect
> is
> > > >>
> > > >> 1s_critical_power
> > > >> 925,00000
> > > >> Do you know the right way to have it?
> > > >>
> > > >> Best regards,
> > > >> Paolo
> > > >>
> > >
> > >
> >
>
--
Abdelhakim Deneche
Software Engineer
<http://www.mapr.com/>
Now Available - Free Hadoop On-Demand Training
<http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available>
Re: Drill ODBC: format number on excel looks like Text
Posted by Paolo Spanevello <pa...@gmail.com>.
Dear Ted,
exactly this is the point.
How could I fix this issue?
Best,
Paolo
2016-01-23 23:27 GMT+01:00 Ted Dunning <te...@gmail.com>:
> Andries,
>
> But if you set Excel to use , as the decimal separator, then 925.0000 could
> be displayed as 925,0000
>
> Guessing by name, I suspect that Paolo is European and might have Excel set
> this way.
>
>
>
> On Fri, Jan 22, 2016 at 9:26 AM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
>
> > What does the JSON data look like?
> >
> > I did a quick test with Excel and MS Query through ODBC to connect to
> > Drill.
> >
> > Selecting data as either a string or numeric value.
> >
> > select * from (values('925.000',925.0000))
> >
> > The results returned is
> > 925.000 for the string value and 925 for the numeric value to Excel.
> >
> >
> > With Drill 1.4 you can use typeof() to see what data type it being
> > interpreted as.
> >
> > --Andries
> >
> >
> >
> > > On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com>
> wrote:
> > >
> > > This sounds like this might be a problem of decimal point separator.
> Can
> > > you say what decimal point character you normally use? It might also be
> > > that this is set differently on the exel machine from the machine where
> > > drill is running.
> > >
> > > I am presuming that the value that you want to see is 925.0
> > >
> > > Is that correct?
> > >
> > >
> > > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <
> paolospane@gmail.com>
> > > wrote:
> > >
> > >> Dear All,
> > >> i'm drilling a JSON File with some fields with numbers with this
> format
> > :
> > >>
> > >> 1s_critical_power
> > >> 925.00000
> > >>
> > >>
> > >> I'm using ODBC Driver to connect it on excel and the result aspect is
> > >>
> > >> 1s_critical_power
> > >> 925,00000
> > >> Do you know the right way to have it?
> > >>
> > >> Best regards,
> > >> Paolo
> > >>
> >
> >
>
Re: Drill ODBC: format number on excel looks like Text
Posted by Ted Dunning <te...@gmail.com>.
Andries,
But if you set Excel to use , as the decimal separator, then 925.0000 could
be displayed as 925,0000
Guessing by name, I suspect that Paolo is European and might have Excel set
this way.
On Fri, Jan 22, 2016 at 9:26 AM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> What does the JSON data look like?
>
> I did a quick test with Excel and MS Query through ODBC to connect to
> Drill.
>
> Selecting data as either a string or numeric value.
>
> select * from (values('925.000',925.0000))
>
> The results returned is
> 925.000 for the string value and 925 for the numeric value to Excel.
>
>
> With Drill 1.4 you can use typeof() to see what data type it being
> interpreted as.
>
> --Andries
>
>
>
> > On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com> wrote:
> >
> > This sounds like this might be a problem of decimal point separator. Can
> > you say what decimal point character you normally use? It might also be
> > that this is set differently on the exel machine from the machine where
> > drill is running.
> >
> > I am presuming that the value that you want to see is 925.0
> >
> > Is that correct?
> >
> >
> > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <pa...@gmail.com>
> > wrote:
> >
> >> Dear All,
> >> i'm drilling a JSON File with some fields with numbers with this format
> :
> >>
> >> 1s_critical_power
> >> 925.00000
> >>
> >>
> >> I'm using ODBC Driver to connect it on excel and the result aspect is
> >>
> >> 1s_critical_power
> >> 925,00000
> >> Do you know the right way to have it?
> >>
> >> Best regards,
> >> Paolo
> >>
>
>
Re: Drill ODBC: format number on excel looks like Text
Posted by Paolo Spanevello <pa...@gmail.com>.
Thanks Andries!
With FLOAT it works properly.
2016-01-25 17:01 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>:
> The JSON data has the data type as a string, so it will require a
> conversion to a numeric type. Do you require decimal, or would FLOAT or
> DOUBLE suffice? Decimal data type is typically slower than the others.
>
> Or is it feasible to do the conversion in Excel? Setting the separator to
> "." on the import and then going back.
>
> Alternatively you can convert with Drill on the string data type by using
> regexp_replace to convert for these columns the "." to a "," , and see if
> that has the required performance compared to DECIMAL data conversion.
>
>
> --Andries
>
>
>
> On Jan 23, 2016, at 10:23 AM, Paolo Spanevello <pa...@gmail.com>
> wrote:
>
> Dear all,
>
> thanks for your reply. In the DataSet it looks with dot.
>
> "METRICS":{
> "1s_critical_power":"2.42418",
>
> and when they are imported to excel it looked string as showed into the
> previous mail.
>
> I had to use this workaround and it works for one field:
>
> select CAST(tt.flat_intervals.metrics.`1s_critical_power` as DECIMAL(28,
> 3)) as `1s_critical_power`,...
>
> but it does not work if i have 15 fields to "cast" the performance is
> incredible low until i have to kill the drill. On my microsoft excel, the
> numbers has the comma "," and the string has the dot ".".
>
> In attach you can find the dataset. Do you have any suggest?
>
> Thanks for your time.
>
> Paolo
>
>
>
>
>
> 2016-01-22 18:26 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>
> :
>
>> What does the JSON data look like?
>>
>> I did a quick test with Excel and MS Query through ODBC to connect to
>> Drill.
>>
>> Selecting data as either a string or numeric value.
>>
>> select * from (values('925.000',925.0000))
>>
>> The results returned is
>> 925.000 for the string value and 925 for the numeric value to Excel.
>>
>>
>> With Drill 1.4 you can use typeof() to see what data type it being
>> interpreted as.
>>
>> --Andries
>>
>>
>>
>> > On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com> wrote:
>> >
>> > This sounds like this might be a problem of decimal point separator.
>> Can
>> > you say what decimal point character you normally use? It might also be
>> > that this is set differently on the exel machine from the machine where
>> > drill is running.
>> >
>> > I am presuming that the value that you want to see is 925.0
>> >
>> > Is that correct?
>> >
>> >
>> > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <paolospane@gmail.com
>> >
>> > wrote:
>> >
>> >> Dear All,
>> >> i'm drilling a JSON File with some fields with numbers with this
>> format :
>> >>
>> >> 1s_critical_power
>> >> 925.00000
>> >>
>> >>
>> >> I'm using ODBC Driver to connect it on excel and the result aspect is
>> >>
>> >> 1s_critical_power
>> >> 925,00000
>> >> Do you know the right way to have it?
>> >>
>> >> Best regards,
>> >> Paolo
>> >>
>>
>>
> <rideDB.json>
>
>
>
Re: Drill ODBC: format number on excel looks like Text
Posted by Andries Engelbrecht <ae...@maprtech.com>.
The JSON data has the data type as a string, so it will require a conversion to a numeric type. Do you require decimal, or would FLOAT or DOUBLE suffice? Decimal data type is typically slower than the others.
Or is it feasible to do the conversion in Excel? Setting the separator to "." on the import and then going back.
Alternatively you can convert with Drill on the string data type by using regexp_replace to convert for these columns the "." to a "," , and see if that has the required performance compared to DECIMAL data conversion.
--Andries
> On Jan 23, 2016, at 10:23 AM, Paolo Spanevello <pa...@gmail.com> wrote:
>
> Dear all,
>
> thanks for your reply. In the DataSet it looks with dot.
>
> "METRICS":{
> "1s_critical_power":"2.42418",
>
> and when they are imported to excel it looked string as showed into the previous mail.
>
> I had to use this workaround and it works for one field:
>
> select CAST(tt.flat_intervals.metrics.`1s_critical_power` as DECIMAL(28, 3)) as `1s_critical_power`,...
>
> but it does not work if i have 15 fields to "cast" the performance is incredible low until i have to kill the drill. On my microsoft excel, the numbers has the comma "," and the string has the dot ".".
>
> In attach you can find the dataset. Do you have any suggest?
>
> Thanks for your time.
>
> Paolo
>
>
>
>
>
> 2016-01-22 18:26 GMT+01:00 Andries Engelbrecht <aengelbrecht@maprtech.com <ma...@maprtech.com>>:
> What does the JSON data look like?
>
> I did a quick test with Excel and MS Query through ODBC to connect to Drill.
>
> Selecting data as either a string or numeric value.
>
> select * from (values('925.000',925.0000))
>
> The results returned is
> 925.000 for the string value and 925 for the numeric value to Excel.
>
>
> With Drill 1.4 you can use typeof() to see what data type it being interpreted as.
>
> --Andries
>
>
>
> > On Jan 22, 2016, at 8:50 AM, Ted Dunning <ted.dunning@gmail.com <ma...@gmail.com>> wrote:
> >
> > This sounds like this might be a problem of decimal point separator. Can
> > you say what decimal point character you normally use? It might also be
> > that this is set differently on the exel machine from the machine where
> > drill is running.
> >
> > I am presuming that the value that you want to see is 925.0
> >
> > Is that correct?
> >
> >
> > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <paolospane@gmail.com <ma...@gmail.com>>
> > wrote:
> >
> >> Dear All,
> >> i'm drilling a JSON File with some fields with numbers with this format :
> >>
> >> 1s_critical_power
> >> 925.00000
> >>
> >>
> >> I'm using ODBC Driver to connect it on excel and the result aspect is
> >>
> >> 1s_critical_power
> >> 925,00000
> >> Do you know the right way to have it?
> >>
> >> Best regards,
> >> Paolo
> >>
>
>
> <rideDB.json>
Re: Drill ODBC: format number on excel looks like Text
Posted by Paolo Spanevello <pa...@gmail.com>.
Dear all,
thanks for your reply. In the DataSet it looks with dot.
"METRICS":{
"1s_critical_power":"2.42418",
and when they are imported to excel it looked string as showed into the
previous mail.
I had to use this workaround and it works for one field:
select CAST(tt.flat_intervals.metrics.`1s_critical_power` as DECIMAL(28,
3)) as `1s_critical_power`,...
but it does not work if i have 15 fields to "cast" the performance is
incredible low until i have to kill the drill. On my microsoft excel, the
numbers has the comma "," and the string has the dot ".".
In attach you can find the dataset. Do you have any suggest?
Thanks for your time.
Paolo
2016-01-22 18:26 GMT+01:00 Andries Engelbrecht <ae...@maprtech.com>:
> What does the JSON data look like?
>
> I did a quick test with Excel and MS Query through ODBC to connect to
> Drill.
>
> Selecting data as either a string or numeric value.
>
> select * from (values('925.000',925.0000))
>
> The results returned is
> 925.000 for the string value and 925 for the numeric value to Excel.
>
>
> With Drill 1.4 you can use typeof() to see what data type it being
> interpreted as.
>
> --Andries
>
>
>
> > On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com> wrote:
> >
> > This sounds like this might be a problem of decimal point separator. Can
> > you say what decimal point character you normally use? It might also be
> > that this is set differently on the exel machine from the machine where
> > drill is running.
> >
> > I am presuming that the value that you want to see is 925.0
> >
> > Is that correct?
> >
> >
> > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <pa...@gmail.com>
> > wrote:
> >
> >> Dear All,
> >> i'm drilling a JSON File with some fields with numbers with this format
> :
> >>
> >> 1s_critical_power
> >> 925.00000
> >>
> >>
> >> I'm using ODBC Driver to connect it on excel and the result aspect is
> >>
> >> 1s_critical_power
> >> 925,00000
> >> Do you know the right way to have it?
> >>
> >> Best regards,
> >> Paolo
> >>
>
>
Re: Drill ODBC: format number on excel looks like Text
Posted by Andries Engelbrecht <ae...@maprtech.com>.
What does the JSON data look like?
I did a quick test with Excel and MS Query through ODBC to connect to Drill.
Selecting data as either a string or numeric value.
select * from (values('925.000',925.0000))
The results returned is
925.000 for the string value and 925 for the numeric value to Excel.
With Drill 1.4 you can use typeof() to see what data type it being interpreted as.
--Andries
> On Jan 22, 2016, at 8:50 AM, Ted Dunning <te...@gmail.com> wrote:
>
> This sounds like this might be a problem of decimal point separator. Can
> you say what decimal point character you normally use? It might also be
> that this is set differently on the exel machine from the machine where
> drill is running.
>
> I am presuming that the value that you want to see is 925.0
>
> Is that correct?
>
>
> On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <pa...@gmail.com>
> wrote:
>
>> Dear All,
>> i'm drilling a JSON File with some fields with numbers with this format :
>>
>> 1s_critical_power
>> 925.00000
>>
>>
>> I'm using ODBC Driver to connect it on excel and the result aspect is
>>
>> 1s_critical_power
>> 925,00000
>> Do you know the right way to have it?
>>
>> Best regards,
>> Paolo
>>
Re: Drill ODBC: format number on excel looks like Text
Posted by Ted Dunning <te...@gmail.com>.
This sounds like this might be a problem of decimal point separator. Can
you say what decimal point character you normally use? It might also be
that this is set differently on the exel machine from the machine where
drill is running.
I am presuming that the value that you want to see is 925.0
Is that correct?
On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <pa...@gmail.com>
wrote:
> Dear All,
> i'm drilling a JSON File with some fields with numbers with this format :
>
> 1s_critical_power
> 925.00000
>
>
> I'm using ODBC Driver to connect it on excel and the result aspect is
>
> 1s_critical_power
> 925,00000
> Do you know the right way to have it?
>
> Best regards,
> Paolo
>