+ Reply to Thread
Results 1 to 24 of 24

Import CSV to Data Model - Change decimal separator

  1. #1
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Import CSV to Data Model - Change decimal separator

    Hi,

    My CSV file has too many rows to fit in a sheet, so I import it to the data model instead.
    Then I can use it when I create a Pivot table (Create PivotChart - Use this workbooks Data Model).

    The problem is the formatting of a column with numbers. Because "." is used as decimal separator the column is stored as text.
    So when I open "Manage Data Model" and try to change Data Type to 'currency' I get an Type mismatch error.

    How can I change the decimal separator for a column in the Data Model?

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Import CSV to Data Model - Change decimal separator

    Hi,

    Have you tried changing the Locale when importing? Click 'From Text'- Advanced.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Yes, I tried both American and Norwegian for locale but I couldn't see any difference.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Import CSV to Data Model - Change decimal separator

    Can you upload a small sample of the csv? Also, which version of Excel do you actually have- clearly not 2010 if you have a Manage Data model button.

  5. #5
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    I have the latest 365 monthly channel version, build 1710.
    But I'm just learning all the new features

    Here's a screenshot from the Data Model:
    screen.PNG

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Import CSV to Data Model - Change decimal separator

    I suggest you update your profile then.

    A screenshot doesn't really help- can you post a sample?

  7. #7
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    The csv har "." in the numbers, like this... "NO","-129439.98",

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    Quote Originally Posted by xlnitwit View Post
    Can you upload a small sample of the csv?
    Quote Originally Posted by xlnitwit View Post
    can you post a sample?
    Try read with understanding

  9. #9
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Not sure how I upload a file, but I guess copy paste this to sample.csv
    E.g. Pivot: Rows: FRA_TIL_LAND, and Values: Sum of BOKF_BELOP

    "FRA_TIL_LAND","BOKF_BELOP","BEREGNET_BELOP"
    "NO","-129439.98","0"
    "NO","-43802.74","0"
    "NO","-19676.85","0"
    "NO","-144310.73","0"
    "NO","-433636.69","0"
    "NO","-238330.94","0"
    "NO","47.25","0"
    "NO","-425703.00","0"
    "NO","-72422.69","0"

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    To attach an CSV file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  11. #11
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Sorry, trying now...
    Attached Files Attached Files

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    Ok, now I leave you in a good, xlnitwit hands

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    but if you want change dot to comma use :
    New Query
    From File
    From CSV
    Load as connection
    select appropriate column
    change to text
    replace dot with comma
    change to decimal

    all depends on your local settings
    Last edited by sandy666; 12-06-2017 at 08:06 AM.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Import CSV to Data Model - Change decimal separator

    In theory with Power Pivot, you can create a text file called Schema.ini in the same folder as your csv file and add the following to it

    [sample new.txt]
    Format=CSVDelimited
    MaxScanRows=0
    ColNameHeader=True
    DecimalSymbol=.

    the red part should be changed to match the name of your file. Then when you import the file, the settings from schema.ini should override the default ones.

    In practice, I cannot get that to work with Power Pivot in 2010 but as a temporary workaround you might change your regional settings to say UK English before starting Excel and loading the CSV into PowerPivot. If you go via Get and Transform, I think you have more options for setting the decimal separator during that import process.

  15. #15
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Thanks for the replies, but no dice yet
    The Table Import Wizard recognised the .ini file, but I could still not change the column to numbers.
    I also tried to set the Options->Advanced->Decimal separator to ".", but even this didn't help.

    I think MS just forgot about this functionality when they came up with Data Model...

  16. #16
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Import CSV to Data Model - Change decimal separator

    I always recommend avoiding direct query import into Data Model.

    Query data and perform any necessary transformation steps in PowerQuery/Get & Transform.
    Once you make sure data is clean and is in format you want, load it to Data Model.

    This will ensure proper data set up before it gets loaded to Data Model and will avoid future issues, if some fields or setting change in the backend.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    Quote Originally Posted by jawaka View Post
    I think MS just forgot about this functionality when they came up with Data Model...
    ... or user don't know how to do it

    post#13
    prepare data in PQ then load clean data into DataModel

    or use PivotTable directly from PQ
    Last edited by sandy666; 12-06-2017 at 09:15 AM.

  18. #18
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Quote Originally Posted by CK76 View Post
    I always recommend avoiding direct query import into Data Model.

    Query data and perform any necessary transformation steps in PowerQuery/Get & Transform.
    Once you make sure data is clean and is in format you want, load it to Data Model.

    This will ensure proper data set up before it gets loaded to Data Model and will avoid future issues, if some fields or setting change in the backend.
    I tried this also. I imported the CSV to a sheet and used "Text to Column" to fix the column. I still didn't have enough rows, but I managed to load it into the Data Model.
    But now the Data Model is based on the table sheet, so rows are still missing.

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    I give up

    have a nice day

  20. #20
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Import CSV to Data Model - Change decimal separator

    Huh? Text to Column isn't same as using Get & Transform.

    Get & Transform can be found in Data tab, use "New Query"->From File -> From CSV

    You can set appropriate File Origin, Delimiter, Data Type Detection setting.

    Before you load the data, go into edit mode and apply appropriate transformation steps.

    Once satisfied with it, from Home tab, Close & Load To... (found in drop down) and pick connection only and also check Load to Data Model.
    Last edited by CK76; 12-06-2017 at 09:50 AM. Reason: Typo

  21. #21
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Hi,

    Just to clarify, I used the Get&Transform->From CSV, then loaded into sheet, and then used "Text to Columns"->Step3->Advanced->Decimal separator = "." ->Finish. to change text to numbers.

    If I follow your steps it seems like a good idea, but I can't seem to change text to numbers in the Edit section. Have you tried this? Just changing Data Type gives an error.

  22. #22
    Registered User
    Join Date
    02-08-2013
    Location
    NO
    MS-Off Ver
    Excel 365
    Posts
    12

    Re: Import CSV to Data Model - Change decimal separator

    Quote Originally Posted by sandy666 View Post
    I give up

    have a nice day
    Thanks for the input tho
    I will do a last ditch attempt by changing my Windows settings to American English (those bastards with their own standards )

  23. #23
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Import CSV to Data Model - Change decimal separator

    Just changing Data Type gives an error.
    Use Replace Value on the column to replace "." with ",". Then you can change type appropriately.
    It may be necessary to delete "Change Type" step created by the system.

  24. #24
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Import CSV to Data Model - Change decimal separator

    btw. if the column is decimal you cannot change dot to comma. first you need change to text then replace then back to decimal (if neccessary)

    but without example of your work it's hard to say what are you really doing

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data extraction problem (decimal separator)
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2017, 04:47 AM
  2. Replies: 4
    Last Post: 09-06-2012, 07:51 AM
  3. Change decimal separator format in a wokbook
    By poseido in forum Excel General
    Replies: 0
    Last Post: 09-01-2011, 10:56 AM
  4. Getting decimal separator
    By Helge in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2011, 08:23 AM
  5. [SOLVED] How to change decimal separator . to ,
    By MG in forum Excel General
    Replies: 2
    Last Post: 03-03-2006, 08:45 AM
  6. Replies: 2
    Last Post: 01-20-2006, 04:00 PM
  7. Replies: 1
    Last Post: 09-08-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1