0

I have to migrate a password database (Keepass) by using a csv file of it, to a new application by using its API. The API is updated with post requests, those needs JSON data format. What I need to do is to use the KeePass CSV to export the passwords and other pieces of information linked to it to the API. I decided todo a script using bash and awk.

The columns of the csv file are arranged like this :

"Group","Title","Username","Password","URL","Notes","TOTP","Icon","Last Modified","Created"

The field "notes" is multiline because some of the comments have line breaks into them.

"That's an important note, <br/>
some extra infos <br/>
concerning a password"

Here's an example of the API request to post the data, the data field is in JSON format :

I didn't put all the needed fields on this request but you can already see how it would work. Some of the field names are different because the KeePass and the API fields name are differently made

var1=name
var2=my.name
var3=password456

curl -s --request PUT -u username123:password123 -H 'Content-Type: application/json; charset=utf-8' https://tpm.mydomain.com/index.php/api/v5/passwords/1659.json --data-binary @- <<DATA
{
"name": "$var1",
"username": "$var2",
"password": "$var3"
}
DATA

I have planned to parse my CSV file field by field and then when I finish to parse the row, I do my API request to post the password in the database. Then I do this for every remaining rows.

To process the CSV I find the AWK language, it seems very handy and quite useful for my situation. I've come with multiple testing on my file with the gsub command, helping me to replace the line breaks (\n). I don't really know how to go further. Here's some of them (only the first work :

cat keepass.csv | awk NF=NF RS=/\n/ OFS=\n
cat keepass.csv |awk 'BEGIN {RS=","}{gsub("/\n/","",$0); print $0}'
cat keepass.csv | awk 'BEGIN {RS=""}{gsub(/\n/,"",$6); print $0}'

I also know that you can share bash var by adding -v after awk. Here's the closest code I could have.

awk -v RS='"\n' -v FPAT='"[^"]*"|[^,]*' '{
print "Row n°", NR, ""
for (i=1; i<=NF; i++) {
sub(/^"/, "", $i)
printf "Field %d, value=[%s]\n", i, $i
}} keepass.csv

What I am looking for, would be a command to parse any column of my csv by taking in account the multilines notes and input them into the global var of bash in JSON format.

I think you need to structure it by doing something like :

awk -v 'BEGIN{parsing and replacing keeping '\n' of notes}
if end of row,
return boolean to bash for processing the API requests, wait,
restart the loop}''

I'm new to scripting, I think it can be done in only a few lines but I'm unsure on how to proceed. I can change the language to python if needed and I can add some tools to my code.

6
  • Do you need to do it in awk or can you use a dedicated csv parser?
    – terdon
    Jun 16 at 13:27
  • 2
    awk isn't great for parsing CSV files, because it doesn't know anything about quoted fields. If you use a language with proper CSV support (e.g., Python's csv module), you'll end up with a much more effective solution. csvkit is a command line tool for parsing CSV files that might be helpful.
    – larsks
    Jun 16 at 13:27
  • @larsks Do you think it is easier in python ? Because I could either change to python or just add csvkit to my script, I'm just unsure if it's better or just as the same for a some few lines of script. Thanks for the advice.
    – Plourfi
    Jun 16 at 13:44
  • 2
    What is easier depends on what you find easier. Everything is easy if you know how to do it :)
    – terdon
    Jun 16 at 14:03
  • Your question seems to focus on parsing the multi-line notes field. Assuming this is easy to parse, what would you want to do with the data? With the csvkit tools, it's trivial to convert the CSV to JSON (with csvjson), pull out a given column (with csvcut), grep for a particular record (csvgrep), etc. but nowhere in your question do you actually say what it is you want to do with the data. An example would be nice to see.
    – Kusalananda
    Jun 16 at 16:55

2 Answers 2

1

The multi line is a feature of a CSV cell, and you can use an utility that is CSV aware as Miller.

As examples, if you have this CSV you can run

  • mlr --csv cut -f fieldA acr.csv to cut the first column
  • mlr --icsv --ojson cut -f fieldA acr.csv to cut the first column and convert all to JSON
[
  {
    "fieldA": "That's an important note,\nsome extra infos\nConcerning a password\nIpsum"
  },
  {
    "fieldA": "hello"
  }
]

As you can see Miller is aware of cell carriage return (RFC4180 compliant).

Below an image of the sample input file.

enter image description here

-2

The field "notes" is multiline

No! CSV does not support multi-line. Absolutely not.

There are two common approaches if you need to store a multi-line string in CSV.

  1. Change the \n character in the original string to something other. Usually it is just a two-letter "\n" string.
  2. Change the record delimiter from \n into some other character which will not appear in the string. Often it is something like \x01.

The first approach would require some additional pre- and post-conversion, but pretty reliable.

The second - works very fine and easy, but not all CSV-able application can change record delimiter, and there is always a possibility that the character you used as record delimiter would appear inside a field.

If by some reason you cannot recreate the file and make it a proper CSV, I would suggest to go with a loop:

while read row
 if row has 6 fields ("Notes" is a 6th field)
   do
     append "\n" to it
     append next row to it
   repeat until the working row has 10 fields
   export the work row to output file
 end if
end while

Any language can do it (even bash).

3
  • 1
    CSV supports quoted multi-line fields. Any CSV parser would be able to read that note data. See page two of RFC 4180.
    – Kusalananda
    Jun 16 at 16:14
  • Unfortunately, it is not true. The CSV is so 'easy' that almost no one who writes an application for it - reads the RFC. I personally never saw a CVS reader which handles multi-line texts. And the TC for example, also uses such tool - or this topic wont be created in the first place.
    – White Owl
    Jun 16 at 22:33
  • 1
    We even have two tags on this site, csv for full-blown CSV data with fields that potentially embed newlines, quotes and commas, and csv-simple for simple comma-delimited data.
    – Kusalananda
    Jun 16 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.