+ Reply to Thread
Results 1 to 2 of 2

How to set column type on data import in Power Pivot?

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    How to set column type on data import in Power Pivot?

    I am trying to import a lot of data in CSV format into Power Pivots Data Model. One of the fields is predominantly numeric, but there are instances where the data is alpha-numeric. When importing the data Power Pivot enforces a data type on the column of numeric, which means it then fails when it finds a row where the field is alpha-numeric.

    How can I force the data type for a column so I do not lose data in these fields?

    (see the same thing happening if I use 'Get Data' in Excel.

    Thanks

    I have made some progress and think that this can be resolved using a schema.ini file, so I have written a file like this;

    [container.csv]
    Format=Delimited(~)
    Col9="Container Id" TEXT


    Column 9 has a header of "Container Id" and is the one I want to import as TEXT. The above does not seem to work (though the delimiter part is working and PowerPivot identifies the Schema file). I think the syntax must be wrong, but I cannot see why or where.
    Last edited by Nerdio; 06-28-2020 at 12:04 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to set column type on data import in Power Pivot?

    Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Replies: 4
    Last Post: 07-01-2020, 02:13 AM
  2. Could not import data properly when using Power query
    By yeungcase in forum Excel General
    Replies: 1
    Last Post: 07-02-2019, 03:57 AM
  3. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  4. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  5. Power Query Import Binary Data Type
    By brent_milne in forum Excel General
    Replies: 0
    Last Post: 08-13-2015, 09:46 AM
  6. [SOLVED] Power Pivot SQL Data Import - How to update import filter
    By minnesotaart in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-29-2013, 12:39 PM
  7. [SOLVED] How to import column from dB and force data to a numeric data type?
    By Mike Hanby in forum Excel General
    Replies: 5
    Last Post: 07-11-2006, 05:13 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