Importing Data

[Granty]

Hey folks,

I'm just starting my first VB project after working in MSAccess. As you can imagine, Im missing my docmd's

Basically, I was wondering if there is an import command/method/function (kinda like access's TransferText function), or do I have to write my data line by line?

Cheers all

[plenderj]

Well what are you importing the data from ?
And then how do you want the data stored once its imported ?

[Granty]

Well, this one is an import from a .csv file into an MSAccess table.

[plenderj]

Okay,
Well to read in data from a file in VB, and then separate values separated by commas into an array you would do this :

Dim varString As String
Dim varStrArray() As String
Open "c:\something.csv" For Input As #1
    Do Until Eof(1)
        DoEvents
        Line Input #1, varString
        varStrArray = Split(varString, ",")        
    Loop
Close #1

Each iteration through the loop it will read in a new line from the file into the varString variable.
Then it splits the data into an array called varStrArray.
So you would then do something with the data in varStrArray. Perhaps something like this :

Dim varString As String
Dim varStrArray() As String
Dim i As Long
Open "c:\something.csv" For Input As #1
    Do Until Eof(1)
        DoEvents
        Line Input #1, varString
        varStrArray = Split(varString, ",")        
        For i = 0 To UBound(varStrArray)
            Select Case i
                Case 0:
                    RS.Fields("Something 0").Value = varStrArray(i)
                Case 1:
                    RS.Fields("Something 1").Value = varStrArray(i)
                Case 2:
                    RS.Fields("Something 2").Value = varStrArray(i)
            End Select                        
        Next I
    Loop
Close #1

The above RS and fields bit would be referring to a recordset using DAO.
I dont like database stuff much so Im not too hot on it ...
but most of the above code should do.

[Granty]

Thanx man, that was the way I guessed I was gonna have to do it.

Just wondered if there was a nice easy 1-liner like access..... =)

Cheers for your help!

[plenderj]

Well there is :

Just call the JustDoIt() function.






Public Sub JustDoIt()
Dim varString As String
Dim varStrArray() As String
Dim i As Long
Open "c:\something.csv" For Input As #1
    Do Until Eof(1)
        DoEvents
        Line Input #1, varString
        varStrArray = Split(varString, ",")        
        For i = 0 To UBound(varStrArray)
            Select Case i
                Case 0:
                    RS.Fields("Something 0").Value = varStrArray(i)
                Case 1:
                    RS.Fields("Something 1").Value = varStrArray(i)
                Case 2:
                    RS.Fields("Something 2").Value = varStrArray(i)
            End Select                        
        Next I
    Loop
Close #1
End Sub

[AWF]

You wanted a nice one liner, Well here is one.


appAccess.OpenCurrentDatabase databasename 'Open DB

'Import file using table specification for setting
appAccess.DoCmd.TransferText acImportDelim, "Import Specification", _
"Tablename ", FilePathToOpen, True
appAccess.CloseCurrentDatabase
appAccess.Quit

Hope you find this OK

[Granty]

Heheh, that's kinda cheating, but I like it!