Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Scientific Format In Data Warehouse Report

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

08-02-2019

Hi. I am trying to schedule a marketing cloud visitor ID report through data warehouse and when I get the excel file the ID is converted into a scientific format. I have tried to change format by using "Custom" format in excel however that results in multiple zeroes after a few digits. Also this report will be consumed as it is so can't really edit it every time before it is used. Any suggestions would be welcome.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
Employee
hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

Correct Reply

222 solutions
Top badges earned
Establish
Coach
Contributor
Shape 1
Give Back 25
View profile

Avatar
Establish
Employee
hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

Correct Reply

222 solutions
Top badges earned
Establish
Coach
Contributor
Shape 1
Give Back 25
View profile
hyderziaee
Employee

26-02-2019

Hi.

You mention that another team is going to consume this automatically. Are they going to be using Excel to check the data. The data only gets distorted when the file is opened in Excel. if the other team is not using excel, then they shouldn't have any issues.

If they are using Excel to check the data. You can probably use Report Builder. In that use the prepend function to avoid changing the value to scientific notation
Re: Report Builder keeps formatting numbers in Scientific/Text format

Regards,

Hyder

Answers (11)

Answers (11)

Avatar

Avatar
Coach
MVP
ursboller
MVP

Likes

522 likes

Total Posts

1,020 posts

Correct Reply

256 solutions
Top badges earned
Coach
Contributor
Bedrock
Seeker
Springboard
View profile

Avatar
Coach
MVP
ursboller
MVP

Likes

522 likes

Total Posts

1,020 posts

Correct Reply

256 solutions
Top badges earned
Coach
Contributor
Bedrock
Seeker
Springboard
View profile
ursboller
MVP

11-03-2019

while doing the import, in excel set the corresponding row to "string" and the mid gets displayed correctly. doing this all the time and don't have any issues with this option...

Avatar

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile
Gigazelle
Employee

11-03-2019

Did some digging, and this is specifically an issue with Excel - I'm afraid there's not much Adobe can do on this front. Excel automatically converts large numbers to scientific notation, and currently there isn't a way to turn it off.

A workaround would be to start with an empty workbook, then set all cells to the format of 'number'. You can then use the import data from csv functionality to open the file.

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

04-03-2019

Hi hyderziaee​. Tried using Report Builder but since I need this MID data correlated with pages and various events as metrics, the limitations with Report Builder do not let me get entire data. Since only Data Warehouse can be used to extract the report is there a way to get MID in the correct (non-scientific) format?

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

01-03-2019

hyderziaee​ Yes, the other team is going to use excel too. Can however try using prepend function in Report Builder to see if that works. Thanks much for the help.

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

01-03-2019

Yes. Tried that too but it didn't work

Avatar

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile

Avatar
Coach
Employee
Gigazelle
Employee

Likes

470 likes

Total Posts

1,947 posts

Correct Reply

739 solutions
Top badges earned
Coach
Contributor 2
Validate 1
Ignite 80
Ignite 70
View profile
Gigazelle
Employee

26-02-2019

Yeah, this is a weird Excel issue. Have you tried explicitly formatting the cells to be 'text'?

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

14-02-2019

Hi ishans9314858​. Tried generating the same report in report builder and faced the same issue again. So copying the data in notepad first is the only option?

Avatar

Avatar
Affirm 100
Employee
ishans52004352
Employee

Likes

153 likes

Total Posts

314 posts

Correct Reply

102 solutions
Top badges earned
Affirm 100
Give Back 10
Give Back 5
Give Back 3
Give Back
View profile

Avatar
Affirm 100
Employee
ishans52004352
Employee

Likes

153 likes

Total Posts

314 posts

Correct Reply

102 solutions
Top badges earned
Affirm 100
Give Back 10
Give Back 5
Give Back 3
Give Back
View profile
ishans52004352
Employee

13-02-2019

Hi vipuls85671841​,

I don't think Report Builder has any such columns capturing long integers. So there shouldn't be any issues using Report Builder.

Avatar

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
vipuls85671841
Level 1

Likes

0 likes

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
vipuls85671841
Level 1

13-02-2019

Thanks for your reply. This file has to be automatically consumed by another team which will not be possible with this solution. Is it going to be the same if the file is scheduled through report builder as well? Any other alternatives?

Avatar

Avatar
Affirm 100
Employee
ishans52004352
Employee

Likes

153 likes

Total Posts

314 posts

Correct Reply

102 solutions
Top badges earned
Affirm 100
Give Back 10
Give Back 5
Give Back 3
Give Back
View profile

Avatar
Affirm 100
Employee
ishans52004352
Employee

Likes

153 likes

Total Posts

314 posts

Correct Reply

102 solutions
Top badges earned
Affirm 100
Give Back 10
Give Back 5
Give Back 3
Give Back
View profile
ishans52004352
Employee

11-02-2019

Hi vipuls85671841​,

Try copying the data in a text editor like Notepad or Notepad++ first, and then copy paste in Excel. In the latter stage, you may want to delimit the values using fixed width, space and it would work.

Let me know if you run into any issues.

Avatar

Avatar
Level 1
rubenm41873595
Level 1

Likes

0 likes

Total Posts

3 posts

Correct Reply

0 solutions
View profile

Avatar
Level 1
rubenm41873595
Level 1

Likes

0 likes

Total Posts

3 posts

Correct Reply

0 solutions
View profile
rubenm41873595
Level 1

09-02-2019

Correcto

El vie., 8 feb. 2019 a las 22:38, vipuls85671841 (<forums_noreply@adobe.com>)

escribió:

Scientific Format In Data Warehouse Report created by vipuls85671841

<https://forums.adobe.com/people/vipuls85671841> in Adobe Analytics - View

the full discussion <https://forums.adobe.com/message/10921007#10921007>