exporting dataset from Dataiku to Excel changes the cells format from Text to Number

mirel29
Level 2
exporting dataset from Dataiku to Excel changes the cells format from Text to Number

When I export a dataset to Excel or CSV, for the cells which are numbers, in Excel file, the cell format is changed to "Number", even if in the dataset the value type was Text (string).

How can be stopped this change to happen when exporting data to Excel?

 

New Bitmap Image.jpg


Operating system used: Windows


Operating system used: Windows

โ€ƒ

0 Kudos
10 Replies
tgb417

@mirel29 

Welcome to the Dataiku Community.

There are a number of different ways to save MS Excel files from Dataiku.  Can you clarify which method you are using?

In the meantime.  This thread might be of interest.

https://community.dataiku.com/t5/Using-Dataiku/Schema-changes-from-double-to-integer-all-the-time/m-...

https://community.dataiku.com/t5/General-Discussion/Fill-Pattern/m-p/21112

Let us know how you are getting along.

--Tom

--Tom
0 Kudos
mirel29
Level 2
Author

Well, first, I used a scenario which sends an email having attachment the dataset converted to Excel.

Secondly, I did the same, but converted to CSV. I got the same result.

Thirdly I download it manually from the flow as Excel then as csv.

Every time Dataiku change the Numbers stored as text to Number Format. 

The reason to keep them as text is that the numbers have 0 in front of the number and it will dissapear, which I dont want to.

Many Thanks for your help

0 Kudos
AlexandreL
Dataiker

Hi,

This issue seems to be related to the way Excel imports files. I tested on my side on a column with ids and leading 0s, and 0s are preserved at export. However, excel auto-converts such columns to integers at import.

You can export the data as csv and use Excel's import wizard to prevent it from converting the column to number types automatically. You can have a look at Microsoft's documentation here: https://support.microsoft.com/en-us/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857#:....

Hope this will help fixing your issue.

Best,

mirel29
Level 2
Author

As I mentioned initially in the issue description, I already testes also to export as CSV and I got the same problem.

It is strange that for you this issue is not reproduced when export to excel.

As I see is the Dataiku is the one who change the format of data, not the Excel.

0 Kudos
tgb417

@mirel29 

It seems like we are making some progress.  Thanks for getting back with further details.  Thanks to @Manuel and @AlexandreL for jumping in on this one.

One additional point that may help with opening your CSV files in MS Excel.  There are actually two ways to open a CSV file in MS Excel

1. By double clicking in windows file explorer, or the Macintosh finder.  When opening in this way MS Excel does itโ€™s best to interpret the .csv and we donโ€™t want that to happen

2. By opening MS Excel first and going to File Open and selecting your CSV file you are put through a 4 or 5 step process to import your file.  On the second or third page of the dialog box you are offered the opportunity to declare what kind of data you have in each column.  โ€œgeneralโ€ is the default.  In this case that is not want for these columns with pretended 0s.  We want to choose text.

--Tom
0 Kudos
mirel29
Level 2
Author

Hi Tom

Thanks for the tips. I'll test them.

All the best to you

0 Kudos
mirel29
Level 2
Author

I agree that, this if you want to avoid losing leading "0" you need to apply Excel workaround as you mentioned.

Thank you for your help

0 Kudos
mirel29
Level 2
Author

I made a check using a wordpad to open the csv file received from Dataiku and the 0 was in front of the number.

So, you are right, Excel is changing the format of the cells.

0 Kudos
Manuel
Dataiker Alumni

Hi,

Like Alexander says, Excel behaves like this.

Within Excel you can prefix values with ' (for example '0787432432), to assume the value as string instead of number.

Within DSS, perhaps you can concatenate this character to the values, immediately before creating the export file.

I hope this helps.

Best regards

0 Kudos
mirel29
Level 2
Author

Yes, this solution with adding prefix values ' (for example '0787432432) which assume the value as string instead of number it is a possible solution which implies further receipe to be added.Thanks

0 Kudos