-2

I am trying to retrieve the data from user groups and exporting it into an excel. So i am able to get the users etc into a text file, but converting them to excel has to be manual(open text file in excel, then do the text import wizard and selecting comma as the deliminator). Is there a way to automate the process whereby using a batch file, i can export the data into an excel with the deliminator already specified? This is my code for retrieving the user groups(quite an easy code)

start wmic /output:C:\Users\John\Desktop\automation\useraccounts.txt useraccount list full /format:csv
END

If i tried to export it to useraccounts.csv, all the data will be in the first column only. single column

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ruiru
  • 117
  • 1
  • 2
  • 7
  • *Is there a way to automate the process whereby using a batch file* - Yes! There is :). If I post this as an answer to the only question you asked will you accept and upvote??? .... I know, right.... Please read [ask] to understand why we really can't help with this question on SO. – Scott Holtzman Jan 03 '19 at 02:23
  • @ScottHoltzman yes I'll accept and upvote :) I've tried using `Workbooks.OpenText` but its giving me an "expected statement" error :( This is the code I'm trying to run `Workbooks.OpenText FileName:="useraccounts.txt", Format:=6, Delimiter:=","` – Ruiru Jan 03 '19 at 02:26
  • 1
    I am confused by your question. Excel opens CSV files and uses the comma by default without having to do anything. – Squashman Jan 03 '19 at 03:26
  • Have you tried renaming the file with a CSV extension to induce it to apply the automation Squashman just pointed to? – Variatus Jan 03 '19 at 03:30
  • So are you saying that the raw text file does not have any `,`'s in it? And you're asking if there was a way to convert all your `.txt` files to `.csv` but also putting the `,`'s in? – John Kens Jan 03 '19 at 05:07
  • @Squashman: actually Excel uses `,` or `;` - depending on localization (installed language) - see my answer. – Stephan Jan 07 '19 at 14:22
  • @Stephan, that is good to know about the localization. I have also played with using `sepchar=;` as the first line of the file and that has worked well. – Squashman Jan 07 '19 at 15:34
  • @Squashman `sepchar` doesn't work (for MY Excel at least). Afaik, `SEP=` is the only (known) working thing. The good point: it works with (nearly?) every character - be it a tabulator or a `|` or whatever delimiter anyone chooses to use. As long as it's (and nothing else) on the first line. – Stephan Jan 07 '19 at 15:51
  • @Stephan, you are correct. Just mixed it up on my end with the variables I use for my automated scripts. – Squashman Jan 07 '19 at 16:48

1 Answers1

1

There is an undocumented trick to tell excel, which character to use as delimiter. You can use that:

echo SEP=,>"C:\Users\John\Desktop\automation\useraccounts.csv"
wmic /append:"C:\Users\John\Desktop\automation\useraccounts.csv" useraccount list full /format:csv

When you open it, Excel will know (from the first line SEP=,) to separate the columns by ,, independent of the default language settings (Some localizations use , as default delimiter, others use ;). WMIC uses , as column delimiter, but apparently your Excel expects ; instead.

Note: the line SEP=, is part of the file, but will NOT be part of the spreadsheet. So it is lost, when you save the file from Excel.

Stephan
  • 53,940
  • 10
  • 58
  • 91