how do i choose specific sheets in an .xlsx file?
i thought that i had seen that option before (on input). but i’m no longer being prompted to make a selection.
i didn’t find anything in the documentation or forum. but i think what i’m looking for is the image here: Copy Settings from one node to another - #10 by Admin
what i’m trying to do
using an online converter, i converted a pdf to excel file. the 85 page file turned into an .xlsx file with 85 separate sheets.
though all of the columns are not the same (due to the conversion not recognizing some empty columns on some of the pages), i am still trying to combine everything into one table.
i have already tried batch processing and merge/append the file.xlsx[*]. but that created something ugly that didn’t look like fun to fix manually.
so my next approach is to see what the final output would look like if i brought in every sheet manually and stacked them in one node. but i’d rather “select all” sheets instead of doing each one manually.
If you drag an Excel file with multiple sheets on to Easy Data Transform, you will be asked which sheets to import.
If you want to import a specific sheet, you can append [sheetname] to the path, e.g.:
i tried that (i.e. drag to import) already and i was not prompted to select sheets.
i have already successfully selected a few sheets with the syntax that you mentioned. so i know that edt knows that the sheets are there.
but i’m not being asked to choose sheets on import.
here’s something random. i wanted to test if there was a difference between a file created in excel vs. a file made into an .xlsx format by a third party.
so far for every file that was created in excel (i’m pretty sure that’s the case for each of these tests) that had multiples sheets — on import, i was prompted to select sheets. yay!
in google sheets, from scratch, i created a spreadsheet with multiple sheets. on edt import, i was prompted to select sheets. yay!
in google sheets, i opened an .xlsx file with multiple sheets. on edt import, i was prompted to select sheets. yay!
but for the “other” files (like the one that i converted), i was not prompted.
and just before i finished this post, i decided on a whim to import my “bad .xlsx” file into google sheets and then download it as .xlsx file. i then imported that file into edt, and it DID prompt me for sheet selection! yay!
is there anything you can do on your end to address this so that i don’t have to take an extra google sheets step (sometimes) to select sheets?
It sounds like the ‘other’ software is creating Excel files that are not correctly formed. If so, I’m not sure there is much we can do about that.
We use a third party library to read and write Excel files, which is a hideous proprietary format.
Feel free to attach here (or email to us at support) an example of one of these spreadsheets. Ideally a version produced by the ‘other’ software and then a version converted by Google sheets. We can send it on the people who create our Excel library. But they may just say that it isn’t correctly formed, so that’s not their problem.
maybe this will be more helpful. i’ll give you the tool that i used:
they use this:
when i was searching for a pdf to spreadsheet converter, i found a handful in my price range. they all seemed to be using the same sdk. i tested a couple of services with a dummy file and ended up with the exact same output; i.e. none of the other services did anything on top of what solid documents provided.
i think that i ultimately made my choice solely based on price.
if you would rather that i still send you a file, i’ll clean up and shrink the files a bit before sending them forward. i just thought that the sdk might help you move forward a little more quickly.
by the way, though i used the paid version that includes ocr capability, the file that i was inquiring about did not need ocr.
so you can test this without a paid account since conversion without ocr is free.
If you could send us (not too huge) files that would save time.
We would like be able to read PDF directly in to Easy Data Transform, but it would require finding a reliable and affordable library that works on both PC and Mac.
well, the plot thickens.
i started to prepare the file for you. i opened it in the mac preview app and removed all but 3 pages in the pdf. i went to the same pdf converter. i downloaded the file. to test it, i imported it into edt. and to my surprise, it worked.
then i wondered if something changed with the pdf file structure when i saved the file in mac preview such that the conversion gave an edt-compatible .xlsx file. i don’t know how i would know. i don’t have enough pdf applications to test this.
but then i also wondered if edt was now reading the file correctly (i.e. bug in edt?). so i imported my original .xlsx file in question that did not prompt me for sheets. and to my surprise, it did prompt me for sheet selection. that’s odd.
i tried it a few more times, with a couple of times by starting a new transform file, and each time i was prompted for sheet selection.
then i wondered if there was something in the ram that allowed edt to remember there were sheets in this file. so i closed the application, and then started again.
for those two files (1. the original .xlsx file that gave me problems, and 2. the reduced 3 page .xlsx file), i was still prompted for sheet selection.
i can no longer duplicate the problem of not being prompted for sheet selection.
could this be an edt bug?
i’m wondering what to do next time if i’m randomly not prompted for sheet selection — because to me the problem seemed to also randomly go away.
It is possible. But we haven’t had any other reports of this.
Perhaps Easy Data Transform some of the other sheets are empty.
If you can send me a file that has >1 sheet that doesn’t prompt for sheets in Easy Data Transform, I can investigate.
i still can’t reproduce the problem.
i was going to send you the original pdf file so that you could do the conversion and follow the exact same steps that i did.
to be sure that this would help, i did it first. i used the same (as when i first started with this problem) original pdf file. did the conversion. imported it into edt. and it prompted me for sheet selection.
i’m not sure that i can send you a problem file since it seems like the issue may not be the file, but rather the “state” that edt was in at that moment. btw, i’m running v1.46.0 on mac.
if i do run into that problem again, i’ll update this thread and upload the file here. though i’m not sure if that will help, i guess you’ve got to start somewhere.
Yes, please. Or email it to us, if sensitive.