Parsing csv's and storing data in temp table for reuse
MySQL does not allow passing table variables to procedures. I developed this utility as a workaround for that.
- Building on top of the awesome function provided by FEDERICO CARGNELUTTI, mysql csv parser utility enables parsing of csv into a my sql temp table.
- Pass the csv as string to the procedure and select from temp table to query the data.
- The utility supports custom delimiters and data type passing as well.
- Defaults:
- Separator ','
- Line break '\n'
- Data type 'varchar(256)'
- vw_generator views generate sequential numbers used in join to avoid looping. Sequence generated in order 16, 256, 4k, 64k, 1m
- Does not support escape sequence.
- Not intended to work with large csv's
- Supported data types
<int, int>, <string, varchar>, <float, decimal>, <date, date>, <datetime, datetime>, <bool, bit>
set @csv =
'LotFrontage,LotArea,OverallQual,OverallCond,YearBuilt,YearRemodAdd,MasVnrArea,BsmtFinSF1,BsmtFinSF2,BsmtUnfSF,TotalBsmtSF,1stFlrSF,2ndFlrSF,LowQualFinSF,GrLivArea,BsmtFullBath,BsmtHalfBath,FullBath,HalfBath,BedroomAbvGr,KitchenAbvGr,TotRmsAbvGrd,Fireplaces,GarageYrBlt,GarageCars,GarageArea,WoodDeckSF,OpenPorchSF,EnclosedPorch,3SsnPorch,ScreenPorch,PoolArea,MiscVal,YrSold,SalePrice
65.0,8450,7,5,2003,2003,196.0,706,0,150,856,856,854,0,1710,1,0,2,1,3,1,8,0,2003.0,2,548,0,61,0,0,0,0,0,2008,1815508.32608
80.0,9600,6,8,1976,1976,0.0,978,0,284,1262,1262,0,0,1262,0,1,2,0,3,1,6,1,1976.0,2,460,298,0,0,0,0,0,0,2007,3481509.54666
68.0,11250,7,5,2001,2002,162.0,486,0,434,920,920,866,0,1786,1,0,2,1,3,1,6,1,2001.0,2,608,0,42,0,0,0,0,0,2008,-2986668.02181
60.0,9550,7,5,1915,1970,0.0,216,0,540,756,961,756,0,1717,1,0,1,0,3,1,7,1,1998.0,3,642,0,35,272,0,0,0,0,2006,-272857.824724
84.0,14260,8,5,2000,2000,350.0,655,0,490,1145,1145,1053,0,2198,1,0,2,1,4,1,9,1,2000.0,3,836,192,84,0,0,0,0,0,2008,-3410079.79621
85.0,14115,5,5,1993,1995,0.0,732,0,64,796,796,566,0,1362,1,0,1,1,1,1,5,0,1993.0,2,480,40,30,0,320,0,0,700,2009,898786.664014
75.0,10084,8,5,2004,2005,186.0,1369,0,317,1686,1694,0,0,1694,1,0,2,0,3,1,7,1,2004.0,2,636,255,57,0,0,0,0,0,2007,-5084980.62723
0.0,10382,7,6,1973,1973,240.0,859,32,216,1107,1107,983,0,2090,1,0,2,1,3,1,7,2,1973.0,2,484,235,204,228,0,0,0,350,2009,6106373.02631
51.0,6120,7,5,1931,1950,0.0,0,0,952,952,1022,752,0,1774,0,0,2,0,2,2,8,2,1931.0,2,468,90,0,205,0,0,0,0,2008,1203908.74905
50.0,7420,5,6,1939,1950,0.0,851,0,140,991,1077,0,0,1077,1,0,1,0,2,2,5,2,1939.0,1,205,0,4,0,0,0,0,0,2008,-3212458.1486
70.0,11200,5,5,1965,1965,0.0,906,0,134,1040,1040,0,0,1040,1,0,1,0,3,1,5,0,1965.0,1,384,0,0,0,0,0,0,0,2008,-3387745.84394
85.0,11924,9,5,2005,2006,286.0,998,0,177,1175,1182,1142,0,2324,1,0,3,0,4,1,11,2,2005.0,3,736,147,21,0,0,0,0,0,2006,3823545.55828
0.0,12968,5,6,1962,1962,0.0,737,0,175,912,912,0,0,912,1,0,1,0,2,1,4,0,1962.0,1,352,140,0,0,0,176,0,0,2008,-77165.2537124
91.0,10652,7,5,2006,2007,306.0,0,0,1494,1494,1494,0,0,1494,0,0,2,0,3,1,7,1,2006.0,3,840,160,33,0,0,0,0,0,2007,2711106.991
0.0,10920,6,5,1960,1960,212.0,733,0,520,1253,1253,0,0,1253,1,0,1,1,2,1,5,1,1960.0,1,352,0,213,176,0,0,0,0,2008,-5966015.38096
51.0,6120,7,8,1929,2001,0.0,0,0,832,832,854,0,0,854,0,0,1,0,2,1,5,0,1991.0,2,576,48,112,0,0,0,0,0,2007,616693.131286
0.0,11241,6,7,1970,1970,180.0,578,0,426,1004,1004,0,0,1004,1,0,1,0,2,1,5,1,1970.0,2,480,0,0,0,0,0,0,700,2010,-2090992.91936
72.0,10791,4,5,1967,1967,0.0,0,0,0,0,1296,0,0,1296,0,0,2,0,2,2,6,0,1967.0,2,516,0,0,0,0,0,0,500,2006,-3701321.22814
66.0,13695,5,5,2004,2004,0.0,646,0,468,1114,1114,0,0,1114,1,0,1,1,3,1,6,0,2004.0,2,576,0,102,0,0,0,0,0,2008,-3614075.93147
70.0,7560,5,6,1958,1965,0.0,504,0,525,1029,1339,0,0,1339,0,0,1,0,3,1,6,0,1958.0,1,294,0,0,0,0,0,0,0,2009,2114793.38227
101.0,14215,8,5,2005,2006,380.0,0,0,1158,1158,1158,1218,0,2376,0,0,3,1,4,1,9,1,2005.0,3,853,240,154,0,0,0,0,0,2006,2114697.96233';
-- Leave blank or null to let all columns take the default data type
set @colDataType = 'float,float,float,float,int,int,float,float,float,float,float,float,float,float,float,int,int,int,int,int,int,int,int,int,int,float,float,float,float,float,float,float,float,int,float';
-- Leave blank or null for default separator and line break
set @seprator = ',';
set @lineBreak = '\n';
call sp_mysql_csv_parser(@csv, @colDataType, @seprator, @lineBreak);
select * from temp_csv; -- Temp table holding the csv data| LotFrontage | LotArea | OverallQual | OverallCond | YearBuilt | YearRemodAdd | MasVnrArea | BsmtFinSF1 | BsmtFinSF2 | BsmtUnfSF | TotalBsmtSF | 1stFlrSF | 2ndFlrSF | LowQualFinSF | GrLivArea | BsmtFullBath | BsmtHalfBath | FullBath | HalfBath | BedroomAbvGr | KitchenAbvGr | TotRmsAbvGrd | Fireplaces | GarageYrBlt | GarageCars | GarageArea | WoodDeckSF | OpenPorchSF | EnclosedPorch | 3SsnPorch | ScreenPorch | PoolArea | MiscVal | YrSold | SalePrice |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 65.0 | 8450 | 7 | 5 | 2003 | 2003 | 196.0 | 706 | 0 | 150 | 856 | 856 | 854 | 0 | 1710 | 1 | 0 | 2 | 1 | 3 | 1 | 8 | 0 | 2003.0 | 2 | 548 | 0 | 61 | 0 | 0 | 0 | 0 | 0 | 2008 | 1815508.32608 |
| 80.0 | 9600 | 6 | 8 | 1976 | 1976 | 0.0 | 978 | 0 | 284 | 1262 | 1262 | 0 | 0 | 1262 | 0 | 1 | 2 | 0 | 3 | 1 | 6 | 1 | 1976.0 | 2 | 460 | 298 | 0 | 0 | 0 | 0 | 0 | 0 | 2007 | 3481509.54666 |
| 68.0 | 11250 | 7 | 5 | 2001 | 2002 | 162.0 | 486 | 0 | 434 | 920 | 920 | 866 | 0 | 1786 | 1 | 0 | 2 | 1 | 3 | 1 | 6 | 1 | 2001.0 | 2 | 608 | 0 | 42 | 0 | 0 | 0 | 0 | 0 | 2008 | -2986668.02181 |
| 60.0 | 9550 | 7 | 5 | 1915 | 1970 | 0.0 | 216 | 0 | 540 | 756 | 961 | 756 | 0 | 1717 | 1 | 0 | 1 | 0 | 3 | 1 | 7 | 1 | 1998.0 | 3 | 642 | 0 | 35 | 272 | 0 | 0 | 0 | 0 | 2006 | -272857.824724 |
| 84.0 | 14260 | 8 | 5 | 2000 | 2000 | 350.0 | 655 | 0 | 490 | 1145 | 1145 | 1053 | 0 | 2198 | 1 | 0 | 2 | 1 | 4 | 1 | 9 | 1 | 2000.0 | 3 | 836 | 192 | 84 | 0 | 0 | 0 | 0 | 0 | 2008 | -3410079.79621 |
| 85.0 | 14115 | 5 | 5 | 1993 | 1995 | 0.0 | 732 | 0 | 64 | 796 | 796 | 566 | 0 | 1362 | 1 | 0 | 1 | 1 | 1 | 1 | 5 | 0 | 1993.0 | 2 | 480 | 40 | 30 | 0 | 320 | 0 | 0 | 700 | 2009 | 898786.664014 |
| 75.0 | 10084 | 8 | 5 | 2004 | 2005 | 186.0 | 1369 | 0 | 317 | 1686 | 1694 | 0 | 0 | 1694 | 1 | 0 | 2 | 0 | 3 | 1 | 7 | 1 | 2004.0 | 2 | 636 | 255 | 57 | 0 | 0 | 0 | 0 | 0 | 2007 | -5084980.62723 |
| 0.0 | 10382 | 7 | 6 | 1973 | 1973 | 240.0 | 859 | 32 | 216 | 1107 | 1107 | 983 | 0 | 2090 | 1 | 0 | 2 | 1 | 3 | 1 | 7 | 2 | 1973.0 | 2 | 484 | 235 | 204 | 228 | 0 | 0 | 0 | 350 | 2009 | 6106373.02631 |
| 51.0 | 6120 | 7 | 5 | 1931 | 1950 | 0.0 | 0 | 0 | 952 | 952 | 1022 | 752 | 0 | 1774 | 0 | 0 | 2 | 0 | 2 | 2 | 8 | 2 | 1931.0 | 2 | 468 | 90 | 0 | 205 | 0 | 0 | 0 | 0 | 2008 | 1203908.74905 |
| 50.0 | 7420 | 5 | 6 | 1939 | 1950 | 0.0 | 851 | 0 | 140 | 991 | 1077 | 0 | 0 | 1077 | 1 | 0 | 1 | 0 | 2 | 2 | 5 | 2 | 1939.0 | 1 | 205 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 2008 | -3212458.1486 |
| 70.0 | 11200 | 5 | 5 | 1965 | 1965 | 0.0 | 906 | 0 | 134 | 1040 | 1040 | 0 | 0 | 1040 | 1 | 0 | 1 | 0 | 3 | 1 | 5 | 0 | 1965.0 | 1 | 384 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2008 | -3387745.84394 |
| 85.0 | 11924 | 9 | 5 | 2005 | 2006 | 286.0 | 998 | 0 | 177 | 1175 | 1182 | 1142 | 0 | 2324 | 1 | 0 | 3 | 0 | 4 | 1 | 11 | 2 | 2005.0 | 3 | 736 | 147 | 21 | 0 | 0 | 0 | 0 | 0 | 2006 | 3823545.55828 |
| 0.0 | 12968 | 5 | 6 | 1962 | 1962 | 0.0 | 737 | 0 | 175 | 912 | 912 | 0 | 0 | 912 | 1 | 0 | 1 | 0 | 2 | 1 | 4 | 0 | 1962.0 | 1 | 352 | 140 | 0 | 0 | 0 | 176 | 0 | 0 | 2008 | -77165.2537124 |
| 91.0 | 10652 | 7 | 5 | 2006 | 2007 | 306.0 | 0 | 0 | 1494 | 1494 | 1494 | 0 | 0 | 1494 | 0 | 0 | 2 | 0 | 3 | 1 | 7 | 1 | 2006.0 | 3 | 840 | 160 | 33 | 0 | 0 | 0 | 0 | 0 | 2007 | 2711106.991 |
| 0.0 | 10920 | 6 | 5 | 1960 | 1960 | 212.0 | 733 | 0 | 520 | 1253 | 1253 | 0 | 0 | 1253 | 1 | 0 | 1 | 1 | 2 | 1 | 5 | 1 | 1960.0 | 1 | 352 | 0 | 213 | 176 | 0 | 0 | 0 | 0 | 2008 | -5966015.38096 |
| 51.0 | 6120 | 7 | 8 | 1929 | 2001 | 0.0 | 0 | 0 | 832 | 832 | 854 | 0 | 0 | 854 | 0 | 0 | 1 | 0 | 2 | 1 | 5 | 0 | 1991.0 | 2 | 576 | 48 | 112 | 0 | 0 | 0 | 0 | 0 | 2007 | 616693.131286 |
| 0.0 | 11241 | 6 | 7 | 1970 | 1970 | 180.0 | 578 | 0 | 426 | 1004 | 1004 | 0 | 0 | 1004 | 1 | 0 | 1 | 0 | 2 | 1 | 5 | 1 | 1970.0 | 2 | 480 | 0 | 0 | 0 | 0 | 0 | 0 | 700 | 2010 | -2090992.91936 |
| 72.0 | 10791 | 4 | 5 | 1967 | 1967 | 0.0 | 0 | 0 | 0 | 0 | 1296 | 0 | 0 | 1296 | 0 | 0 | 2 | 0 | 2 | 2 | 6 | 0 | 1967.0 | 2 | 516 | 0 | 0 | 0 | 0 | 0 | 0 | 500 | 2006 | -3701321.22814 |
| 66.0 | 13695 | 5 | 5 | 2004 | 2004 | 0.0 | 646 | 0 | 468 | 1114 | 1114 | 0 | 0 | 1114 | 1 | 0 | 1 | 1 | 3 | 1 | 6 | 0 | 2004.0 | 2 | 576 | 0 | 102 | 0 | 0 | 0 | 0 | 0 | 2008 | -3614075.93147 |
| 70.0 | 7560 | 5 | 6 | 1958 | 1965 | 0.0 | 504 | 0 | 525 | 1029 | 1339 | 0 | 0 | 1339 | 0 | 0 | 1 | 0 | 3 | 1 | 6 | 0 | 1958.0 | 1 | 294 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2009 | 2114793.38227 |
| 101.0 | 14215 | 8 | 5 | 2005 | 2006 | 380.0 | 0 | 0 | 1158 | 1158 | 1158 | 1218 | 0 | 2376 | 0 | 0 | 3 | 1 | 4 | 1 | 9 | 1 | 2005.0 | 3 | 853 | 240 | 154 | 0 | 0 | 0 | 0 | 0 | 2006 | 2114697.96233 |
