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.
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'scsv
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.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 (withcsvjson
), pull out a given column (withcsvcut
), 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.