Fixed File Import

Is there a way to import a fixed width text file into a access database table?
jrcpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anthonycCommented:
yes...  You can do it with access, but if you do not want to require that access is on the machine, you can do this:

Let's assume you have 3 fields in your text file.  A name (30 chars), birthdate (10 char) and a score (3 char)  you can do this  (all off the cuff, forgive me if I make a typo or something)

type PlayerRecord
  szName  as string * 30
  szDate  as string * 10
  szScore as string * 3
end type
private const mc_lRecLen = 43

private sub ReadAndInsert(szFileName as string, rsTable as recordset)
  dim nFile     as integer
  dim prRec     as PlayerRecord
  dim lRecCt    as Long
  dim lI        as long

  nFile = freefile
  Open szFileName for Input as #nFile
 
  lrecct = lof(nfile) \ mc_lRecLen

  for lI = 1 to lRecCt
    get #nFile, lI, prRec

    rsTable.Addnew
    rsTable!PlayerName = prRec.szName
    rsTable!BirthDate = cdate(prRec.szDate)
    rsTable!Score = val(prRec.szScore)
    rsTable.Update
  next ni

  close #nfile 'we read the entire file
end sub
0
anthonycCommented:
oops that was an answer!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.