Large CSV Files

[krixtsup3r]

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.

[harshnathani]

Hi @krixtsup3r ,

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!

[krixtsup3r]

@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.

@harshnathani 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]

@krixtsup3r , 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.

[krixtsup3r]

@amitchandak the real data source is from CSV. I just tested to convert it into XLSX and weirdly enough it didn't have any error.

[v-easonf-msft]

Hi , @krixtsup3r 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@krixtsup3r , I think then it just the format issue. Check which format did excel taken it might not UTF-8 or something you are selecting. Just try to get that and choose that file loading in power bi

There is an option while loading: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-csv

Also, see if this can help

https://www.mrexcel.com/board/threads/power-query-doesnt-properly-import-utf-8-unicode-from-csv.9173...

[amitchandak]

@krixtsup3r , 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