• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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