Problem with type off data import TXT files in PowerPivot

Question

Hi,
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.

Answer 1

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.

HTH

Thomas Ivarsson

Answer 2

Adding IMEX=1 to the extended properties of your connection string may resolve this. http://support.microsoft.com/kb/194124/EN-US

Answer 3

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?

BR

Thomas Ivarsson

Answer 4

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.

Regards,

---

Elvis Long
TechNet Community Support

Answer 5

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 :

Field_Number_1;Field_Number_2;Field_Number_3;Field_Number_4;Field_Number_5;Field_Number_6;Field_Number_7;Field_Number_8;Field_Number_9

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.