{"cells":[{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"a4794d226b478135f90c9f2d83f937effda5e80f","trusted":false},"cell_type":"markdown","source":"Following is the code I used to create a database in SQL Server that contains all the table for the NFL Punt competition.\nListed here in case anyone else wants to use it. Must use SQL Server version 2017 or above.\n\n-- creates database and puts in all nfl punt data\n-- using decimal type for GSISID as ngs-2016-post.csv has \n--   a decimal point in that field\n-- version 1.0 - no indexes on any fields\n-- version 3.0 - decimal field correction, now decimal(12,8), was just decimal, which did not have any decimal places. oops.\n-- version 4.0 - added input for 2017 data, chanded event length for ngs to 30\n\nUSE master;\nGO\n\nCREATE DATABASE KaggleNFLPunt3\nGO\n\nUSE KaggleNFLPunt3\nGO\n\nCREATE TABLE game_data( \n  GameKey        smallint, \n  Season_Year    smallint, \n  Season_Type    char(4), \n  Week           tinyint, \n  Game_Date      char(23), \n  Game_Day       char(9), \n  Game_Site      char(15), \n  Start_Time     char(5), \n  Home_Team      char(20), \n  HomeTeamCode   char(3), \n  Visit_Team     char(20), \n  VisitTeamCode  char(3), \n  Stadium        char(35), \n  StadiumType    char(28), \n  Turf           char(26), \n  GameWeather    varchar(80), \n  Temperature    char(5), \n  OutdoorWeather varchar(80))\n\nbulk insert game_data\nfrom 'c:\\nfl\\all\\game_data.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE play_information( \n  Season_Year          smallint,   \n  Season_Type          char(4),  \n  GameKey              smallint, \n  Game_Date            char(23), \n  Week                 tinyint, \n  PlayID               smallint, \n  Game_Clock           char(5), \n  YardLine             char(6), \n  Quarter              tinyint, \n  Play_Type            char(4), \n  Poss_Team            char(3), \n  Home_Team_Visit_Team char(7), \n  Score_Home_Visiting  char(7), \n  PlayDescription      varchar(max))\n\nbulk insert play_information\nfrom 'c:\\nfl\\all\\play_information.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE player_punt_data( \n  GSISID   int, \n  Number   char(3), \n  Position char(3))\n\nbulk insert player_punt_data\nfrom 'c:\\nfl\\all\\player_punt_data.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE video_review( \n  Season_Year                      smallint,   \n  GameKey                          smallint,   \n  PlayID                           smallint, \n  GSISID                           decimal,   \n  Player_Activity_Derived          char(8), \n  Turnover_Related                 char(3), \n  Primary_Impact_Type              char(16), \n  Primary_Partner_GSISID           char(7), \n  Primary_Partner_Activity_Derived char(8), \n  Friendly_Fire                    char(7))\n\nbulk insert video_review\nfrom 'c:\\nfl\\all\\video_review.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE play_player_role_data( \n  Season_Year smallint,   \n  GameKey     smallint,   \n  PlayID      smallint, \n  GSISID      decimal,   \n  Role        char(4))\n\nbulk insert play_player_role_data\nfrom 'c:\\nfl\\all\\play_player_role_data.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE video_footage_control( \n  season          smallint, \n  Season_Type     char(4), \n  Week            tinyint, \n  Home_team       char(20), \n  Visit_Team      char(20), \n  Qtr             tinyint, \n  PlayDescription varchar(max), \n  gamekey         smallint, \n  playid          smallint, \n  Preview_Link    varchar(max))\n\nbulk insert video_footage_control\nfrom 'c:\\nfl\\all\\video_footage-control.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE video_footage_injury( \n  season          smallint, \n  Type            char(4), \n  Week            tinyint, \n  Home_team       char(20), \n  Visit_Team      char(20), \n  Qtr             tinyint, \n  PlayDescription varchar(max), \n  gamekey         smallint, \n  playid          smallint, \n  Preview_Link    varchar(max))\n\nbulk insert video_footage_injury\nfrom 'c:\\nfl\\all\\video_footage-injury.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nCREATE TABLE ngs( \n  Season_Year smallint, \n  GameKey     smallint, \n  PlayID      smallint,  \n  GSISID      decimal, \n  Time        char(23),\n  x           decimal, \n  y           decimal, \n  dis         decimal, \n  o           decimal, \n  dir         decimal, \n  Event       char(30))\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2016-pre.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2016-reg-wk1-6.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2016-reg-wk7-12.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2016-reg-wk13-17.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2016-post.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2017-pre.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2017-reg-wk1-6.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2017-reg-wk7-12.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2017-reg-wk13-17.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\nbulk insert ngs\nfrom 'c:\\nfl\\all\\ngs-2017-post.csv'\nwith (\n  FORMAT = 'CSV', \n  FIELDQUOTE = '\"',\n  FIRSTROW = 2,\n  FIELDTERMINATOR = ',',  --CSV field delimiter\n  ROWTERMINATOR = '0x0a',   --Use to shift the control to next row\n  TABLOCK\n  )\n\ngo\n\n\n"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}