CSV data file importing with odd blank lines

[emma313823]

Hi All

I have an odd situation which I can't seem to figure out.  I'm importing data which has over 1 million lines.  I tried Excel as the import option and the software seems to choke, so I saved the file as a .csv and it seemed to import fine.  In the first snapshot...you see that my filters seem to show these blank options.  When I open my .csv file and I filter on SalesRep for example...it shows blanks, however in my original data in excel there are no blanks at all.  When I scroll to column A...I simply do a ctrl arrow down to get to any blank cell if they exist...The first one I come to is the same one for the blank on the SalesRep.  If you look at that screenshot....you see row 485968 looks normal.  It looks to me as though Row 485969 and 485970 split....leaving one row with just the customer name and the other row with all of its associated data.

So - I've fixed this twice only to save it back as a .csv and refresh the desktop appliation to have the blanks still continue to show.  When I reopen the .csv, I fine that it is happening again but to differnet rows.

Does anyone have any idea what is happening?  I'm at a loss as to why and as to how to fix this.

Emma

[BhaveshPatel]

Hi Emma,

Try and Test this and see if it works.

Go to Query Editor and Under the Home Tab, there is a option " REMOVE ROWS" , Select "REMOVE BLANK ROWS" . apply the changes and see if the problem persists. There are so many other ways as well but give it a shot this one first.

[emma313823]

I'm not sure this is the correct solution as the rows that are showing issues are legitimate - they are just being split somehow into two rows - 1 with just the customer name and all else is blank and the other with no customer name and all other cells populated. 

1. I went back to my original excel source data and looked at every column and there are no blanks

2. I saved the excel as a .csv and after reviewing it seems that whatever is happening is doing so during the save into the .csv format.

3. I went back to the source file and saved as .txt and reveiwed the results and all looked good, then I save that into a .csv and the rows whacked out again.

4. This time however, from .txt to .csv I fixed the offending rows, saved and refreshed Power BI Desktop and the blanks in my filters went away.

Not sure this is the fix - as something is definitelty wrong with saving to .csv, but excel to txt, then save to csv at least allows me to fix the rows, since they are legitimate, then I can refresh and it's clean.

Emma