Transform "MS Excel 97-2003" data files stored in a folder path into a read-able table format

[geeklarokcmie]

Hello Experts,

I currently have folder path where MS Excel 97-2003 files are received - macros are run on each excel file to convert them to read-able table format (csv).

I tried transforming in Power Query but it doesn't work and keeps failing. How can I achieve this?

Expected Output with column headers as below and all the rows of data under those column headers - without any page breaks and duplicate column headers

Date 5/1/2023

IT Account	Cg Bal	Ad Bal	Difference
**D TAL**	********	*****	*****
1000252222	********	*****	*****

[Anonymous]

Hi @geeklarokcmie ,

You have provided too little information. What actions are you performing in Power Query? What error did it report? And how was your macro created? How do you want it to work in Power Query?
Also if you want to convert xlsx to csv file via Power Query, you can refer to the solution in this post.
Solved: Change from xlsx to csv - Microsoft Fabric Community

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

[geeklarokcmie]

When I posted the original message and tried attaching the xls file (input) : an error occured stating "The file type (.xls) is not supported"

Viewing the input file would have helped the community to provide solution for the expected output.

Below are the steps I took:

• Get data and select folder - paste a folder path containing multiple xls file(s)
• then transform the data in Power query - by adding a filter on date creation
• then click on content column with down-arrow, which evaluates the query and establishes connection to Excel workbook
•  then I run into the below error :
  Details: "External table is not in the expected format."
• I edit the error and open the file as CSV document instead of excel workbook and the columns are split inconsistently.

I would like the columns to split as it was in the spreadsheet. Below is the adv editor screen-shot: