• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Importing a txt file into MS Access

If I use the built-in function of MS Access, is it true that it samples the first 10 rows to calculate the number of columns.

The problem that I am having is, the first 10 or so rows has data in 10 columns/fields, but my imported file should have 12 columns/fields, so there is data missing, which I use the run a certain query.

What would be the best way forward? Also this file will change over time, so would vba code to read data from a text file be the better option?

0
irishbil
Asked:
irishbil
  • 4
  • 3
1 Solution
 
Kelvin SparksCommented:
I understand it's about this first 25 rows.

What sort of text file (CSV, Tabs etc). Most files would still have null values for those fields.

You can force it with an import specification to say what columns to import.

You can also have a table to import to that has the correct columns there.


Kelvin
0
 
irishbilAuthor Commented:
Hi Kelvin, Thanks for you quick reply. The file is a CSV file...

Your last comment seems the best for what i am looking for. So if I created the table first, how would i get the text file to import into it. Maybe I am missing something here!
0
 
Kelvin SparksCommented:
The best is to import to a table and then create an import specification to match the data.

The Transfer text command will do this, just give the table name in the appropriate space in the code. To create the simport specification, manually import the data = (if Access 2003, use File>Import and when you see the first preview of the data click the Advanced button at the bottom left. Once you have told it what to do (and it'll take a bit to master) save it, and paste the name you saved the specification as into the correct part of the transfertext command.

Once you have this sorted it should stay stable and reliable.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
irishbilAuthor Commented:
I have never used the transfertext command before, but i understand it looks for the saved import spec.

Just to clarify, in the Import Specification, under Field information, can i just add in the extra fields names and the extra columns will be added. Sorry I can't fully test it at the minute.
0
 
Kelvin SparksCommented:
Yes you can.

I'm away now (in New Zealand and after midnight).

Will look again tomorrow
0
 
irishbilAuthor Commented:
Its easy when you know how! Thanks, Liam
0
 
jonlakeCommented:
You first need to import the file, customising the field types etc., then save the specification. This is accessed using the Advanced button during the import process. Here is a simple example of some thing I use regularly and it works like a charm. The routine first deletes all records from the target table, then imports the target file, then runs a query on the new data :

Private Sub ImportData_Click()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryP11D_Stage01_Delete"
    DoCmd.TransferText acImportDelim, "P11D_CONS Import Specification", "tblP11D_CONS", [TargetFile], True
    DoCmd.OpenQuery "qryP11D_Stage04"
    DoCmd.SetWarnings True
    DoCmd.Close

End Sub

Rather than have the file location hard coded into the routine I have a form with the field TargetFile which is populated using the following routine:

Private Sub ImportDir_Click()
On Error GoTo Err_ImportDir_Click
    Dim strFilter As String
    Dim lngFlags As Long
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    Me.TargetFile = ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
        DialogTitle:="Select File to Import")
   
Exit_ImportDir_Click:
    Exit Sub

Err_ImportDir_Click:
    MsgBox "You have not specified a file"
    Resume Exit_ImportDir_Click
End Sub

Good luck, if you need a sample db let me know.
0
 
Kelvin SparksCommented:
Thanks

It is only easy, when we have previously struggled our way through these things as you have. Believe me, one of the biggest challenges a developer can have is to build a robust import tool that handles every variance that the non technical world can throw at us.

Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now