New topic: Read file in from .csv then insert into database
<http://forums.realsoftware.com/viewtopic.php?t=34197> Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message McDian Post subject: Read file in from .csv then insert into databasePosted: Wed Jun 09, 2010 2:41 pm Joined: Fri Sep 14, 2007 5:00 pm Posts: 276 Okay, I have done this many times and had no issues. Now I am working on another project and can't get it to work? I can't see any obvious reasons for it not working. Anyhow here is the code breakdown. Module Name: dbConn Properties: Declaration: dbMeters As REALSQLDatabase AppName: dbUcscMeters dbUcscMeters.Open Code: ' *************************************************************************** // Preliminary code... ' -- : NONE DEFINED : -- // Variable declarations... Dim dbFldrItm As FolderItem // Function(s)... ' Instantiate a new instance dbMeters = New REALSQLDatabase // Define the location and load the variable dbFldrItm = GetFolderItem("DBF").Child("dbMMDB.rsd") // Test to ensure the folder and database actually exist. If(dbFldrItm) <> Nil Then If(dbFldrItm.Exists) Then dbMeters.DatabaseFile = dbFldrItm Else MsgBox "Database isn't there." + EndOfLine + Str(dbMeters.ErrorCode) + EndOfLine + dbMeters.ErrorMessage Return End If Else MsgBox "The folder containing the database isn't there!" + EndOfLine + "Error is located in: dbMeters.Open" Return End If WindowName: frmLoadData PushButton.Name = btnLoadMeters frmLoadData.btnLoad Code: ' *************************************************************************** // Preliminary code... ' -- : NONE DEFINED : -- // Variable declarations... // Function(s)... mthLoadMeters Method Name: mthLoadMeters Parameters: Return Type: Scope: Private Code: ' *************************************************************************** // Preliminary code... ' -- : NONE DEFINED : -- // Variable declarations... Dim fldItm As FolderItem Dim dbRec As New DatabaseRecord // Function(s)... fldItm = GetFolderItem("DBF").Child("Load Source Tables").Child("tblMeters.csv") If(dbMeters.Connect) = True Then If(fldItm) <> Nil Then Dim txtStrm As TextInputStream txtStrm = fldItm.OpenAsTextFile ' Open the csv file... If(txtStrm) <> Nil Then ' Read the file contents into the stream... txtStrm.Encoding = Encodings.UTF8 ' Set encoding... Dim csvStr As String csvStr = txtStrm.ReadAll ' does a complete read in without checking for EOF... txtStrm.Close ' Format data now... csvStr = Trim(csvStr) ' Remove leading and trailing white space... csvStr = ReplaceLineEndings(csvStr, EndOfLine) ' Replace line endings... ' Split datat into individual lines... Dim stLines() As String = Split(csvStr, EndOfLine) ' Loop through the newly created array... For intA As Integer = 0 To UBound(stLines) Dim stFieldDef() As String = Split(stLines(intA), ",") ' Break on comma's For intB As Integer = 0 To UBound(stFieldDef) ' Remove any double quotes... stFieldDef(intB) = ReplaceAll(stFieldDef(intB), """","") Next dbRec.Column("recId") = stFieldDef(0) dbRec.Column("recNum") = stFieldDef(1) dbRec.Column("metStatus") = stFieldDef(2) dbRec.Column("metId") = stFieldDef(3) dbRec.Column("metName") = stFieldDef(4) dbRec.Column("metSerNum") = stFieldDef(5) dbRec.Column("regSerNum") = stFieldDef(6) dbRec.Column("mxuSerNum") = stFieldDef(7) dbRec.Column("metType") = stFieldDef(8) dbRec.Column("metMfg") = stFieldDef(9) dbRec.Column("metModel") = stFieldDef(10) dbRec.Column("readType") = stFieldDef(11) dbRec.Column("metMulti") = stFieldDef(12) dbRec.Column("metRoll") = stFieldDef(13) dbRec.Column("vendCode") = stFieldDef(14) dbRec.Column("vendName") = stFieldDef(15) dbRec.Column("bldgCaan") = stFieldDef(16) dbRec.Column("bldgName") = stFieldDef(17) dbRec.Column("xfrmrCode") = stFieldDef(18) dbRec.Column("engyType") = stFieldDef(19) dbRec.Column("uOm") = stFieldDef(20) dbRec.Column("billRate") = stFieldDef(21) dbRec.Column("metLoc") = stFieldDef(22) dbRec.Column("metAcc") = stFieldDef(23) dbRec.Column("comments") = stFieldDef(24) dbRec.Column("dateInstall") = stFieldDef(25) dbRec.Column("dateCal") = stFieldDef(26) dbRec.Column("pipeSizeIn") = stFieldDef(27) dbRec.Column("pipeSizeOut") = stFieldDef(28) dbRec.Column("metVal") = stFieldDef(29) dbRec.Column("valDate") = stFieldDef(30) dbRec.Column("elecSpecs") = stFieldDef(31) dbRec.Column("srchName") = stFieldDef(32) dbRec.Column("srchMetType") = stFieldDef(33) dbRec.Column("srchMetLoc") = stFieldDef(34) dbRec.Column("srchMetGrp") = stFieldDef(35) dbRec.Column("msgLbl") = stFieldDef(36) dbMeters.InsertRecord "tblRealMeters", dbRec 'MsgBox stFieldDef(3) + " " + stFieldDef(4) If(dbMeters.Error) Then Beep dbMeters.Rollback MsgBox "There was an error loading tblRealMeters" + EndOfLine.Windows + dbMeters.ErrorMessage + EndOfLine.Windows + Str(dbMeters.ErrorCode) Exit Sub Else dbMeters.Commit End If Next Else MsgBox "Is the file named correctly and in the correct location?" End If Else MsgBox "Check to see if the folder is where it should be." End If Else If(dbMeters.Error) Then // Testing for issues in the DB tissues! Beep MsgBox "Database Error: " + Str(dbMeters.ErrorCode) + EndOfLine + EndOfLine + dbMeters.ErrorMessage Exit Sub End If MsgBox "I do not believe it is connected! " + Str(dbMeters.ErrorCode) + EndOfLine + EndOfLine + dbMeters.ErrorMessage End If The data file is loading as I have checked and there is data in the TextInputStream. But when it gets to InsertRecord it errors out with a code 0. I have used this same code (with applicable tweaks) in 3 other projects and had no problem. Now it won't load and I am stumped as to why?! Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 1 post ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
