What file type do you use to export large data set from Alteryx to PowerBI?

I am currently using Excel but Excel does not like huge datasets (over 250K rows) and has error message. (The error message does not seem to cause the data to be inaccurate. It is just annoying.)

I also tried csv and json but both file types have numerous error messages in Alteryx, which seems to be because the dataset I have include a lot of string characters like comma. Therefore, Excel is better than those two.

Is there another file type you'd recommend?

I don't plan to use the PowerBI Rest API as the documentation on it said it has a lot of limitations like maximum rows and performance time.

Thank you.

Output

Power BI

Accepted answers

Felipe_Ribeir0

Hi @vball

1) It is expected, XLSX is a actually a compressed (zipped) file. - excel - *.csv Increase the file size - Stack Overflow

2)Take a look at your output and try to input it on pbi. If you have any error, you can try to use the data cleansing tool. Here some explanation about why Solved: Warning message: Newlines detected - Alteryx Community

3)If you set the encoding of your output to UTF-8 and configure the input of your pbi to UTF-8, i would not expect problems.

All comments

gautiergodard

Hello @vball

I would recommend trying to output your results to SQLite file.

**Please like this post and mark as resolved if it answered your question!**

Robin_McIntosh

@vball - What is the error message you're receiving with Excel? Is it an error within Alteryx or within Excel/Power BI? Can you not export to just an Excel file and then bring that into Power BI?

Felipe_Ribeir0

Hi @vball

This error message is not related to the output file choices (250k rows is a little heavy for excel, but it can handle it), can you share a little more about what errors are you getting?

Anyway, here is the list of better options for this:

1)Database (If you have and can use it, it is the best option for multiple reasons).

2)Power BI Output tool (If you have power BI Service, you can update the dataset there with this tool: Power BI Output - Alteryx Community).

3)For files, .csv is the best option. It can handle any amount of rows and it is the fastest file option to load from PBI (take a look at this test: Best file type for Power BI | 5X FASTER REFRESH! - YouTube)

If the provided answer helped you to solve the problem/was correct, please accept it as a solution

Thanks.

vball

Thank you for the advice.

I don't have a database that I can use, and the data needs to be used by co-workers for manual testing so database will be tough.

Does PowerBI Output Tool have restrictions on rows? I saw the restriction warning on both Alteryx and PowerBI Push Dataset webpages.

I agree with .csv. The only problem for my particular file is that it has many fields with "," in it and therefore .csv cannot be used properly. I cannot even change the delimiter because many fields also have other symbols like ".". But if .csv can still work by some specific setup, can you advice? I can give that a try.

vball

That is exactly what I am trying to do: export to Excel and bring to PowerBI. I suppose there is no error message if I directly load the file from excel to PowerBI as long as I don't open the excel file. But knowing that when opening the excel file using excel has this error message just makes me nervous.

The error message shows up in Excel and says the file needs to be repaired. When it is repaired, it says Removed Records: Formula from /xl/worksheets/sheet1.xml part.

At first, I thought this is due to formula created in Alteryx. But I tried to import an excel file to Alteryx and export it right away back to excel. The same error message pops up. The weird thing is that the original excel file has no error message when opening up in Excel. It seems that as soon as it gets "showered" in Alteryx, an error will pop up. This tells me that it might not be an issue of Excel unable to handling the size of the data set.

In addition, if I export a smaller file to excel from Alteryx, there is no error message when opening up in Excel. If I created formula in Alteryx > export a file to excel and the file is small, there is no error message when opening up in Excel.

The whole thing does not make sense to me at all.

vball

Thank you but we don't have SQLite database. Do you know if that file can be directly imported to PowerBI?

Felipe_Ribeir0

Hi @vball

You can use the .csv output with ; as delimiter, so you would not have problems with ,. it is a good choice.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution

Thanks.

Felipe_Ribeir0

Hi @vball

It worked?

vball

Hi @Felipe_Ribeir0

I was able to try .csv and for whatever reason, this time there is no significant error even if I export without changing the delimiter. That is really good news. However, I do have two follow-up questions (sorry for so many questions)

1) The .csv file double the size of .xlsx file with same data. Is that expected?

2) The error when exporting using .csv is "Newlines detected in data, output file might not be readable by other tools". It's just a warning, not actually an error. I have not tried to input the data to PowerBI to see if it is indeed not readable. But do you know why this error shows up?

Another thing I want to mention is that my data has non-English characters and Alteryx community posts on handling non-English characters said I should not use .csv to export the data as .csv does not handle non-English characters well. So I might not be able to use .csv after all. Nevertheless, thank you for providing a possibility. (Sorry I cannot accept your answer as a solution now as that will mark the post as solved. I will for sure accept your answer as solution after getting all my questions answered. Thank you!)

Felipe_Ribeir0

Hi @vball

1) It is expected, XLSX is a actually a compressed (zipped) file. - excel - *.csv Increase the file size - Stack Overflow

3)If you set the encoding of your output to UTF-8 and configure the input of your pbi to UTF-8, i would not expect problems.

vball

@gautiergodard Thank you but that would not work for us as other users will need to consume the data in Microsoft Office in addition to the need to upload to Power BI. Sorry, I should have made my request clearer.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703