As I have been creating CSV files for several books recently (trying to get the metadata filled out more completely and consistently), I thought it might be time to share the tool I created to properly format the CSV and provide some data validation for the spelling, names, keys, time signatures, etc. as they are entered.
After creating a tool on LibreOffice sheets and Excel, I switched to google sheets because the data validation and autofill capabilities were better. (It also makes it possible to do data entry on my computer or phone.) A shared version is available at the link below.
https://docs.google.com/spreadsheets/d/1...sp=sharing
Add a copy of this workbook to your google drive to use it to create your own CSV files.
The workbook consists of three worksheets.
- The first sheet (CSVFile) is the final formatted data that can be downloaded/exported to CSV format.
- The second sheet (FullData) is the data entry sheet. Data can be entered into the blue cells. The white cells contain formulas (modify at your own risk).
- The last sheet (DataLists) contains the lists of composers/artists/albums/keys/signatures etc. that the second sheet uses for data validation. Additional information can be added to the lists at the bottom and the list can be selected and re-sorted, if desired.
The first sheet (CSVFile) directly references several cells from the second sheet (FullData). For longer books with lots of songs, the formulas may need to be copied to additional rows. For shorter books, the formulas in the extra rows may need to be deleted to not end up with a bunch of extra empty lines in the final CSV file.
The second sheet (FullData) has a 'start page' column that is used to enter the page number for the fist page of each song. The next two columns automatically calculate the end page and then the page range (as additional songs and 'start page(s)' are added). The last song of the book/pdf will need to have the formula in column C overridden to show the number for the last page of the song (or pdf). This change will need to be reverted when the sheet is used for another book/PDF. The last page column assumes that all of the song starting pages occur on their own pages (they don't share the page with previous songs). If the book has places where more than one song share a page, the formula will not be valid and will either need to be adjusted or replaced with correct page numbers.
The second sheet (FullData) also has several columns under some headings to allow multiple items to be added so that they can be data validated and then combined in a format that CSV import will then add as multiple entries in the same field. Key, Composer, Genre, Album, Artist, Instrument (custom group), and Signatures have multiple columns of data (most have 6 but some have less). The data in these columns is combined in the next (seventh) column with the appropriate separation | symbol between entries. If additional entries are needed they can be manually combined in an entry cell. Skipping cells in these groups (say entries for Artist 1, 2, 4, 5) will not add the data after the gap (only Artist 1 and 2 shown in the final cell). It should be noted that duplicate entries in one of these groups (on a row) is not allowed by MobileSheets. For example, if a song modulates from C to Cm and back to C, then only C and Cm can be entered in the Keys group. A second C can be entered on the spreadsheet but MobileSheets will show an error during import.
Each cell with data validation enabled has a little triangle on the right. Clicking the triangle will show a drop-down list of data from sheet 3 (DataLists) for that category. Typing text into the cell will automatically show and filter the list. The mouse or arrow keys can be used to select an option from the filtered list. Options that are not listed can be fully typed out in the cell. They will show a little red triangle in the top right corner of the cell (indicating that the data in that cell is not on the list). Additional information can be added to the lists on the 'DataLists' sheet to remove the red triangles in the cells on the 'FullData' sheet.
The third sheet (DataLists) contains the lists of data for several categories of information and is used for the data validation of category cells on sheet two (FullData). The data in these lists can be added to, replaced, and/or modified. I used a database editor to access the MobileSheets databases for my library and export several of these lists. I copied the data in those lists to the appropriate column on the 'DataLists' sheet. I've since edited, modified, and added to the entries in most of the lists and then sorted the data. There are probably still issues and duplicates, but it's better than is used to be. Feel free to replace any of the columns of data with data from your library. The data in any column can be selected and then sorted (by range) if desired. Sorting isn't required for the data validation to work on the second sheet but it does make it easier to find duplicates and typos.
Hope this helps someone. It's already saved me hundreds of hours of data entry and editing.
After creating a tool on LibreOffice sheets and Excel, I switched to google sheets because the data validation and autofill capabilities were better. (It also makes it possible to do data entry on my computer or phone.) A shared version is available at the link below.
https://docs.google.com/spreadsheets/d/1...sp=sharing
Add a copy of this workbook to your google drive to use it to create your own CSV files.
The workbook consists of three worksheets.
- The first sheet (CSVFile) is the final formatted data that can be downloaded/exported to CSV format.
- The second sheet (FullData) is the data entry sheet. Data can be entered into the blue cells. The white cells contain formulas (modify at your own risk).
- The last sheet (DataLists) contains the lists of composers/artists/albums/keys/signatures etc. that the second sheet uses for data validation. Additional information can be added to the lists at the bottom and the list can be selected and re-sorted, if desired.
The first sheet (CSVFile) directly references several cells from the second sheet (FullData). For longer books with lots of songs, the formulas may need to be copied to additional rows. For shorter books, the formulas in the extra rows may need to be deleted to not end up with a bunch of extra empty lines in the final CSV file.
The second sheet (FullData) has a 'start page' column that is used to enter the page number for the fist page of each song. The next two columns automatically calculate the end page and then the page range (as additional songs and 'start page(s)' are added). The last song of the book/pdf will need to have the formula in column C overridden to show the number for the last page of the song (or pdf). This change will need to be reverted when the sheet is used for another book/PDF. The last page column assumes that all of the song starting pages occur on their own pages (they don't share the page with previous songs). If the book has places where more than one song share a page, the formula will not be valid and will either need to be adjusted or replaced with correct page numbers.
The second sheet (FullData) also has several columns under some headings to allow multiple items to be added so that they can be data validated and then combined in a format that CSV import will then add as multiple entries in the same field. Key, Composer, Genre, Album, Artist, Instrument (custom group), and Signatures have multiple columns of data (most have 6 but some have less). The data in these columns is combined in the next (seventh) column with the appropriate separation | symbol between entries. If additional entries are needed they can be manually combined in an entry cell. Skipping cells in these groups (say entries for Artist 1, 2, 4, 5) will not add the data after the gap (only Artist 1 and 2 shown in the final cell). It should be noted that duplicate entries in one of these groups (on a row) is not allowed by MobileSheets. For example, if a song modulates from C to Cm and back to C, then only C and Cm can be entered in the Keys group. A second C can be entered on the spreadsheet but MobileSheets will show an error during import.
Each cell with data validation enabled has a little triangle on the right. Clicking the triangle will show a drop-down list of data from sheet 3 (DataLists) for that category. Typing text into the cell will automatically show and filter the list. The mouse or arrow keys can be used to select an option from the filtered list. Options that are not listed can be fully typed out in the cell. They will show a little red triangle in the top right corner of the cell (indicating that the data in that cell is not on the list). Additional information can be added to the lists on the 'DataLists' sheet to remove the red triangles in the cells on the 'FullData' sheet.
The third sheet (DataLists) contains the lists of data for several categories of information and is used for the data validation of category cells on sheet two (FullData). The data in these lists can be added to, replaced, and/or modified. I used a database editor to access the MobileSheets databases for my library and export several of these lists. I copied the data in those lists to the appropriate column on the 'DataLists' sheet. I've since edited, modified, and added to the entries in most of the lists and then sorted the data. There are probably still issues and duplicates, but it's better than is used to be. Feel free to replace any of the columns of data with data from your library. The data in any column can be selected and then sorted (by range) if desired. Sorting isn't required for the data validation to work on the second sheet but it does make it easier to find duplicates and typos.
Hope this helps someone. It's already saved me hundreds of hours of data entry and editing.
Sam
Surface Pro 7 with MSP
Surface Pro 7 with MSP