How to fix Kaspersky Password Export… or any data that is in a single column, not rows and columns.
A client wanted to know if there was an easy way to fix the exported password records so he could import into another password app. While he could use Excel’s Transpose to fix it, that is a lot of cut and pasting when you have hundreds of records.
My answer: Sure, and my method a lot less work than copy and paste.
I receive a couple of reports that are improperly formatted, and this is the method I use to fix them:
1. Open Excel. Row 1 is used for field names.
2. Paste the data in Column A
3. In B2, I enter the formula =A2. In C2, enter =A3, in D2 enter =A4. Continue for each row that belongs to the first record or to the longest record if data is not the same length or there are blank rows between some or all records.
Tip: Keyboard shortcut to show and hide formulas is Ctrl+ ` (back-tick/tilde key)
Note: if the records do not have the same number of fields as in my example, add enough columns to cover all values for the largest record or for blank rows between records. If the records have the same number of rows between each record, you can add a column to count the rows per record and sort by it (see last screenshot).
4. When each row in the first record is in a column in row 2, select those cells and drag to fill.
Press Ctrl+` to see the formulas.
5. Select all, Copy and Paste Special as Text to remove the formulas and keep the data.
6. Sort by a column. For example, if all the records have a web address, sort by the web address column so those records are all together. Delete the other records.
Example of adding a numbered column to sort by when each record has the same number of columns.
7. Copy the records to a new sheet then save the sheet as a CSV.
It's fast and fairly easy and harder to explain than it is to do. 🙂