PowerShell CSV to V2 XML converter

[Paul]

This version works with chr(011) "VT" as the line break, as well as "\n".
Can you give it a whirl?

cheers, Paul

[Gaspard]

Yes, it works now for my data exported from SplashID, thank-you!

Btw, in my previous version I said that I have added the IconID and indeed icons are properly set in Keepass. But I had not prevented the creation of a custom field "IconID". I modified your version that works with chr(011) to no longer create the custom field "IconID". Attached v 2.1.

ps.
- I also made a modification that I never mentioned: I commented the instructions that create the group "Imported data", because I don't need it.
- for memory, all modifications I made are prefixed with "##GG"

Cheers,
Gaspard

[Paul]

Added the expiry time field logic.
You can use YYYYMMDD, YYYYMMDDHHMM or YYMMDDHHMMSS, using the header ExpiryTime.

cheers, Paul

[Gaspard]

Super, thank you Paul,
seems to work perfectly!
Cheers,
Gaspard

[Paul]

Latest update at the top of this thread.
Changes:
Convert 0x0B in text to CR/LF
Added IconID field. Uses KeePass icon ID numbers.
Added ExpiryTime field. Past or future dates accepted in the form YYYYMMDD.
Updated readme file

cheers, Paul

[Jay M]

Hi,
mSecure just came out with a new version (mSecure 6) and have changed their CSV export. It now includes the field name and type in the actual field itself except for a few predefined fields. This makes CSV import into KeePass or any other password manager challenging without significant processing of the export. However, this utility can handle this new format much more easily since XML is more flexible. Anyway, I made a few mods to the existing PS script and it seems to work well and the xml successfully imports into KeePass. I'm not sure of the protocol, but I'd be happy to post the updated version for review or send via whatever method.

Jay

[Paul]

Post the PS1 / changes here.

cheers, Paul

[Jay M]

Hi Paul-

See what you think. Changes commented with #JM and start on line 173. I also included an additional note in the txt file that describes the issue. mSecure hasn't fully documented their CSV changes but have described it in their forum and, of course , I've seen it myself on my own exports which is what started this whole thing.

Best regards.
Jay

[Paul]

Try this new version.
I've pulled the formatting out into a sub-section to make it easier to add more later.
Incorporated your VT conversion in the standard \n routine.

cheers, Paul

[Jay M]

Hi Paul-

Seems to work fine although the notification window regarding the msecure 6 format might confuse some users into thinking the conversion didn't occur. Of course, most people using the utility will probably be more on the techy side...

[Paul]

Latest update at the top of this thread.
Changes:
Convert Msecure V6 CSV files.
Details
Column names may no longer match the entry fields, instead the cells contain both the field name and value, e.g.
Title,Group,Tag,Notes,Custom1,Custom2,Custom3
Netflix,Login,Shared,this is netflix line1\nthis is netflix line2,Username|7|NetflixUser,Password|8|pass123,Website|2|www.netflix.com

cheers, Paul

[ShupDogg]

Thank you for this!! I am trying to utilize this in efforts to import my export from AD with just my LAPS passwords for PCs I have my export but the headers are Name and ms-Mcs-AdmPwd which is the PC name and LAPS password from AD. which in this case is all I am worried about. I would like it to import to Title and Password. Then I can run once a day my export, the conversion to XML and then the import to the DB. how can I change the headers to match?
Also is there a way to tell the script the file to import and export without the browse dialog box? what is the syntax to automate it?
And it would be awesome if I could get my export PS Script to export only the accounts that have a LAPS password set. I know I just need to find the right syntax.
Thank you again for creating this! I feel like I am one step closer!

[Paul]

The required headers are listed in the first post in this thread.
Why don't you post a sample of your CSV and we will advise. A few lines is enough.

The script was not designed for regular use so the file names are not passable as parameters.
I take it from your post that you are not much of a Powershell coder (neither am I). I'm not sure we can modify the scripts for you, but we may be able to work something out.

cheers, Paul

[ShupDogg]

Paul, Thank you for the reply. Ya I am not much a a PowerShell coder I definitely do a lot of copy/paste to try to get things working
The Export csv looks like this... Some of the PCs don't have a password which if I can get it working with all of them that would be perfect! If I can get the blanks filtered out that would just be a bonus.
"Name","ms-Mcs-AdmPwd"
"Computer1","Password1"
"Computer2",
"Computer3","Password3"

If I manually change the headers in my csv I can use your CSV2XML and it imports perfectly with the KPScript import. I attached the PS I found to export the csv from AD.

[Paul]

You do not need to use the converter to import such a simple file. KeePass will do it with the native CSV import and it works via KPScript.

To change the headers, add these lines in place of your final export. They find and replace the table header values, assuming they are the first line of the table (-Index 0).

$a = $Computers | Select-Object -Index 0
$b = $a -Replace "Name", "Title"
$b = $b -Replace "ms-Mcs-AdmPwd", "Password"
$Computers = $Computers -Replace $a, $b
$Computers | select Title, Password | Export-Csv -path C:\Temp\"LAPS-Passwords.csv" -NoTypeInformation

To get only computers with a LAPS password set use the filter in your Get-AD...

cheers, Paul

[ShupDogg]

Thank you so much for the reply! I have tried the KPScript with generic CSV and I have not gotten it to work as other forums say that it won't import them. So if you have another forum or know the right syntax for this, that would be awesome! I tried your code from above and all I get is the correct headers and blank entries for the PCs. So I probably screwed something up!

[Paul]

The -Replace operator uses Regex so it may be doing odd things with your data. Use .replace instead.
I've concatenated the replacements into one line as well.

$a = $Computers | Select-Object -Index 0
$b = $a.Replace("Name", "Title").Replace("ms-Mcs-AdmPwd", "Password")
$Computers = $Computers.Replace($a, $b)
$Computers | select Title, Password | Export-Csv -path C:\Temp\"LAPS-Passwords.csv" -NoTypeInformation

cheers, Paul

[ShupDogg]

Thank you again for continuing to look at this... This is my whole script...

$Computers = Get-ADComputer -Filter * -Properties ms-Mcs-AdmPwd, ms-Mcs-AdmPwdExpirationTime
$a = $Computers | Select-Object -Index 0
$b = $a.Replace("Name", "Title").Replace("ms-Mcs-AdmPwd", "Password")
$Computers = $Computers.Replace($a, $b)
$Computers | select Title, Password | Export-Csv -path C:\Temp\"LAPS-Passwords.csv" -NoTypeInformation

And this is what I get now.
"Title","Password"
,"Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"
,"Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"
,"Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"

So is there a way to import generic CSV to KeePass with KPScript without converting?

[Paul]

You need to post a sample of your data so we can see why it's misbehaving.

cheers, Paul

[ShupDogg]

OK I finally got my export with the headers, it may be an around about way but I got it.
Here is my code.

$Computers = Get-ADComputer -Filter * -SearchBase "DC=wmcdom01,DC=wyomingcat,DC=com" | Get-AdmPwdPassword -ComputerName {$_.Name} | where { $_.Password -ne $null }
$Computers | select ComputerName, Password | Export-Csv -path C:\Temp\"LAPS-Passwords_OG.csv" -NoTypeInformation
Get-Content C:\Temp\"LAPS-Passwords_OG.csv" | ForEach-Object { $_ -replace "ComputerName", 'Title' } | Set-Content C:\Temp\"LAPS-Passwords.csv"

This is my output now...
"Title","Password"
"Computer1","Password1"
"Computer2","Password2"

Now to get this to import into KeePass! Is there a way to just import this CSV or do I have to convert to XML with your awesome Script? If so can I tell the script to use the same files every time it runs without having to pick them from a pop-up window?

[Adrian Jansen]

Newbie to KeePass.
I got your excellent script to work for importing data from a Bfolders database, after exporting as .csv and then converting to xml.
I had to split the exported csv into several separate .csv files - Bfolders uses different field arrangements for its various categories. That was fine, but I noticed when I converted the files to xml and then imported, that the group structure was created separately for each import, rather than merging the groups. The KeePass builtin generic csv importer will merge the groups, but it has other problems which make it difficult for what I want. Is there a merge facility in your script to do this ? Or have I missed something in the setup ( quite possible )

[Paul]

There is no merge.
The groups are sorted before the conversion so there should be no issue if you put them all in the same CSV file and then convert the single file.

cheers, Paul

[Adrian Jansen]

Thanks for the response Paul.
But my different CSV files have different field structures - some contain more or different fields.
If there were only a few groups, I would do it manually, but my source data contains several hundred groups.
I think overall its easier to use the generic CSV converter for this.
But certainly for single imports, I really like your csv to xml system.

[Milada T]

First I would like to thank Paul for this useful utility.
I would like to share my experience with others to save their time.
I was transiting from mSecure 4, worked for me, although author adapted script for version 6.
I created CSV file with mSecure mBackup 3. Here I found that mBackup use as delimiter semicolon, Paul's script comma. So do not forget change it, and find, where you use these in your own records not to parse fields. If opening CSV in spreadsheet, it is good idea consider all files as text (except date/time fields) to not loose zero character at beginning of cells.

[Paul]

There is now an XML to CSV converter for those exporting from KeePass.
https://sourceforge.net/p/keepass/feature-requests/2840/#d9af

cheers, Paul