Change from xlsx to csv

[JGG]

I have several xlsx-files in a sharepoint folder, it worked perfectly in PBI but due to performance issues in my source system I need to change to csv-file. My csv-file (with same columns and column name as xlsx) are now stored in the correct sharepoint folder. And as expected I get error message "DateFormatError: The external table doesn't have expected format"

Is this possible to fix without deleting the query and start over (I would like to avoid due to complex model)? I would guess Advanced editing but it doesn't seem to be anything regarding file format there.

regards JG

[esmat]

Hello , I did the same howver i am receiving an error message when I try to "close and apply".

this is the error message :

"OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))."

[v-stephen-msft]

Hi @JGG ,

1. Create a new query that points to your SharePoint file in Power BI Desktop. 
2. Copy the code(Source line) of the new query in Advanced Editor.
3. Go to your existing query of Excel and replace the  source code in the Advanced Editor with copied code in Step2.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @JGG ,

I'm assuming you're comfortable using the Advanced Editor in Power Query here.

I think the quickest/easiest way to do this would be to connect to the .csv folder and perform the Load/Transform steps you want as you did previously for the .xlsx folder. This will create a new transform function group for the .csv folder in Power Query.

Create a duplicate of you original .xlsx query as a safety copy here as we will mess with the code next.

Then take the code generated in the Advanced Editor for your new .csv query and use it to replace the same code lines in your existing .xlsx query without replacing the subsequent transformations you've done after the initial data load/transform. This should work fine as all your fields are the same in the .csv files as they are in the .xlsx files.

Once you are happy that your full query is running as expected, you can delete the original .xlsx function group from Power Query.

Hope this makes sense.

Pete

[JGG]

Thank you, this make sense. However when I compare the advanced editor for xslx and csv they seems to be identical except for the reference to 21 (the number of the new query). See attached. So may be something I misunderstand here.

JG 

[BA_Pete · Accepted solution]

@JGG ,

I believe this is correct as the difference in connection strings would be found within the 'Transform Sample File from [your folder name]' query, as well as a different sample file being used for the 'Sample File' query, each in the new function group (automatically generated folder of queries).

Additionally, Windows tends to actually store .csv files as Excel csv, so may appear to Power BI as an Excel file when importing. You can see this from the icons that your .csv files display when viewing them in File Explorer - they will either show as an Excel icon or a text file icon.

Further to the above, if your .csv files are Excel csv (maybe even if they're not), you may need to select your 'Filtered hidden files' step in your output query and manually filter this table to only include files with [Extension] .csv to prevent all of your .xlsx and .csv files being bundled together during the transform function operation.

Your 'Filtered hidden files' step table should look something like this:

This is where you may need to filter rows to only include specific files in the folder for processing.

Pete