473,326 Members | 2,095 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Best way to import data from txt file using VB

365 100+
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
or

StaffID,Date1
eg
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
5 3648
NeoPa
32,556 Expert Mod 16PB
  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
Dan2kx
365 100+
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
  12.  
Expand|Select|Wrap|Line Numbers
  1. Public Function FolderFromPath(strFullPath As String) As String
  2.     FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
  3. End Function
  4.  
Dan
Jan 28 '09 #3
NeoPa
32,556 Expert Mod 16PB
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
Dan2kx
365 100+
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
Jan 28 '09 #5
NeoPa
32,556 Expert Mod 16PB
I did wonder ;)

That makes perfect sense.
Jan 28 '09 #6

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

Similar topics

5
by: Daniel Pryde | last post by:
Hi everyone. I was wondering if anyone might be able to help me out here. I'm currently looking to find the quickest way to find a best fit match in a large array. My problem is that I have an...
5
by: yvan | last post by:
Approximately once a month, a client of ours sends us a bunch of comma-delimited text files which I have to clean up and then import into their MS SQL database. All last week, I was using a Cold...
4
by: Bruce W. Roeser | last post by:
All, I'm reading a book by Charles Petzold (Programming VS.Net). Pretty good content but am confused about the difference. From the text: ...
4
by: BostonNole | last post by:
I am looking for suggestions on the most efficient way to import 7 different fixed width files into a DataSet. Not all at the same time. One file at a time, but the format could change from file...
5
by: Ryan Liu | last post by:
I have an application need export ane import data of projects. There are about 10 database tables releated to one project. 3 of them each could have up to 100K lines data. I can export all data...
3
by: ninrulz | last post by:
I will try to explain my situation. I know that it is hard to offers solutions without fully understanding what people would like to achieve. I receive 2 csv files every month. The csv files...
4
by: Max2006 | last post by:
Hi, We are developing a SQL server based asp.net application. As part of requirement we should allow users import/export some relational data through web user interface. We are investigation...
6
by: kamsmartx | last post by:
I'm new to programming and need some help figuring out an algorithm. I need to design some kind of algorithm which will help us define capacity for one of our portfolios....here's the problem...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.