474
edits
Changes
Jump to navigation
Jump to search
VI_NATION FIRM_LAST_INV_DATE FIRM_FIRST_INV_DATEFIRM_WREG1_DESC
FIRM_AREA_CODE
VI_CAPITAL_CITY
VI_CAPITAL_NATION
VI_CAPITAL_COUNTRY
VI_CAPITAL_STATE
VI_ADR_LINE1
VI_ADR_LINE2
VI_POSTAL_CODE
VI_CITY
FIRM_COUNTY
FIRM_ROLE_DESC
VI_STATE
VI_STREET1
VI_STREET2
FIRM_WREG1_DESC
VI_ZIP
LoadFirmsLoadFirms2.sql
LoadFirmsLoadBranchOffices.sql DROP TABLE firmbasebranchoffices; CREATE TABLE firmbase branchoffices (
foundingdate date, --mm-dd-yyyy
location varchar(100),
nation varchar(100),
nationcode varchar(10),
datelastinv date, --mm-dd-yyyy
datefirstinv date, --mm-dd-yyyy
numportcos integer,
numrounds integer,
investedk money,
areacode integer,
bonationcode bozip varchar(100), bostatecode varchar(10010),
\COPY firmbase branchoffices FROM 'VCFirms1980USVCFirmBranchOffices1980-presentPresent-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --9734
Retrieving US VC Data From SDC (view source)
Revision as of 17:42, 24 October 2017
, 17:42, 24 October 2017→Branch Offices
*Firms includes branch office so attributes must be extracted
*Portfolio company descriptions - just the portco name, state, date of first inv, and the long description - has to be custom processed.
The next steps are detailed in [[VC Database Rebuild]].
</onlyinclude>
cat Out2.txt | perl -pe 's/\n//g' > Out3.txt
cat Out3.txt | perl -pe 's/#/\n/g' > Out4.txt
You will still have an issue running the normalizer on Out4.txt. I appended 100,000 filler characters to the last column header so that the normalizer will space things properly. However, the final output will have some long descriptions split across two lines. This will cause problems copying to a psql table. You will have to manually adjust these lines. Also remove all quotation marks from the text file because these will just cause trouble.
==Build Specs==
Core files:
USVCFunds1980-present.ssh VCFund1980-present-normal-NoFoot.txt
VCFund1980-present-normal.txt
VCFund1980-present-NoFoot.txt
VCFund1980-present.txt
VCFunds1980-present.rpt
Created: 69/1628/2017 3:2816:23 59 PM
Session Contents:
);
\COPY fundbase FROM 'VCFund1980-present-NoFoot-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--27588
===VC Firms===
Core Files: VCFirms1980USVCFirms1980-presentPresent.rpt VCFirms1980USVCFirms1980-presentPresent.ssh VCFirms1980USVCFirms1980-presentPresent.txt VCFirms1980USVCFirms1980-present.txt.org VCFirms1980-presentPresent-normal.txt
Created: 69/1629/2017 23:4446:42 06 PM
Session Contents:
DATABASE: Private Equity Firms (VIFM)
Venture Related Deals: Select All Venture Related Deals
Firm Portfolio Cos: 1Round Date: 01/101/1980 to 0609/1629/2017 (Custom) (Calendar) Firm Portfolio Cos: Nation : US
Custom Report Contents:
FIRM_NAME
VI_YEARFOUND
FIRM_FIRST_INV_DATE
FIRM_LAST_INV_DATE
VI_STREET1
VI_STREET2
VI_STREET3
VI_CITY
VI_ZIP
FIRM_AREA_CODE
FIRM_COUNTY
VI_STATE
VI_NATION
VI_NATION_LONG
FIRM_NUM_COMPANIES
FIRM_NUM_ROUNDS
FIRM_TOTIN
FIRM_CAP_MGT
FIRM_STATUS_DESC
FIRM_MSA_CODE
FIRM_ROLE_DESC
FIRM_GEOGRAPHY_PREF_DESC
FIRM_INDUSTRY_PREF_DESC
FIRM_STAGE_PREF_DESC
FIRM_TYPE_DESC
DROP TABLE firmbase;
CREATE TABLE firmbase ( firmname varchar(255), foundingdate date, --mm-dd-yyyy location varchar(100), nation varchar(100), nationcode varchar(10), datelastinv datefirstinv date, --mm-dd-yyyy datefirstinv datelastinv date, --mm-dd-yyyy numportcos integer, numrounds integer, investedk money, areacode integer, bocity addr1 varchar(100), bonation addr2 varchar(100), bonationcode location varchar(100), bostatecode city varchar(100), boaddr1 zip varchar(10010), boaddr2 areacode integer, county varchar(100), bozip state varchar(102), capitalundermgmt money, city nationcode varchar(10010), county nation varchar(100), invstatus worldregion varchar(100), msacode varchar(10) numportcos integer, geogpref varchar(100) numrounds integer, indpref varchar(100) investedk money, rolepref varchar(100) capitalundermgmt money, stagepref invstatus varchar(100), statecode msacode varchar(210), addr1 rolepref varchar(100), addr2 geogpref varchar(100), type indpref varchar(100), worldregion stagepref varchar(100), zip type varchar(10100)
);
\COPY firmbase FROM 'VCFirms1980USVCFirms1980-presentPresent-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV --9734 ===Branch Offices===Core Files USVCFirmBranchOffices1980-Present.rpt USVCFirmBranchOffices1980-Present.ssh USVCFirmBranchOffices1980-Present.txt USVCFirmBranchOffices1980-Present-normal.txt Created: 9/29/2017 3:54:32 PM Session Contents: DATABASE: Private Equity Firms (VIFM) Venture Related Deals: Select All Venture Related Deals Firm Portfolio Cos: Round Date: 01/01/1980 to 09/29/2017 (Custom) (Calendar) Firm Portfolio Cos: Nation : US Custom Report Contents: FIRM_NAME VI_CAPITAL_CITY VI_CAPITAL_STATE VI_CAPITAL_COUNTRY VI_CAPITAL_NATION VI_POSTAL_CODE VI_ADR_LINE1 VI_ADR_LINE2
firmname varchar(255),
bocity varchar(100),
bostate varchar(2),
bocountrycode varchar(2),
bonation varchar(100),
boaddr1 varchar(100),
boaddr2 varchar(100), bozip varchar(10), capitalundermgmt money, city varchar(100), county varchar(100), invstatus varchar(100), msacode varchar(10), geogpref varchar(100), indpref varchar(100), rolepref varchar(100), stagepref varchar(100), statecode varchar(2), addr1 varchar(100), addr2 varchar(100), type varchar(100), worldregion varchar(100), zip varchar(10)
);
==Company Long Description==
