Powerpivot data import with trailing minus

**igoodable** · 11-25-2014, 01:40 PM

My text data file have trailing minus (for example 8- instead of -8), when I import them into PowerPivot via OLED/ODBC connection, they become blank. Does anyone knows how to this without VBA?

This is my connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\networkname\interface\RPT\;Persist Security Info=false;Extended Properties="Text;HDR=Yes;FMT=TabDelimited";Jet OLEDB:Registry Path=Software\Microsoft\Office\15.0\PowerPivot\ACE\TabDelimited

Attached is an example of the file.

Thanks in advance!

**igoodable** · 11-25-2014, 05:16 PM

Is my problem difficult or my question is not clear? Lots of view, but no response. =(

**Debraj Roy** · 11-25-2014, 05:21 PM

Hi igoodable!

In case of PowerPivot.. I dont hink, any option to change anything in the source.. However.. you can add a Calculated Column, (Simply Excel Formula will work.. No DAX required) and you can hide the source Column.

Something like..

Formula:

=MID(table.Inv_qty,1,LEN(table.Inv_qty)-1)

Right now no Power Pivot available.. if required let us know.

**igoodable** · 11-25-2014, 06:29 PM

Thanks for the reply Debraj, it has to be Powerpivot because I'm working with many multiple data sources. On top of that, the tables are huge and they are also many to many relations. I'm trying my best to avoid VBA, but it looks like i'm running out of options.

**Debraj Roy** · 11-25-2014, 08:34 PM

I just tried .. and its working fine .. did you tried by changing Column Separator as "TAB" instead of Comma..

https://dl.dropboxusercontent.com/u/...2006-00-32.jpg

**igoodable** · 12-01-2014, 03:37 PM

Originally Posted by Debraj Roy

I just tried .. and its working fine .. did you tried by changing Column Separator as "TAB" instead of Comma..

https://dl.dropboxusercontent.com/u/...2006-00-32.jpg

I do have it as tab, I think the reason it works for you because the sample data doesn't include regular numbers. If there are both regular numbers and trailing minus number, the system import only the numeric value and leave the trailing minus as blank. I tried IMEX=1, but it doesn't help either.