Power Pivot Importing Blank Fields that Should Contain Data

[mkeisha]

I'm creating my first Power Pivot model and am having an issue importing a Customer table. The table has about 215,000 rows with the unique identifier as the CustomerID. I start with an Excel file that I then save as .csv file. Then I import the .csv file into the Power Pivot model. However, even though exactly all 215,000 rows import in, some fields that should be populated come in as blanks. (including the CustomerID field.) Not on every record, just on some. I can't seem to find a pattern for the ones that come in correctly/incorrectly. And there's no warning--it states the import was successful. Out of the 215,000 records, about 40,000 come in with the CustomerID field blank. Same thing happens with a couple other fields too even though I made sure the fields were formatted as text.

Does anyone know why this is happening and how I can prevent data from importing as blanks when the fields should be fully populated? (I chose to import via a .csv file because it is so large.) I'm worried because as I progress in my learning of Power Pivot, I want to import directly from the data sources and automatically refresh. If Power Pivot is going to randomly blank out some of the data, then I'm going to be in big trouble!
Thank you!!

 

[scottsen]

Wow, that sounds truly ungood. Can you share the csv? (email me, or dropbox/onedrive/googledrive?)

 

[PentaGalCXO]

Your intuition to import your data as CSV is good I think. I believe in it so much I blogged about it here. You didn't mention a schema.ini file so I assume you are importing via PowerPivot's text import wizard. I would stay away from the wizard except for short term ad-hoc type work. Per my blog, create a schema.ini, setup the connection as described and I bet your issues go away. If not, it is probably due to dirty data - in which case inspect your source data with a good text editor (like notepad++) and typically crafty SQL can overcome the issues.

 

[mkeisha]

PentaGalCXO,
Thank for the info. I built the schema.ini file per your blog instructions. But when I tried to create the connection, I got stuck at the last step, #13. Your instructions are as follows: From the Specify a SQL Query panel, define a useful name and the following as a SQL Statement and click the Finish button: select * from c:\data\report1\report1.txt.

Since my pathname is different, my statement is: select * from \\fileserver\TSUsers\mprun\Desktop\Ortho PowerPivot Nov 2014\report1\report1.txt.
But I get an error message: ERROR [42000] [Microsoft][ODBC Text Driver] Syntax error in FROM clause.

I'm so close! Any thoughts on what to do now?

 

[PentaGalCXO]

I suspect the issue is with the space(s) in your path. You can get around this by delimiting with this character "`" (the tilde above the tab key). But it is tedious to figure out where to place it. What I find easier is to be creative with your path. First I would copy the file to a local C:\temp\ and test everything else. Then I would play with path options such as mapping a drive letter or using MKLINK as i descrive in my other blog article.

 

[scottsen]

I was able to bring in your CSV to both 2010 and 2013... and didn't end up with any blank accountid fields... in either. However, I did end up with issues on custgroup. I think that is because power pivot looked at the first few rows, saw just #'s... 1000, 8000... and cleverly (stupidly) thought it was a column of int's... and freaked out when it go non-numeric. Kind of a ridiculous bug.

In your scenario, can you use Power Query? (which basically means... you aren't doing on-prem sharepoint w/ scheduled refreshes)

It's certainly better about csv imports...

 

[mkeisha]

Thank you for your help Scott. I am aware the Power Query exists but I've never used it. So once I open the csv file in Power Query, before I load to the data model in Power Pivot, is there something I should actually do to the csv file within Power Query?

 

[scottsen]

Honestly... probably not. But it is worth your time to make sure all the columns have the types you want/expect and names you want... the interaction can between the two tools can get wonky if you change stuff in power pivot... after doing it in power query, and the best practice is to do what you can in power query.

Power query is simply "better" at correnctly importing csv files than power pivot.

 

[mkeisha]

I'll try that! Once again, thanks so much.