Best way to import data into DB from a text file? 2

[trc]

I am having difficulties finding a good way to import data from a text file into a db. I have tried several ways but no success. What is a good way to do it?

I have tried the following code from the thread "Information on how to look at a text file" on Jun 11, 2001. Could not figure out how to move to the next line.

dim temp as string
dim tempArray() as string

open myText.txt for read as 1
Line Input #1, temp 'temp is used to store the line
temparray = split(temp,",&quot 'This line will split the raw line up based on the comma
'Do whatever else that you need to do with tokens....


Also tried:

Open strImportFile For Input As #1 ' Open File for Input
Input #1, strTemp 'IP_ID, IP_BName, IP_BLoc, IP_Name, IP_PolNum, IP_EffDate, IP_ExpDate, IP_Class, IP_PDF, IP_PolVer, IP_PDFPath, strTemp.........

But again, how do I move to the next line for input?

Thanks in advance. ************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[dsb]

Your ideas are ok
Read a line (assume this contains a record)
Parse the line into individual fields
Stuff the fields into a recordset record
update the database....

It sounds like you are having a problem with step 2?
what is the format for the text file?

 

[Hungoverhippie]

dim temp as string
dim tempArray() as string

open &quot;c:\mydir\myText.txt&quot; for input as #1
do while eof(1) = false
Line Input #1, temp
temparray = split(temp,&quot;,&quot
loop


Hope this helps!!!
Hungoverhippie

 

[trc]

Thanks you both. The hung over one had it. I tried that but was apparently asleep at the wheel and did not set it up quite right. I was trying to find a slick way to add the information to the rs but this is what I wound up with.

Here is the code that I used:


' Open File for Input
Open strImportFile For Input As #1

Do While EOF(1) = False
Line Input #1, strTemp
tempArray = Split(strTemp, &quot;;&quot

With rs
.AddNew
!B_name = tempArray(1)
!B_loc = tempArray(2)
!Name = tempArray(3)
!Pol_Number = tempArray(4)
!Effective_Date = tempArray(5)
!Expiry_Date = tempArray(6)
!Class = tempArray(7)
!pdf_name = tempArray(8)
!Pol_Version = tempArray(9)
!pdf_path = tempArray(10)
.Update
End With

'call sub to copy files. Had to use 'call' for some reason
Call subCopyFile(tempArray(8), tempArray(10))

Loop ************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[dsb]

you should give hungover a START (right under his name at bottom of his note)

 

[trc]

Good point. Never thought of it. Sorry. ************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->