Problem with type off data import TXT files in PowerPivot

    General discussion

  • Hi,
    I need to import "TXT" file in power pivot. In one column there are numerical and alphabetical data. My problem is why power pivot decided that this column is numerical so I lose all alphabetic data.
    How to pay this problem?
    Thank you in advance for your help.

    Saturday, December 29, 2012 5:53 PM

All replies

  • Hi,

    You should not mix numerical and alphabetical data in the same column. If you import data into an Excel spreadsheet, outside of PowerPivot, Excel will make a choice and interpret it as text mostly. The solution is to think about  if your column is really text or  not. Is it something that can be used in a calculation or some kind of code.


    Thomas Ivarsson

    Sunday, December 30, 2012 5:52 PM
  • Adding IMEX=1 to the extended properties of your connection string may resolve this.
    • Edited by Strafe Sunday, December 30, 2012 7:39 PM
    Sunday, December 30, 2012 7:38 PM
  • When we have these issues in data warehouse scenarios we import all data as text and then do the data transformation data types in a second step. It is hard to do that directly in PowerPivot from a text file. Still it is more or less a question of how you use this mixed column data types. Is it something that you run calculations on, a key that identfies records like invoice numbers or is it a classification?


    Thomas Ivarsson

    Sunday, December 30, 2012 8:56 PM
  • Hi JPH78250,

    I tried to reproduce this issue on my test environment, but everything is OK. Could you please share your text file to us? So, I will reproduce this issue again.

    In addition, we can directly copy the data into Excel and import the Excel file instead import a text file.


    Elvis Long
    TechNet Community Support

    Wednesday, January 02, 2013 3:20 AM
  • Hi Elvis,

    Thank you for taking the time to consider my problem.

    I do not know how to link a file to my post, so I copied a few lines below as an example.

    My source file is in TXT or CSV (the result is the same) with separator ";"

    The anomaly is visible in the columns 6 and # 9 - lines 1, 3, 6 to 8.


    Source File data :


    PR;20120220;9/1000009;1560269;ROWW300Y;A200026;ROI 26 / JUIN 2010;1;19

    ED;20121211;4/1000016;2147379;EDZM30A2;111562;CONSTRUCT COMMENT CA MARCHE;1;70

    PR;20120712;1/1000112;2033534;VNZNJ00T;J00192;JDC 192 / JUIN 2012;1;T2

    ED;20121012;4/1000115;2071456;EDWN30JF;112885;TRAITE EVALUATION BIENS 12ED;1;70

    PR;20121002;4/1000115;2065979;EDWN30JC;200048;ROI 48 / SEPT 2012;1;T2

    PR;20120704;5/1000252;2030907;PAZDP00Z;PK10PA;VESTE PARKA XL;1;19

    BA;20120227;0/1000278;1561309;BAZJB077;BACO12;BATIPRIX COFFRET 2012;1;19

    PR;20121126;0/1000315;2124526;VNZNR00C;D00261;CMR 261 / OCT 2012;1;T2

    ED;20120123;6/1000384;1557190;EDWW300X;112788;DROIT DE L'URBANISME 1RE EDITION;1;55

    ED;20120925;1/1000391;2062361;EDZM309T;112889;PERMIS CONSTRUIRE DIVISION;1;70


    Thank you all.

    Saturday, January 05, 2013 10:15 AM
  • JPH,

    Is this still an issue?


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 24, 2013 3:24 AM