By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Best way to import data from txt file using VB

P: 365
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,Date2,Date3,Date4 etc
eg data
Expand|Select|Wrap|Line Numbers
  1. 1,21/10/2009,22/10/2009,23/10/2009
  2. 2,01/05/2009
  3. 3,09/08/2009,10/08/2009

Expand|Select|Wrap|Line Numbers
  1. 1,21/10/2009
  2. 1,22/10/2009
  3. 1,23/10/2009
  4. 2,01/05/2009
  5. 3,09/08/2009
  6. 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
Jan 28 '09 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,409
  1. The latter would generally be the better of the two.
  2. Forget this.
  3. Typically, you would use a form for this.
Jan 28 '09 #2

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

Expand|Select|Wrap|Line Numbers
  1. Function ImportTextFile()
  2. Dim LineData As String
  3. Dim SID As Long, sdt As Date
  4. Open FolderFromPath(CurrentDb.Name) & "ShiftInfo.txt" For Input As #1
  5. Do While Not EOF(1)
  6. Line Input #1, LineData
  7.     SID = Left(LineData, InStr(LineData, ","))
  8.     sdt = Right(LineData, Len(LineData) - InStrRev(LineData, ","))
  9. Loop
  10. Close #1
  11. End Function
Expand|Select|Wrap|Line Numbers
  1. Public Function FolderFromPath(strFullPath As String) As String
  2.     FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
  3. End Function
Jan 28 '09 #3

Expert Mod 15k+
P: 31,409
No worries Dan :)

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

P: 365
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.

Jan 28 '09 #5

Expert Mod 15k+
P: 31,409
I did wonder ;)

That makes perfect sense.
Jan 28 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.