http://stackoverflow.com/questions/10757631/conversion-from-string-unitid-instnm-addr-city-stab-to-type-integer-is-n

gistfile1.vb

Dim WFSDINSTITUTION As String =""

Dim WFSDSTRING As New System.Data.SqlClient.SqlConnection

WFSDSTRING.ConnectionString ="Data Source=LED-SQL;Initial Catalog=WorkforceSD_DEV;Integrated Security=True"

WFSDSTRING.Open()


Dim Institution As String ="Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\CSchexnaydre\Desktop\TEST SHEETS\Institution\INST_TEST2010.csv;HDR=YES;"

Using INSTITUTIONCONNECTION As New System.Data.OleDb.OleDbConnection(Institution)

Dim INST_TABLE As New Data.DataTable()

INST_TABLE.Columns.Add("UNITID")

INST_TABLE.Columns.Add("INSTNM")

INST_TABLE.Columns.Add("ADDR")

INST_TABLE.Columns.Add("CITY")

INST_TABLE.Columns.Add("STABBR")

INST_TABLE.Columns.Add("ZIP")

INST_TABLE.Columns.Add("FIPS")

INST_TABLE.Columns.Add("OBEREG")

INST_TABLE.Columns.Add("CHFNM")

INST_TABLE.Columns.Add("CHFTITLE")

INST_TABLE.Columns.Add("GENTELE")

INST_TABLE.Columns.Add("ENI")

INST_TABLE.Columns.Add("OPEID")

INST_TABLE.Columns.Add("OPEFLAG")

INST_TABLE.Columns.Add("WEBADDR")

INST_TABLE.Columns.Add("ADMINURL")

INST_TABLE.Columns.Add("FAIDURL")

INST_TABLE.Columns.Add("APPLURL")

INST_TABLE.Columns.Add("ICLEVEL")

INST_TABLE.Columns.Add("CONTROL")

INST_TABLE.Columns.Add("HLOFFER")

INST_TABLE.Columns.Add("UGOFFER")

INST_TABLE.Columns.Add("GROFFER")

INST_TABLE.Columns.Add("HDEGOFR1")

INST_TABLE.Columns.Add("DEGGRANT")

INST_TABLE.Columns.Add("HBCU")

INST_TABLE.Columns.Add("HOSPITAL")

INST_TABLE.Columns.Add("MEDICAL")

INST_TABLE.Columns.Add("TRIBAL")

INST_TABLE.Columns.Add("LOCALE")

INST_TABLE.Columns.Add("OPENPUBL")

INST_TABLE.Columns.Add("ACT")

INST_TABLE.Columns.Add("NEWID")

INST_TABLE.Columns.Add("DEATHYR")

INST_TABLE.Columns.Add("CLOSEDAT")

INST_TABLE.Columns.Add("CYACTIVE")

INST_TABLE.Columns.Add("POSTSEC")

INST_TABLE.Columns.Add("PSEFLAG")

INST_TABLE.Columns.Add("PSET4FLG")

INST_TABLE.Columns.Add("RPTMTH")

INST_TABLE.Columns.Add("IALIAS")

INST_TABLE.Columns.Add("INSTCAT")

INST_TABLE.Columns.Add("CCBASIC")

INST_TABLE.Columns.Add("CCIPUG")

INST_TABLE.Columns.Add("CCIPGRAD")

INST_TABLE.Columns.Add("CCUGPROF")

INST_TABLE.Columns.Add("CCENRPRF")

INST_TABLE.Columns.Add("CCSIZSET")

INST_TABLE.Columns.Add("CARNEGIE")

INST_TABLE.Columns.Add("TENURSYS")

INST_TABLE.Columns.Add("LANDGRNT")

INST_TABLE.Columns.Add("INSTSIZE")

INST_TABLE.Columns.Add("CBSA")

INST_TABLE.Columns.Add("CBSATYPE")

INST_TABLE.Columns.Add("CSA")

INST_TABLE.Columns.Add("NECTA")

INST_TABLE.Columns.Add("F1SYSTYP")

INST_TABLE.Columns.Add("F1SYSNAM")

INST_TABLE.Columns.Add("FAXTELE")

Dim INST_TEST2010 As New FileIO.TextFieldParser("C:\Documents and Settings\CSchexnaydre\Desktop\TEST SHEETS\Institution\INST_TEST2010.csv")
INST_TEST2010.Delimiters =New String() {","}
INST_TEST2010.HasFieldsEnclosedInQuotes =True

INST_TEST2010.TrimWhiteSpace =True

INST_TEST2010.ReadLine()

Do Until INST_TEST2010.EndOfData = True

INST_TABLE.Rows.Add(INST_TEST2010.ReadFields("UNITID, INSTNM, ADDR, CITY, STABBR, ZIP, FIPS, OBEREG, CHFNM, CHFTITLE, GENTELE, EIN, OPEID, OPEFLAG, WEBADDR, ADMINURL, FAIDURL, APPLURL, ICLEVEL, CONTROL, HLOFFER, UGOFFER, GROFFER, HDEGOFR1, DEGGRANT, HBCU, HOSPITAL, MEDICAL, TRIBAL, LOCALE, OPENPUBL, ACT, NEWID, DEATHYR, CLOSEDAT, CYACTIVE, POSTSEC, PSEFLAG, PSET4FLG, RPTMTH, IALIAS, INSTCAT, CCBASIC, CCIPUG, CCIPGRAD, CCUGPROF, CCENRPRF, CCSIZSET, CARNEGIE, TENURSYS, LANDGRNT, INSTSIZE, CBSA, CBSATYPE, CSA, NECTA, F1SYSTYP, F1SYSNAM, FAXTELE"))

Loop


Dim i As Integer

Dim INSERT_INST_COMMAND = New System.Data.SqlClient.SqlCommand

INSERT_INST_COMMAND = WFSDSTRING.CreateCommand()

For Each row In INST_TABLE.Rows

INSERT_INST_COMMAND.CommandText ="INSERT INTO Institution_UPLOAD (Institution_ID, Institution_Name, Address_1, City, State_Code, Zip, FIPS, OBE_Region_ID, Chief_Admin_Name_ID, Chief_Admin_Title_ID, Institution_Phone, ENI, OPE_Number, OPEFLAG_ID, Institution_Web, Admission_Web, Financial_Web, Application_Web, Type_Of_Institution_ID, Level_Of_Institution_ID, HLOFFER_ID, UGOFFER_ID, GROFFER_ID, HDEGOFR1_ID, DEGGRANT_ID, Historic_Black_College_ID, Hospital_Available_ID, Medical_Degree_ID, Tribal_College_ID, Location_ID, Open_To_Public, ACT_ID, New_ID, Deleted_Year, Closed_Date, CYACTIVE_ID, POSTEC_ID, PSEFLAG_ID, PSET4FLG_ID, RPTMTH_ID, INSTCAT_ID, ALIAS, CCBASIC_ID, CCIPUG_ID, CCIPGRAD_ID, CCUGPROF_ID, CCENPRF_ID, CCSIZSET_ID, CARNEGIE_ID, TENURSYS_ID, Land_Grant_Institution_ID, Size_Of_Institution_ID, CBSA_ID, CBSATYPE_ID, CSA_ID, NECTA_ID, F1SYSTYP_ID, F1SYSNAM, Institution_Fax) VALUES ("_

& "'" & row("UNITID") & "'," _

& "'" & row("INSTNM") & "'," _

& "'" & row("ADDR") & "'," _

& "'" & row("CITY") & "'," _

& "'" & row("STABBR") & "'," _

& "'" & row("ZIP") & "'," _

& "'" & row("FIPS") & "'," _ 

& "'" & row("OBEREG") & "'," _

& "'" & row("CHFNM") & "'," _

& "'" & row("CHFTITLE") & "'," _

& "'" & row("GENTELE") & "'," _

& "'" & row("ENI") & "'," _

& "'" & row("OPEID") & "'," _

& "'" & row("OPEFLAG") & "'," _

& "'" & row("WEBADDR") & "'," _

& "'" & row("ADMINURL") & "'," _

& "'" & row("FAIDURL") & "'," _

& "'" & row("APPLURL") & "'," _

& "'" & row("ICLEVEL") & "'," _

& "'" & row("CONTROL") & "'," _

& "'" & row("HLOFFER") & "'," _

& "'" & row("UGOFFER") & "'," _

& "'" & row("GROFFER") & "'," _

& "'" & row("HDEGOFR1") & "'," _

& "'" & row("DEGGRANT") & "'," _

& "'" & row("HBCU") & "'," _

& "'" & row("HOSPITAL") & "'," _

& "'" & row("MEDICAL") & "'," _

& "'" & row("TRIBAL") & "'," _

& "'" & row("LOCALE") & "'," _

& "'" & row("OPENPUBL") & "'," _

& "'" & row("ACT") & "'," _

& "'" & row("NEWID") & "'," _

& "'" & row("DEATHYR") & "'," _

& "'" & row("CLOSEDAT") & "'," _

& "'" & row("CYACTIVE") & "'," _

& "'" & row("POSTSEC") & "'," _

& "'" & row("PSEFLAG") & "'," _

& "'" & row("PSET4FLG") & "'," _

& "'" & row("RPTMTH") & "'," _

& "'" & row("IALIAS") & "'," _

& "'" & row("INSTCAT") & "'," _

& "'" & row("CCBASIC") & "'," _

& "'" & row("CCIPUG") & "'," _

& "'" & row("CCIPGRAD") & "'," _

& "'" & row("CCUGPROF") & "'," _

& "'" & row("CCENRPRF") & "'," _

& "'" & row("CCSIZSET") & "'," _

& "'" & row("CARNEGIE") & "'," _

& "'" & row("TENURSYS") & "'," _

& "'" & row("LANDGRNT") & "'," _

& "'" & row("INSTSIZE") & "'," _

& "'" & row("CBSA") & "'," _

& "'" & row("CBSATYPE") & "'," _

& "'" & row("CSA") & "'," _

& "'" & row("NECTA") & "'," _

& "'" & row("F1SYSTYP") & "'," _

& "'" & row("F1SYSNAM") & "'," _

& "'" & row("FAXTELE") & "')"

i = INSERT_INST_COMMAND.ExecuteNonQuery()


Next

End Using

End