Pull and Validate Spreadsheet Data #451
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Pull and Validate Spreadsheet Data | |
| env: | |
| SPREADSHEET_ID: 1YGt_Y70oy6qc09ZZ7kip9DM2JGtRC2fAHxi6JXOIsSk | |
| on: | |
| workflow_dispatch: {} | |
| schedule: | |
| - cron: '0 10 * * *' | |
| jobs: | |
| Pull-And-Merge-Spreadsheet-Data: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Pulling Maui | |
| working-directory: ${{ runner.temp }} | |
| env: | |
| MAUI_SHEET_ID: '987737350' | |
| MAUI_FILE_NAME: 'maui.csv' | |
| run: | | |
| wget "${{ format('https://docs.google.com/spreadsheets/d/{0}/export?format=csv&gid={1}', '$SPREADSHEET_ID', '$MAUI_SHEET_ID') }}" -O $MAUI_FILE_NAME | |
| - name: Pulling Honolulu (Oahu) | |
| working-directory: ${{ runner.temp }} | |
| env: | |
| HONOLULU_SHEET_ID: '448730928' | |
| HONOLULU_SHEET_NAME: 'honolulu.csv' | |
| run: | | |
| wget "${{ format('https://docs.google.com/spreadsheets/d/{0}/export?format=csv&gid={1}', '$SPREADSHEET_ID', '$HONOLULU_SHEET_ID') }}" -O $HONOLULU_SHEET_NAME | |
| - name: Pulling Hawaii (Big Island) | |
| working-directory: ${{ runner.temp }} | |
| env: | |
| HAWAII_SHEET_ID: '835193382' | |
| HAWAII_SHEET_NAME: 'hawaii.csv' | |
| run: | | |
| wget "${{ format('https://docs.google.com/spreadsheets/d/{0}/export?format=csv&gid={1}', '$SPREADSHEET_ID', '$HAWAII_SHEET_ID') }}" -O $HAWAII_SHEET_NAME | |
| - name: Pulling Kauai | |
| working-directory: ${{ runner.temp }} | |
| env: | |
| KAUAI_SHEET_ID: '1657052642' | |
| KAUAI_SHEET_NAME: 'kauai.csv' | |
| run: | | |
| wget "${{ format('https://docs.google.com/spreadsheets/d/{0}/export?format=csv&gid={1}', '$SPREADSHEET_ID', '$KAUAI_SHEET_ID') }}" -O $KAUAI_SHEET_NAME | |
| - name: Merge Island CSVs | |
| working-directory: ${{ runner.temp }} # insert a new line after the last comma of each csv, that way each appended CSV after #Maui will append on a new line | |
| run: | # also started from 3rd line for only unique data from each file | |
| sed 's/,$/,\n/' hawaii.csv > hawaii_insert_line.csv | |
| sed 's/,$/,\n/' honolulu.csv > honolulu_insert_line.csv | |
| sed 's/,$/,\n/' kauai.csv > kauai_insert_line.csv | |
| sed 's/,$/,\n/' maui.csv > maui_insert_line.csv | |
| rm hawaii.csv | |
| rm honolulu.csv | |
| rm kauai.csv | |
| rm maui.csv | |
| head -n 2 maui_insert_line.csv > hawaii-zoning-data.csv && tail -n+3 -q *.csv >> hawaii-zoning-data.csv | |
| - name: Upload Artifact | |
| uses: actions/upload-artifact@v2 | |
| with: | |
| name: hawaii-zoning-data.csv | |
| path: ${{ runner.temp }}/hawaii-zoning-data.csv | |
| Validate-Data: | |
| needs: Pull-And-Merge-Spreadsheet-Data | |
| runs-on: ubuntu-latest | |
| steps: | |
| - uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| - name: Install csvkit | |
| run: | | |
| pip install csvkit | |
| - name: Download Artifact | |
| uses: actions/download-artifact@v2 | |
| with: | |
| name: hawaii-zoning-data.csv | |
| - name: Validate Data # Not doing any real validation, just outputting csvclean results to detect any surface problems | |
| run: | | |
| csvclean -n hawaii-zoning-data.csv | |
| Validate-CSV: | |
| needs: Validate-Data | |
| runs-on: ubuntu-latest | |
| steps: | |
| - uses: 'actions/checkout@v3' | |
| - name: Set up Python 3 | |
| uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| cache: 'pip' | |
| - name: Install petl | |
| working-directory: data-pipeline/csv-validation | |
| run: | | |
| pip install -r ./requirements.txt | |
| - name: Run Validation | |
| working-directory: data-pipeline/csv-validation | |
| run: | | |
| python validation.py | |
| Commit-Data: | |
| needs: Validate-CSV | |
| runs-on: ubuntu-latest | |
| steps: | |
| - uses: actions/checkout@v2 | |
| - name: Download Artifact | |
| uses: actions/download-artifact@v2 | |
| with: | |
| name: hawaii-zoning-data.csv | |
| - name: Get current date | |
| run: echo "NOW=$(date +'%Y-%m-%d')" >> $GITHUB_ENV | |
| - name: Move Artifact | |
| run: | |
| mv hawaii-zoning-data.csv data/ | |
| - name: Commit to repo | |
| run: | | |
| if [ -n "$(git status --porcelain)" ]; then | |
| echo "there are changes to commit"; | |
| else | |
| exit 0; | |
| fi | |
| git config --local user.email "[email protected]" | |
| git config --local user.name "GitHub Actions" | |
| git add data/hawaii-zoning-data.csv | |
| git commit -m "Spreadsheet data update ${{ env.NOW }}" | |
| git push |