gistfile1.txt

CLI ARGUMENTS
   ( * = Required for excel parsing and bulk query generation. )

Input Options: Arguments used for importing and parsing the excel sheet.

   * -i,  --in <file>                     Excel sheet to parse and use for the macro substitutions in the parsed query. Can be xls or xlsx.
                                          Examples: --in ./foo.xlsx; -i ./foo\ bar.xls

   * -q,  --query <query>                 Query "template" to use for the bulk query generation. Place macros in the query to customize it for each row (see "--help macros" for more info)
                                          Example:  --query "UPDATE accounts SET accounts.status = '{col:D;d:0}' WHERE account.id = '{col:A}!'"

          --macro-enclosure <enclosure>   Enclosures for the macros in the query. The default value is "{}", which means the macros need to be enclosed like so: "{col:a}" (Note: This needs to have an even amount of characters in it)
                                          Example:  --macro-enclosure "{{}}"

          --macro-enclosure <enclosure>   Enclosures for the macros in the query. The default value is "{}", which means the macros need to be enclosed like so: "{col:a}" (Note: This needs to have an even amount of characters in it)
                                          Example:  --macro-enclosure "{{}}"

     -r,  --row-start <row #>             This specifies what row the iterating and parsing should begin. (Defaults to 2, assumes header on line 1.)

          --key-val <char>                Change the character that will be separating the key and values within the macros. (Default is a colon. EG: {col:a})
                                          Examples: --key-val "-"; --key-val \|

Output Options: Arguments related to displaying or exporting the MySQL query results.

     -s,  --strict                        Disable passive mode by setting strict. Leaving passive mode enabled (default) will withhold queries that have empty results for macros. Disabling (by enabling strict) it will terminate the query generation.

     -o,  --out <file>                    Output file to save the SQL queries to. (.sql extension is appended if not in the value specified)
                                          Examples: --out ./update-items-queries; -o ./delete\ expired\ items.sql; -o "create new accounts.sql"

General Options: Any arguments that arent for input/output crap.

     -h,  --help {query,macros,explain}   Shows the general help menu, or help regarding a specific item or topic.
                                          Examples: --help macro; -h mode

     -e,  --explain                       Parses and explains the spreadsheet. (Argument --in is required, argument --row-start is recommended)


EXAMPLE USAGES

Basic Examples

   Parse the spreadsheet.xlsx file (starting at the defaulted line 2), writing an UPDATE query using the column C (defaulted to 'disabled'), and column A (required).
      python xls2sql.py -i spreadsheet.xlsx -q "UPDATE accounts SET accounts.status = '{col:C:d:disabled}' WHERE accounts.id = '{col:A}!'"

   Parse the spreadsheet.xlsx file (starting at the defaulted line 2) in strict mode, writing a DELETE query using the column A for the account ID and column C for the status. If any rows have empty values in columns A or C, the processing will terminate.
      python xls2sql.py -i spreadsheet.xlsx --strict --query "DELETE FROM accounts WHERE accounts.id = '{col:A}' AND accounts.status = '{col:C}'"

   Parse the spreadsheet.xlsx file (starting at line 5), writing an UPDATE query using the column C as the new status value, and anything in columns D or on as the collection of account IDs, saving all queries to update-accounts.sql
      python xls2sql.py --in-file spreadsheet.xlsx --row-start 5 -q "UPDATE accounts SET accounts.status = '{col:C}' WHERE accounts.id IN ({range:D-*})" --out-file update-accounts.sql