Parsing csv's and storing data in temp table for reuse
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
README.md
SPLIT_STR.sql
sp_delimited_to_table.sql
sp_mysql_csv_parser.sql
vw_generator_16.sql
vw_generator_1m.sql
vw_generator_256.sql
vw_generator_4k.sql
vw_generator_64k.sql

README.md

mysql_csv_parser

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

Limitations

  • 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>
HOW TO USE?
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
Output
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

alt text