Import BirdDB.txt to birds.db

[Phaeton]

So I made a rookie mistake and backed up BirdDB.txt rather than /scripts/birds.db. Is there a way to import that .txt file back into the database?

[mcguirepr89]

Yes, this should be possible.

To do that, enter the SQL prompt:

sqlite3 ~/BirdNET-Pi/scripts/birds.db

Then

.mode csv
.import /home/<YOUR USERNAME HERE>/BirdNET-Pi/BirdDB.txt detections

Make sure you change <YOUR USERNAME HERE> to your actual system user.

I haven't tested that, but it should work? EDIT: The above code has been tested and does work!

Let me know if that works for you

[Phaeton]

I just figured out a different approach. I dropped the .txt file into a spreadsheet, added a File_Name column and formula at the end to generate the file name and imported using adminer. Seems to work.

[Phaeton]

If someone else happens to do the same thing as me down the road, do the following.

1. Open the BirdDB.txt in a spreadsheet application, like Excel/Google Sheets
2. If it does not do it for you automatically, Split Text to Columns on the semicolon ";"
3. Add a column to the end labelled "File_Name"
4. Be sure and adjust the Time column to add back/keep the preceding zero on the single digit timestamps. Excel/Google Sheets will remove these by default. Change the format to Text to lock in the change. (If you miss this step, it will cause sort issues in your database/graphs).
5. Add and drag down the following formula =SUBSTITUTE(D2," ","_")&"-"&TRUNC(ROUND(E2,2),2)*100&"-"&text(A2,"YYYY-MM-DD")&"-birdnet-"&text(B2,"HH:MM:SS")&".mp3"
6. Save the file as a .csv
7. Navigate to Import in Adminer. Tools -> Database Maintenance -> Login -> detections -> Select data -> Import (scroll to the bottom to find)
8. Select file saved in step 5 and select Import

Hopefully no one else makes the same mistake I did, but if you did, it's pretty easy to recover from.

[DD4WH]

Hi Phaeton,

thanks for your suggestion !

I have not managed to follow your instructions completely and have several issues regarding this import:

• when I use Excel that uses a different language and different column separators and different decimal separators AND different separator character for the Excel formulas, the formula is a little different. For example, this is what I use for a German Excel system: =WECHSELN(D2;" ";"_")&"-"&KÜRZEN(RUNDEN(E2;2);2)*100&"-"&TEXT(A2;"JJJJ-MM-TT")&"-birdnet-"&TEXT(B2;"hh:MM:ss")&".mp3"
• when I save the file as a csv file in Excel, the csv-file automatically uses the separator and decimal separator characters of the system. So it probably causes problems to use a csv file (but I have not tested this)
• I seem unable to understand what Adminer. Tools is !? Is it the Tools section in BirdNET-Pi ? Or is this a separate software that you setup on the Raspberry Pi ?

[Phaeton]

• when I save the file as a csv file in Excel, the csv-file automatically uses the separator and decimal separator characters of the system. So it probably causes problems to use a csv file (but I have not tested this)

I am not in front of my machine at the moment, but I believe you can specify the delimiter in save options.

• I seem unable to understand what Adminer. Tools is !? Is it the Tools section in BirdNET-Pi ? Or is this a separate software that you setup on the Raspberry Pi ?

This is Tools on BirdNET-Pi. Starting at Tools, each option following a “->” should be a mouse click. Let me know if I missed something and I’ll edit my answer.