Where is the documentation ?!

[elmatou]

Hi Seamus, I'm planning to load several XLS files in a Mysql DB, and found your gem. It seems to be pretty great. I say "it seems" because I can't find any documentation on your work... am I dreaming or you can give me some url to find this out.

Best regards,

[seamusabshere]

hi Marco,

No excuses - the documentation will get better.

If you want to start now, the earth library has a huge number of examples.

~/code/earth (master) $ ack -ia -C 2 xls
lib/earth/air/flight_fuel_type.rb
1-class FlightFuelType < ActiveRecord::Base
2-  # this fallback is jet fuel
3:  falls_back_on :emission_factor =>           (21.09.pounds.to(:kilograms) / 1.gallons.to(:litres)),  # in pounds CO2/gallon fuel: http://www.eia.doe.gov/oiaf/1605/excel/Fuel%20Emission%20Factors.xls
4-                :radiative_forcing_index =>   2,                                                      # from Matt
5-                :density =>                   3.057                                                   # kg / gal

lib/earth/automobile/automobile_make_model_year_variant/data_miner.rb
339-      1998 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/98guide6.zip', :filename => '98guide6.csv' },
340-      1999 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/99guide.zip', :filename => '99guide6.csv' },
341:      2000 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/00data.zip', :filename => 'G6080900.xls' },
342:      2001 => { :url => 'http://static.brighterplanet.com/science/data/transport/automobiles/fuel_economy_guide/01guide0918.csv' }, # parseexcel 0.5.2 can't read Excel 5.0 { :url => 'http://www.fueleconomy.gov/FEG/epadata/01data.zip', :filename => '01guide0918.xls' }
343:      2002 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/02data.zip', :filename => 'guide_jan28.xls' },
344-      2003 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/03data.zip', :filename => 'guide_2003_feb04-03b.csv' },
345-      2004 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/04data.zip', :filename => 'gd04-Feb1804-RelDtFeb20.csv' },
--
375-    {
376-      2006 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/06data.zip', :filename => '2006_FE_Guide_14-Nov-2005_download.csv' },
377:      # the 07data.xls file provided by the government has a bad encoding
378-      2007 => { :url => 'http://static.brighterplanet.com/science/data/transport/automobiles/fuel_economy_guide/2007_FE_guide_ALL_no_sales_May_01_2007.csv' },
379-      2008 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/08data.zip', :filename => '2008_FE_guide_ALL_rel_dates_-no sales-for DOE-5-1-08.csv' },
--
408-    # 2010--?
409-    {
410:      2010 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/10data.zip', :filename => '2010FE Guide for DOE-all rel dates-no-sales 4-9-10public.xls' },
411:      2011 => { :url => 'http://www.fueleconomy.gov/FEG/epadata/11data.zip', :filename => '2011FEGuide-for DOE rel-dates before 1-23-2011-no-sales-01-10-2011public.xlsx' }
412-    }.sort { |a, b| a.first <=> b.first }.each do |year, options|
413-      import "#{ year } Fuel Economy Guide",

lib/earth/locality/egrid_region/data_miner.rb
8-    import "eGRID regions and loss factors derived from eGRID 2007 data",
9-           :url => 'http://www.epa.gov/cleanenergy/documents/egridzips/eGRID2007_Version1-1.zip',
10:           :filename => 'eGRID2007_Version1-1/eGRID2007V1_1_year0504_STIE_USGC.xls',
11-           :sheet => 'STIE05',
12-           :skip => 4,

lib/earth/locality/egrid_subregion/data_miner.rb
23-    # import "eGRID regions and electricity emission factors derived from eGRID 2007 data",
24-    #        :url => 'http://www.epa.gov/cleanenergy/documents/egridzips/eGRID2007_Version1-1.zip',
25:    #        :filename => 'eGRID2007_Version1-1/eGRID2007V1_1_year05_aggregation.xls',
26-    #        :sheet => 'SRL05',
27-    #        :skip => 3,

lib/earth/locality/zip_code/data_miner.rb
29-    
30-    import 'a list of zipcode states and eGRID Subregions from the US EPA',
31:           :url => 'http://www.epa.gov/cleanenergy/documents/egridzips/Power_Profiler_Zipcode_Tool_v3-0.xls',
32-           :sheet => 'Zip-subregion' do
33-      key   'name', :field_name => 'ZIP (character)'

lib/earth/residence/residence_fuel_price/data_miner.rb
124-    # electricity in dollars per kWh
125-    import 'residential electricity prices from the EIA',
126:           :url => 'http://www.eia.doe.gov/cneaf/electricity/page/sales_revenue.xls',
127-           :select => lambda { |row| row['Year'].to_s.first(4).to_i > 1989 } do
128-      key 'row_hash'
--
139-    # breaks if date-performance is enabled because DateTime.parse(...1899...) dies
140-    import 'residential natural gas prices from the EIA',
141:           :url => 'http://tonto.eia.doe.gov/dnav/ng/xls/ng_pri_sum_a_EPG0_FWA_DMcf_a.xls',
142-           :sheet => 'Data 1',
143-           :skip => 2,
--
159-    # ihough 4/22/2011 comment out for now; we'll deal with this later
160-    # import "residential fuel oil prices from the EIA",
161:    #        :url => 'http://tonto.eia.doe.gov/dnav/pet/xls/PET_PRI_RESID_A_EPPR_PTA_CPGAL_M.xls',
162-    #        :sheet => 'Data 1',
163-    #        :skip => 2,
--
178-    # ihough 4/22/2011 comment out for now; we'll deal with this later
179-    # import "residential propane prices from the EIA",
180:    #        :url => 'http://tonto.eia.doe.gov/dnav/pet/xls/PET_PRI_PROP_A_EPLLPA_PRT_CPGAL_M.xls',
181-    #        :sheet => 'Data 1',
182-    #        :skip => 2,

Again, I apologize for the lack of documentation, and it will get better.

Best, Seamus

[elmatou]

Thx for the "reference" I'm going to digg in it ! My concerns are for the mapping of columns.

After a quick look, I wonder if I could use mysql2 gem instead of mysql (known to be much faster), and bulk insert in the DB. (I have got 1M rows to insert recurrently from dozens of xls files.) If I'm able to understand your library, I problably will fork it. I can send a a pull request if you want.

Bye