Forum Discussion

emma313823's avatar
emma313823
Icon for Helper V rankHelper V
9 years ago

CSV data file importing with odd blank lines

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 save...
Need Help
BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

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's avatar
    emma313823
    Icon for Helper V rankHelper V
    9 years ago

    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