Best way to import data from txt file using VB

[Dan2kx]

Good evening folks,

i have yet another question for the experts, i need to be able to import a list of dates and ID numbers from a text file i suspect i need to format my text file as such

StaffID,Date1,D ate2,Date3,Date 4 etc
eg data

1,21/10/2009,22/10/2009,23/10/2009
2,01/05/2009
3,09/08/2009,10/08/2009

or

StaffID,Date1
eg

1,21/10/2009
1,22/10/2009
1,23/10/2009
2,01/05/2009
3,09/08/2009
3,10/08/2009

what i am not sure about is:
1) Which is this the best way to format this data?
2) how would i extract the variable number of dates (not constant per StaffID) for 1st eg and keep it in vb because i will need to some additional data before inserting into table
3) how would i open a prompt to locate the txt fileand then continue the code, can this be done? (not essential)

any help would be much appreciated

Thanks again, Dan

[NeoPa]

1. The latter would generally be the better of the two.
2. Forget this.
3. Typically, you would use a form for this.

[Dan2kx]

Ok thanks for the advice, here is the code im going to use if anyone is interested:

Function ImportTextFile()
Dim LineData As String
Dim SID As Long, sdt As Date
Open FolderFromPath(CurrentDb.Name) & "ShiftInfo.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, LineData
    SID = Left(LineData, InStr(LineData, ","))
    sdt = Right(LineData, Len(LineData) - InStrRev(LineData, ","))
Loop
Close #1
End Function

Public Function FolderFromPath(strFullPath As String) As String
    FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
End Function

Dan

[NeoPa]

No worries Dan :)

BTW Do you realise your code simply dumps the values once loaded? SID & sdt are not referred to between record loads.

[Dan2kx]

Yeah i need to build in some other routines to construct the rest of my data in SQL format, havent done that yet, just for illustrative purposes i guess.

Dan

[NeoPa]

I did wonder ;)

That makes perfect sense.