Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Large CSV Files

I have a large csv file with around 80mb each. I tried to use the Folder as the data source and as soon as it combines, some of the data are note correct, such as:

  1. Some of the columns are misaligned, even though I have already ensured that all of the column names of file are the same
  2. Some of the datetime column are not getting parsed and throwing out parsing error

I already tried in changing the File Origin in PBI to UTF-8 and Western (which is the default) but it keeps getting me error. Is there any limitations we have in CSV? 

By the way, I already tried to convert the CSV files into XLSX and it works perfectly fine so I'm wondering if there is a limitations.

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , As it CSV, difficult to tell, But if there is a field which contains comma then it should be inclosed in double quotes

     

    Like Name Abc, Def

     

    Should come like "Abc, Def"

     

    If it comes like the first one you will have a problem. Or have number has been exported with the format in csv

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    1. You can use Trim Function to trim the Columns which are misaligned.

    2. You will need to Use Locale settting the Date to the correct Format.

     

     

     

     

    Select Locale.

     

     

     

    For Trimming and Cleaning

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak , it's always just the comma. I'm wondering if it's the file itself the problem, CSVs should be always smaller right? but when I convert it in xlsx the file size gets smaller.

       

      Anonymous the problem in the misalignment is just certain count of rows not just one file is getting misaligned. Which I really find weird. I also tried using the Date Locale but it still doesn't work, what worked is that I converted the column to Text first then added a column to convert text to DateTime. I think the problem stems out really in the file itself which I don't know what it is.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , I doubt file size can be an issue. CSV in uncompressed and excel is. So if you convert this CSV to excel and do not give an issue then it is a problem to solve. else it is a data issue.