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.
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.
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.
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:
{4}
or
{4.fname}_{4.sheet}
Rather than:
{4.fname}
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.
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.
