Skip to content

Instantly share code, notes, and snippets.

@jhyland87

jhyland87/gist:8766d0fd39d7c92404b2d5584a0aa30f

Created September 13, 2016 17:34
Show Gist options
  • Save jhyland87/8766d0fd39d7c92404b2d5584a0aa30f to your computer and use it in GitHub Desktop.
Save jhyland87/8766d0fd39d7c92404b2d5584a0aa30f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
$ time xls2sql -i /Users/jhyland/Documents/scripts/python/work/excel-to-sql/excel-templates/ON-DTS\ Replen\ OFF\ -\ 20-rows.xls -q "UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '{col:A}' AND ITEMID IN ({range:B-*;j:,})" --row-start 5
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '532' AND ITEMID IN (118469,117677,118233,118474,118467,116726,116727,116732,116730,117676,117859,117460,116729,119056,117472,116731,117481,117866,117461,117463,117868,117462,117483,117869,117464,117484,117493,118509,118507,117466,118508,117891,117468,117469,118566,118466,118471,118472,118470,118468,117878,118946,118947,118948,118950,117880,118510,117471,117473,117893,117892,117476,117895,117896,117897,117485,118624,118517,117474,117475,117486,118626,117901,117496,117490,117906,117908,118527,118526,117479,117477,117910,117912,117478,117480,117491,117501,118711,118716,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '773' AND ITEMID IN (117677,113881,116732,116730,118475,117460,119056,117472,116731,117866,117463,117483,117869,117493,117465,118566,118471,118470,117471,117897,118517,117474,117475,117486,117489,117898,117496,117905,117907,117909,117908,117479,117477,117910,117500,118711,118716,118715,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '835' AND ITEMID IN (117677,113881,118474,118467,116727,116732,116730,117676,118475,116728,117460,116729,119056,116731,117481,117868,117487,117493,118466,118471,118472,117878,117471,117474,117496,117906,117905,117907,117479,117480,118711,118716,118715,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '838' AND ITEMID IN (117677,113881,118474,118467,116726,116733,118243,118463,118477,116732,116730,117676,118475,116728,116729,119056,116731,117866,117482,118507,117466,118566,118471,118472,118470,117878,117486,117496,117905,117909,117501,118244,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '839' AND ITEMID IN (117677,113881,118233,118467,116732,118475,119056,116731,117462,117891,118863,118466,117471,117488,117499,117905,117477,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '840' AND ITEMID IN (118469,117677,118795,113881,118233,118474,118467,116726,116727,116733,119073,118243,118460,118245,118465,118461,118463,118459,118477,116732,116730,118475,116728,116729,119056,117472,116731,117481,117482,117483,117493,118874,118566,118799,118466,118471,118472,118947,118948,118949,118800,117894,118516,117898,117907,118526,118244,118711,118716,118715,118712,118713,119063);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '843' AND ITEMID IN (117677,113881,118467,116726,116727,116733,119073,118460,118463,118477,116732,118475,116728,117460,116729,119056,117472,116731,117481,117461,117463,117482,117462,117869,117464,117484,117493,118874,117465,117466,118508,117891,117468,118566,118466,118471,118472,118468,117878,118948,118949,117880,118510,117471,117894,117473,117893,117892,117476,117895,117897,118624,118517,117474,117475,118569,117486,117488,117489,118516,118626,117901,117496,117499,117906,117905,117909,118527,118526,117479,117912,117480,117491,117501,117500,118244,118711,118716,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '845' AND ITEMID IN (118469,117677,113881,118233,118467,116726,116727,118477,116732,116730,117676,118475,116728,116729,119056,116731,117463,117868,117468,117469,118566,118466,118471,118472,118470,117878,118945,118946,117880,118510,117475,118516,118626,117496,117490,117906,117907,117908,118526,117479,117912,117480,117500,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '1749' AND ITEMID IN (118469,117677,118233,118467,117676,116728,117859,117460,116729,119056,116731,117481,117461,117482,117868,117869,117464,117487,117465,117891,117468,117469,118466,118471,118470,118468,117878,117897,117474,117475,117486,117488,117489,117901,117906,117905,117907,117909,118526,117479,117477,117480,117501,118711,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '1836' AND ITEMID IN (113881,118233,118474,118467,116730,117676,118475,116729,119056,116731,117481,117461,117463,117868,117462,117869,118466,118471,118472,118470,117905,117907,118711,118716,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '2000' AND ITEMID IN (118469,117677,113881,118233,118474,118467,116726,116732,116730,116729,119056,116731,117482,117868,117462,117869,118466,118470,118468,117479,117480,118711);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3015' AND ITEMID IN (117677,118474,118467,116726,116730,117676,118475,119056,118466,118471,118472,118470,118468,117471,117473,117897,117474,117489,117496,117906,117905,117907,117909,117479,117478,117480,117501,118716,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3068' AND ITEMID IN (118469,117677,118795,113881,118233,118474,118467,116726,116726,116727,116733,119073,118243,118245,118465,118461,118463,118801,118477,116732,117676,118475,118455,118452,118453,118454,118457,118456,116728,117859,117460,116729,119056,117472,116731,117461,117482,117868,117483,117487,118509,118507,118508,118625,117468,117469,118566,118799,118466,118471,118472,118470,117878,118945,118946,118947,118948,118950,118949,118800,118510,117473,117893,117476,117895,118624,117474,118626,117898,117490,117499,117906,117905,117907,117909,118527,118526,117477,117910,117480,117501,118244,119068,119070,119071,118711,118716,118715,118714,118712,118713,119063);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3069' AND ITEMID IN (118469,117677,113881,118474,118467,116726,116727,116733,119073,118243,118460,118245,118465,118461,118463,118459,118477,116732,116730,118475,116728,119056,116731,117481,117463,117462,117464,118566,118466,118471,118472,118470,118468,117897,117486,118516,117901,118244,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3070' AND ITEMID IN (117677,118474,118467,116726,116727,116730,118475,116729,119056,116731,117481,117461,117482,117462,118466,118471,118472,118470,118468,117471,117476,117897,117499,117477,117478,117480,117491,118711,118716,118715,118714,118713);
real 0m1.733s
user 0m1.289s
sys 0m0.037s
$ time xls2sql -i /Users/jhyland/Documents/scripts/python/work/excel-to-sql/excel-templates/ON-DTS\ Replen\ OFF\ -\ 20-rows.xlsx -q "UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '{col:A}' AND ITEMID IN ({range:B-*;j:,})" --row-start 5
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '532' AND ITEMID IN (118469,117677,118233,118474,118467,116726,116727,116732,116730,117676,117859,117460,116729,119056,117472,116731,117481,117866,117461,117463,117868,117462,117483,117869,117464,117484,117493,118509,118507,117466,118508,117891,117468,117469,118566,118466,118471,118472,118470,118468,117878,118946,118947,118948,118950,117880,118510,117471,117473,117893,117892,117476,117895,117896,117897,117485,118624,118517,117474,117475,117486,118626,117901,117496,117490,117906,117908,118527,118526,117479,117477,117910,117912,117478,117480,117491,117501,118711,118716,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '773' AND ITEMID IN (117677,113881,116732,116730,118475,117460,119056,117472,116731,117866,117463,117483,117869,117493,117465,118566,118471,118470,117471,117897,118517,117474,117475,117486,117489,117898,117496,117905,117907,117909,117908,117479,117477,117910,117500,118711,118716,118715,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '835' AND ITEMID IN (117677,113881,118474,118467,116727,116732,116730,117676,118475,116728,117460,116729,119056,116731,117481,117868,117487,117493,118466,118471,118472,117878,117471,117474,117496,117906,117905,117907,117479,117480,118711,118716,118715,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '838' AND ITEMID IN (117677,113881,118474,118467,116726,116733,118243,118463,118477,116732,116730,117676,118475,116728,116729,119056,116731,117866,117482,118507,117466,118566,118471,118472,118470,117878,117486,117496,117905,117909,117501,118244,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '839' AND ITEMID IN (117677,113881,118233,118467,116732,118475,119056,116731,117462,117891,118863,118466,117471,117488,117499,117905,117477,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '840' AND ITEMID IN (118469,117677,118795,113881,118233,118474,118467,116726,116727,116733,119073,118243,118460,118245,118465,118461,118463,118459,118477,116732,116730,118475,116728,116729,119056,117472,116731,117481,117482,117483,117493,118874,118566,118799,118466,118471,118472,118947,118948,118949,118800,117894,118516,117898,117907,118526,118244,118711,118716,118715,118712,118713,119063);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '843' AND ITEMID IN (117677,113881,118467,116726,116727,116733,119073,118460,118463,118477,116732,118475,116728,117460,116729,119056,117472,116731,117481,117461,117463,117482,117462,117869,117464,117484,117493,118874,117465,117466,118508,117891,117468,118566,118466,118471,118472,118468,117878,118948,118949,117880,118510,117471,117894,117473,117893,117892,117476,117895,117897,118624,118517,117474,117475,118569,117486,117488,117489,118516,118626,117901,117496,117499,117906,117905,117909,118527,118526,117479,117912,117480,117491,117501,117500,118244,118711,118716,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '845' AND ITEMID IN (118469,117677,113881,118233,118467,116726,116727,118477,116732,116730,117676,118475,116728,116729,119056,116731,117463,117868,117468,117469,118566,118466,118471,118472,118470,117878,118945,118946,117880,118510,117475,118516,118626,117496,117490,117906,117907,117908,118526,117479,117912,117480,117500,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '1749' AND ITEMID IN (118469,117677,118233,118467,117676,116728,117859,117460,116729,119056,116731,117481,117461,117482,117868,117869,117464,117487,117465,117891,117468,117469,118466,118471,118470,118468,117878,117897,117474,117475,117486,117488,117489,117901,117906,117905,117907,117909,118526,117479,117477,117480,117501,118711,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '1836' AND ITEMID IN (113881,118233,118474,118467,116730,117676,118475,116729,119056,116731,117481,117461,117463,117868,117462,117869,118466,118471,118472,118470,117905,117907,118711,118716,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '2000' AND ITEMID IN (118469,117677,113881,118233,118474,118467,116726,116732,116730,116729,119056,116731,117482,117868,117462,117869,118466,118470,118468,117479,117480,118711);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3015' AND ITEMID IN (117677,118474,118467,116726,116730,117676,118475,119056,118466,118471,118472,118470,118468,117471,117473,117897,117474,117489,117496,117906,117905,117907,117909,117479,117478,117480,117501,118716,118714,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3068' AND ITEMID IN (118469,117677,118795,113881,118233,118474,118467,116726,116726,116727,116733,119073,118243,118245,118465,118461,118463,118801,118477,116732,117676,118475,118455,118452,118453,118454,118457,118456,116728,117859,117460,116729,119056,117472,116731,117461,117482,117868,117483,117487,118509,118507,118508,118625,117468,117469,118566,118799,118466,118471,118472,118470,117878,118945,118946,118947,118948,118950,118949,118800,118510,117473,117893,117476,117895,118624,117474,118626,117898,117490,117499,117906,117905,117907,117909,118527,118526,117477,117910,117480,117501,118244,119068,119070,119071,118711,118716,118715,118714,118712,118713,119063);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3069' AND ITEMID IN (118469,117677,113881,118474,118467,116726,116727,116733,119073,118243,118460,118245,118465,118461,118463,118459,118477,116732,116730,118475,116728,119056,116731,117481,117463,117462,117464,118566,118466,118471,118472,118470,118468,117897,117486,118516,117901,118244,118711,118716,118715,118714,118712,118713);
UPDATE STOREITEM SET replenish_status = 'N' WHERE COMPANYID = '123' AND STOREID = '3070' AND ITEMID IN (117677,118474,118467,116726,116727,116730,118475,116729,119056,116731,117481,117461,117482,117462,118466,118471,118472,118470,118468,117471,117476,117897,117499,117477,117478,117480,117491,118711,118716,118715,118714,118713);
real 0m1.723s
user 0m1.654s
sys 0m0.047s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment