Transfer Data from the Flat File to the Database

[nicky]

How do you write a program to transfer the data from the flat file (inventory.dat) to the database (inventory.mdb)? Thank you...

[Aaron Young]

The Flat File would have to be Formatted so that you could distinguish between Fields, either by knowing the Length of each Field or by having a Delimiter Character Seperating each Field.

You would then read the File in a Line/Record at a Time, seperate the Fields and Store each in its Appropriate Database Field, repeating the Process until the End of the Flat File is Reached.

If the Order of the fields in both the Flat File and the DB match it's even easier, eg.

Something like..

Dim aFields As Variant
Dim sRecord As String
Dim iField As Integer
Dim oDB As Database
Dim oRS As Recordset

Set oDB = WorkSpaces(0).OpenDatabase("C:\MyDB.mdb")
Set oRS = oDB.OpenRecordSet("SELECT * FROM MyTable", dbOpenDynaset)
Open "C:\FlatFile.dat" For Input As 1
While Not EOF(1)
    LineInput #1, sRecord
    'Assuming a Comma Delimiter
    aFields = Split(sRecord, ",")
    oRs.AddNew
    For iField = 0 To Ubound(aFields)
        oRs(iField) = aFields(iField)
    Next
    oRs.Update
Wend

Of course you'd have different Data Types and you'd need to convert them before assigning them to the Fields, but you get the Idea.

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

[nicky]

Aaron, thank you very much...

[Ruchi]

For VB6 using split function

For VB5

aFields = Split(sRecord, ",")
=====================
If InStr(sRecord, ",") > 0 Then
aFields(i) = Trim(Left(sRecord, InStr(sRecord, ",") - 1))
sRecord = Mid(sRecord, InStr(sRecord, ",") + 1)
Else
aFields(i) = Trim(sRecord)
sRecord = ""
End If

I think this should work.

Ruchi

[Steve Thomas]

The code errors out because the value I am trying to assign to the aField is a string(for instance 01) and this does not seem to be a valid variant data type. The other question I have is do you know if the split function will grab a line of data and if there are 13 commas, will it return the value of 13. I ask this because I believe the previous code will only split two variables with 1 comma. I think I can write code to deal with the commas but I would preffer something as simple as the split function.

[Ruchi]

The code I wrote gave you an idea for the split function. I haven't tried to test it myself. Any ideas for Steve Thomas?


Ruchi

[Aaron Young]

Heres a Split function I wrote a while ago:

Public Function SplitString(ByVal sString As String, ByVal sSeperator As String) As Variant
    Dim sStrings() As String
    Dim iStrings As Integer
    Dim iPos As Integer
    
    If sString = "" Then Exit Function
    iPos = 1
    Do
        ReDim Preserve sStrings(iStrings)
        iPos = InStr(sString, sSeperator)
        sStrings(iStrings) = sString
        If iPos Then sStrings(iStrings) = Left(sString, iPos)
        sString = IIf(iPos, Mid(sString, iPos + 1), "")
        iStrings = iStrings + 1
    Loop While Len(sString) > 0
    
    SplitString = sStrings
End Function

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

[Steve Thomas]

I am running VB5 and the split function is not recognized. Any suggestions?