Saturday, December 29, 2012 5:53 PM
I need to import a "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.
Sunday, December 30, 2012 5:52 PM
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.
Sunday, December 30, 2012 7:38 PMAdding IMEX=1 to the extended properties of your connection string may resolve this. http://support.microsoft.com/kb/194124/EN-US
- Edited by Strafe Sunday, December 30, 2012 7:39 PM
Sunday, December 30, 2012 8:56 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?
Wednesday, January 02, 2013 3:20 AMModerator
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.
TechNet Community Support
Saturday, January 05, 2013 10:15 AM
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.