1

I'm using a batch file to convert XLSB files to CSV - from here: https://stackoverflow.com/a/11252731/9403175

It's great, but I would like to include also subfolders. I have 0 experience writing batch files, so I just copied it (and included one extra argument for sheet name).

The code I'm using:

FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%~ni.csv"

I Googled that you're supposed to use FOR /R instead of FOR /F, but it doesn't work.

I tried to modify it a bit and also came up with this:

FOR /f "delims=" %%i IN ('DIR *.xls* /b /s') DO ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%i.csv"

This version on the other hand loops also through subfolders, but incorrectly saves the file as *.xlsb.csv, instead of just *.csv (as I guess without the ~n in the last argument it takes the whole file path)

Can someone please help me? I think this should be fairly simple for someone more experienced

Thank you!

Gerhard
  • 22,678
  • 7
  • 27
  • 43
RadekC
  • 13
  • 6
  • See `dir /?`. You are using `For` to go through the output of `dir`, so `for /f` is right. – CatCat Jul 30 '18 at 10:48
  • CatCat: it doesn't work on subfolders though, it only loops through the files in the top level folder – RadekC Jul 30 '18 at 10:52
  • @CatCat: so thanks to dir /? suggested by your post, I figured out I can do: FOR /f "delims=" %%i IN ('DIR *.xls* /b /s') DO ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%i.csv" which now loops even through subfolders but it now has .xlsb.csv as the file type:P so still something I could improve, just don't know how... – RadekC Jul 30 '18 at 10:57
  • @RadekC, do not post code in the comments section. If you revisit your question and [edit](https://stackoverflow.com/posts/20444569/edit) it to include the code you are now using with an explanation of what happens and what you intend to happen, you will have a clear question. Please note that when posting code, you should highlight it and format it properly using the **`{}`** button. – Compo Jul 30 '18 at 11:08
  • 1
    Read the end of the help on `For`. – CatCat Jul 30 '18 at 11:13
  • 1
    Why would you chose to use the FOR variable modifiers in your first set of code but not your second? – Squashman Jul 30 '18 at 11:21
  • `Dir *.xls`, `Dir *.xls*`, `For /R %A In (*.xls)` and `For /R %A In (*.xls*)` are unituitive and will output `.xls`, `.xlsx`, `xlsm` and `xlsb` files too! If you're wanting to convert `.xlsb` files, _especially those throughout a tree structure_, I'd sugest you use a more specific method of ensuring that your conversion script is working only with the `.xlsb` files you intended it to. – Compo Jul 30 '18 at 11:40
  • Thank you, well I replaced `*.xls*` with `*.xlsb`, but it still doesn't solve the real issue at hand – RadekC Jul 30 '18 at 12:20
  • What happens if you write to the file as `"%%~ni.csv"`? – lit Jul 30 '18 at 14:02
  • @lit - it doesn't write the CSV files into the subfolders with the original XLSB files, but tries to write all of them to the top folder (which includes the BAT file) – RadekC Jul 31 '18 at 07:14

1 Answers1

0

Seems you are overcomplicating the query a bit. You do not need to do the dir /S function, as for /R does it recursively for you.

for /R %%i in (*.xlsb) do ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%~dpni.csv"

When you run cmd.exe and type for /? you scroll to variable references and you will see:

%~nI        - expands %I to a file name only

This will strip the extention from the name, so in my example, I simply do %%~ni.csv

Gerhard
  • 22,678
  • 7
  • 27
  • 43
  • Thank you - this is something similar to what I got when I was experimenting with it, but the problem with this code is that it tries to write all the files into the top folder (so it always asks if you want to overwrite) - however, I would need the files to stay in the same subfolder as the original XLSB file. Which is what I was only able to do when I removed ~n (then it shows the full path). I already ran the script overnight, so I have all the files as .XLSB.CSV - I think I will just make another BAT to rename them to .CSV – RadekC Jul 31 '18 at 07:11
  • @RadekC This will not use the original file extension, Guaranteed!! I changed it to use `%%~dpni` instead, meaning Drive, Path and name, which will place it in the same directory it was found. Try it, you'll see it will work. – Gerhard Jul 31 '18 at 07:54