Import/Export Text Files with Specification Names

[kt1978]

Hi all

I think I have found a solution now but I want to still ask a couple of questions regarding this as I've spent hours on it. and want to understand a bit more. Sorry, its a bit lengthy... (My solution is right at the bottom)

Basically, I want to import text files into an Access table and also export data from a table to a text file.

I first used the transferText method, which does the job fine when using the spec name specified in the advanced options when importing the table for the first time manually.

Here is the code... Which works fine!

Sub ImportData()
  'This uses the 'Saved Specs' when the table is first imported manually
  DoCmd.TransferText acImportFixed, "MySpecs", "MyTable", "C:\myFile.txt", fasle
End Sub
Sub ExportData()
  'This uses the same 'Saved Specs' that were used when creating the table
  DoCmd.TransferText acExportFixed, "MySpecs", "MyTable", "C:\myFile.txt", fasle
End Sub

However I wanted a vba solution using schema.ini file so that I could easily change bits if necessary.

I found this work around due to not being able to specifiy custom ini files in the TransferText method. I just had to modify to work whether the table existed or not.

Here is the code... Again, this works (Sort of)

Sub ImportSchemaTable()
 
  ' This uses my own schema.ini file.  If the table doesn't exist, it creates it.
  ' If the table does exist then the data is appended
  Dim Db As DAO.Database
  Dim bExists As Boolean
  Set Db = CurrentDb()
 
  On Error Resume Next
  bExists = IsObject(Db.TableDefs("MyTable"))
  If bExists Then
    Db.Execute _
      "INSERT INTO MyTable SELECT * FROM " & _
      "[Text;FMT=Fixed;HDE=Yes;DATABASE=C:\myFile.txt;].[myText#txt];", dbFailOnError
  Else
    Db.Execute _
      "SELECT * INTO MyTable FROM " & _
      "[Text;FMT=Fixed;HDE=Yes;DATABASE=C:\myFile.txt;].[myText#txt];", dbFailOnError
  End If
 
  Db.TableDefs.Refresh
 
  Set Db = Nothing
End Sub

The problem with this is that that table doesn't show up until I close Access and reopen.

Also, if I amend the ini file it will not change the how it appends data to the table unless the table is deleted first and then created again using the amended ini file. e.g. if the first colum has a fixed width of 10 the first time it imports it will be 10, but no matter what I amend it to it will alway be 10 unless the table is deleted.. Hope this makes sense.

Questions
1, Is it possible to use a custom ini file with the transfertext method? Have Microsoft fixed the issue?

2, On my second method, can I get the table to appear without having to re-open Access?

3, Why does the import data fixed width not after the ini is amended?

My Solution

After a long time trying to get the above solutions, I found out that you can amend the specs created as follows. And this is alot quicker than having to do the manual import again and savespecs again.

In Access 2007
In Access Options > Navigation Options, click Show System Objects.

In Access 2003
In Tools > Options > View, click System Objects.

This lets you modify the column names and widths in MSysIMEXSpecs and MSysIMEXColumns.

Appreciate any help/adivce or tips on any of this.

Thanks

 

[Guus2005]

Search for the V-Tools collection of functions. It contains a form to enter and alter import/export specifications. It uses queries to change data in the MSysIMEXSpecs and MSysIMEXColumns tables.

I am not sure what you want to do with the ini file or what's in the text file: "[Text;FMT=Fixed;HDE=Yes;DATABASE=C:\myFile.txt;].[myText#txt]

But i believe you want to alter the import/export specs using the ini file?

HTH

 

[kt1978]

Hi

Thanks for this. This may will come in handy.

The reason I wanted something like this is that I'm not entirely sure which columns of data I am going to need from my text file.

I know I could import all the data and then use queries, but I was curious about the import specs so wanted to find out a bit more.

It may be slightly pointless, but it will help me.

Thanks again.