.xlsx/.xls > .csv Batch Transform Basic Query

Hi! I’m new to EDT and sure I’m missing something blindingly obvious here, but I just can’t work out what I’m doing wrong and would appreciate some help.

I’m trying to convert ~70 .xlsx and .xls files to individual .csv files so that they can be merged later. I’m working on Mac (Venture 13.5.2) and processing the two file types separately (even though they’re in the same folder) to keep things simple. I set up the transform using the first file in the folder, and open up Batch Process to try the following parameters:

Input alias=dummy
[long filepath]/4.1 END PreExist/All BIW merge test 01/.xls[]

Output alias=dummy9 (Overwrite)
[long filepath]/4.1 END PreExist/Merge Test EDT 01/{dummy}_output2.csv

The Process does run through all of the .xls files successfully, but it always creates just one .csv file and overwrites it multiple times. If I swap to ‘New’ as the write mode it doesn’t create new files because it ‘already exists’. I’ve tried adjusting the {} variable to include .fname and similar, but that doesn’t seem to make any difference.

I can successfully use the ‘Append’ write mode to just export all the .xlsx/.xls files to one big .csv, which will probably be an adequate solution for now, but I’d like to know what I’m missing here (apart from another cup of coffee…) for future reference.

Thanks in advance!

Great software btw—this is going to be super useful for the sort of ridiculous archival data wrangling I do.

Hi @PatchworkFez

You should use {dummy.fname}_output2.csv as the output file name. What is the name of the one file it does produce?

Can you please add a screenshot of what you see in the Batch Processing window after you click Process. Also copy and paste into the forum the output at the bottom of the Batch Process window.

If the forum won’t let you upload the screenshot (due to being a new forum user) please email it to support.

Hey! Thanks for the quick response.

Here is the screenshot of the batch processing screen after running. I accidentally closed the software after my last post, so set up new unique names to avoid confusion, but get the same result.

The filename produced is 4_quibble.csv
The message at the end of the process is:
> File ‘/Volumes/Mnemonic/BIG DATA processing/2 - NEW DATA/4.1 END PreExist/Merge Test EDT 01/4_quibble.csv’ overwritten 27 times.

Please let me know if you need any more information (and sorry if I missed something).

I think Easy Data Transform is getting confused by the dot (‘.’) characters in the file paths. Normally you would only expect a single dot before the file extension. If you replace the dots with underscores in the folder and file names, does that fix it?

Also, if there is possibly more than 1 sheet per file then use:




Rather than:


Otherwise multiple sheets will overwrite the same .csv file.

Yes, you’ve solved it. Replacing the dot characters with underscores has fixed the issue. I can batch rename the Excel files to avoid this problem going forward.

Thank you for your help!

Glad that fixed it. I will look into whether we can accomodate ‘.’ in file paths. Otherwise perhaps we can add a warning in the output.

I’ve fixed it so it will work with file names that contain more than 1 ‘.’ character. The fix will be in the next release.

Probably best not to use more than one ‘.’ in file names though. ;0)

A new release which handles multiple ‘.’ characters in file names is now available:

Many thanks for this!

Yeah, I don’t habitually put ‘.’ in the body of filenames but I’m unfortunately wrangling hundreds of sheets that have been downloaded from a system that does…

I’ve been deploying A Better Finder Rename to tidy things up in bulk but this update is really helpful nonetheless. :slight_smile:

1 Like