Importing unformatted text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to build a database that will import a .txt file from a main
frame. A sample of the file is below which shows records for 3 unique IDs
(the numbers starting with N).

The problem I have is that the second row does not have the unique ID for
that row which is on the row above. What I would like to do is add this ID
to the second row and so on for each record.

I know how to do this in Excel but there are hunderds of thousands of
records so Excel is not ideal. I think I need to use a do loop function and
and append query but I am not sure how to do this programatically. Can
anyone help me?

Thank you,

Martin


ID CALLS NOTIFIED ACKNOW ARRIVED
CLEARED
N092631 1 0:00 0:00
0:00
0:00
0:00 0:00
N114631 1 0:00 0:01
0:03
0:19
0:20 0:23
N190631 1 0:00
0:03 0:05
0:41
0:44 0:49
 
Hi Martin,

you will need to open the text file and loop through it

here is some "starter code" for you

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Function ImportFile( _
pFilename As String _
, pTablename as string _
) as Long

'written by Crystal
'strive4peace2007 at yahoo dot com

'NEEDS reference to Microsoft DAO Library if you open recordset

'BASIC USEAGE
' assign this to a command button event
' =ImportFile("c:\path\filename.csv", "Tablename")

'RETURN VALUE is number of records read

ImportFile = 0

Dim mFileNumber _
, mLine As String _
, i As Long

'you can open a recordset to put values into
'Dim r As dao.Recordset
'Set r = CurrentDb.OpenRecordset(pTablename, dbOpenDynaset)

'alternately, you could use APPEND queries...

mFileNumber = FreeFile
Open mFilename For Input As #mFileNumber

i = 0
Do While Not EOF(mFileNumber)

'i is the line number you are on
i = i + 1

ImportFile = i

Input #mFileNumber, mLine

'remove this line after you get your parsing stuff working right
If MsgBox(mLine, vbOKCancel, "Line " & i) = vbCancel Then Stop

'put your parsing and assignment statements here

Loop

Proc_Exit:
On Error Resume next
Close #mFileNumber
'r.close
'set r = nothing
exit function

'ERROR HANDLER
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " ImportFile: " & pFilename

'comment next statement after debugging
'press F8 to step through code and correct problem
Stop: Resume

Resume Proc_Exit

End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~

to learn more about the INPUT statement (and related statements),
position your mouse pointer over the word in a module sheet and press F1
for context-sensitive help

****************



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top