text file import wizard

[exaccess]

Hello Experts,
Is it possible to have a command button on a form to run the Text File Import Into a Table Wizard? Thanks.

 

[vbaInet]

Have a look at DoCmd.TransferText

 

[exaccess]

Have a look at DoCmd.TransferText

Thank you for the quite useful tip. I tried the command and it brings up the wizard. Now it is necessary to create a task which is probably a batch file where the initial parameters are stored so that the user can simply run it. However due to whatever reason I get the error message " The Outlook task can not be created". My purpose is to have just a simple batch file executing the wizard. Can please anyone point to the right direction? How should this kind of job be approached? Thanks.

 

[namliam]

In the "normal" wizard you can Save your settings.
These settings you can then use in the Docmd.TranferText to automaticaly load the file to a table.

 

[Mile-O]

The term for what namlian describes is import specification.

 

[exaccess]

Hi Folks,
The docmd.transfertext command is promising probably it will do what is requested from me. But to have a clear answer I have to sort out some details. This Import Specification file is stored where. Can somebody tell me please. Thanks a lot to all those contributing to the solution.

 

[vbaInet]

The Import Spec is stored in your database. It's simply a template for your import.
Run the import once with all your configurations, save it as a spec, run it again -> use the already saved spec.

Through the Import/Export Wizard you'll find where to save as a Specification.

 

[exaccess]

The Import Spec is stored in your database. It's simply a template for your import.
Run the import once with all your configurations, save it as a spec, run it again -> use the already saved spec.

Through the Import/Export Wizard you'll find where to save as a Specification.

If I run the code

Dim Forname As String, OriginTbl As String, CSVInput As String, Proc As String
    CSVInput = "OrInput.csv"
    OriginTbl = "OriginalTbl"
    Proc = "ImpBank"
    If fExistTable(OriginTbl) = True Then
        DoCmd.DeleteObject acTable, OriginTbl
    End If
    DoCmd.TransferText acImportDelim, Proc, OriginTbl, CSVInput, True

It comes back saying it can not find the procedure. When I run standalone once more as stated above using the procedure the error message changes to :

Access 2010 Error: "The text file specification '<number>' does not exist. You cannot import, export, or link using the specification"

I googled this message. Microsoft says it is a known problem. They propose solutions first is using Access 2007 which I do not have, second gives a link to the same article which is useless and the third is a quite complicated long process. Now could anyone help please.

 

[vbaInet]

Two things:

1. Just as the error message states, it can't find the import spec meaning that you haven't created the import spec. An import spec can be created by clicking the Advanced button at the bottom of the wizard. See here:
http://www.btabdevelopment.com/ts/default.aspx?PageId=25
2. You've just entered a file name without the full path to the file. I know that it's called a Wizard but it's not that clever

Get this working and I'll give you another tip.

 

[CJ_London]

you can also use the openastextstream method - see this link

http://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx

you can then loop through the file, reading each row and then use a bit of sql to insert the row into a table.

Benefit is that you don't need an import specification as such, but still need to know the file structure (field delimiters, text qualifiers, has headings) to import the data.

Here is a code snippet - my preference is to insert data to a temporary table where all fields are text, then insert data into the relevant tables (I frequently get sent reports in flat file or 'worksheet ' form and need to import different parts of it to different tables

Set fs = CreateObject("Scripting.FileSystemObject")
Set TxtFile = fs.opentextFile(SrcFile, 1, 0)
If RStP.Fields("SysHasFieldNames") = True Then S = TxtFile.readline
While Not TxtFile.AtEndOfStream
      If Len(Nz(RStP!SysRange)) > 0 Then
            delim = left(RStP!SysRange, 1)
      Else
            delim = "'"
      End If
      If delim = "T" Then delim = Chr(9)
      If Len(RStP!SysRange) > 1 Then qualifier = Mid(RStP!SysRange, 2, 1) Else qualifier = """"
      srcfields = "'" & Replace(Replace(TxtFile.readline, "'", "''"), delim, "','") & "'"
      sqlStr = "INSERT INTO impTable VALUES (" & srcfields & ")"
      CurrentDb.Execute sqlStr
Wend

If you want to look at import specifications, look in your system tables for MsysIMEXSPecs (which is delim information) and MsysIMEXColumns (which relates to each column).

Prior to importing anything using the wizard these tables do not exist - they are created when you create an import specification. Note that although you can create these tables yourself, Access will not recognise it. However, once created by Access you can add new records.

 

[exaccess]

Two things:

1. Just as the error message states, it can't find the import spec meaning that you haven't created the import spec. An import spec can be created by clicking the Advanced button at the bottom of the wizard. See here:
http://www.btabdevelopment.com/ts/default.aspx?PageId=25
2. You've just entered a file name without the full path to the file. I know that it's called a Wizard but it's not that clever

Get this working and I'll give you another tip.

It works. Thanks. What is the other tip?

 

[vbaInet]

There's also the Stream class in ADO but streams are meant for manipulating text files, that's the sole purpose of these classes. When it comes to batch processing a loop just isn't fast enough.

I remember looking for efficient ways to upload csv files containing 600k+ rows, the TransferText method did it in mere seconds whilst the stream methods for both ADO and the FSO took almost 5.5 minutes. The main problem being the singular INSERTs that's performed for each iteration. Looping through the file is a breeze.

If there's any text manipulation that needs to be done it's either you do it in the Stream, close it, and upload the transformed file using TransferText (into a staging table and onwards) or you upload the raw file into a staging table using TransferText, transform the data and load into the main table.

As for the system tables I really won't advice touching that unless you know what you're doing. Of course CJ and myself know the structure of the tables and we can do whatever we like with it, but I don't think you should be touching those tables if you don't yet fully understand how the Import Spec works. If the spec was created you won't receive that error.

 

[vbaInet]

I was typing away when you posted.

It works. Thanks. What is the other tip?

If the following conditions are met:
1. the file name is always the same
2. the file is always in the same location
3. the layout of the file is consistent
...then you can actually save the entire import process so that next time you want to import the file you simply click a button and it does it for you.

And like CJ mentioned, it's best to import into a staging/temporary table first.

 

[exaccess]

you can also use the openastextstream method - see this link

http://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx

you can then loop through the file, reading each row and then use a bit of sql to insert the row into a table.

Benefit is that you don't need an import specification as such, but still need to know the file structure (field delimiters, text qualifiers, has headings) to import the data.

Here is a code snippet - my preference is to insert data to a temporary table where all fields are text, then insert data into the relevant tables (I frequently get sent reports in flat file or 'worksheet ' form and need to import different parts of it to different tables

Set fs = CreateObject("Scripting.FileSystemObject")
Set TxtFile = fs.opentextFile(SrcFile, 1, 0)
If RStP.Fields("SysHasFieldNames") = True Then S = TxtFile.readline
While Not TxtFile.AtEndOfStream
      If Len(Nz(RStP!SysRange)) > 0 Then
            delim = left(RStP!SysRange, 1)
      Else
            delim = "'"
      End If
      If delim = "T" Then delim = Chr(9)
      If Len(RStP!SysRange) > 1 Then qualifier = Mid(RStP!SysRange, 2, 1) Else qualifier = """"
      srcfields = "'" & Replace(Replace(TxtFile.readline, "'", "''"), delim, "','") & "'"
      sqlStr = "INSERT INTO impTable VALUES (" & srcfields & ")"
      CurrentDb.Execute sqlStr
Wend

If you want to look at import specifications, look in your system tables for MsysIMEXSPecs (which is delim information) and MsysIMEXColumns (which relates to each column).

Prior to importing anything using the wizard these tables do not exist - they are created when you create an import specification. Note that although you can create these tables yourself, Access will not recognise it. However, once created by Access you can add new records.

Very valuable information. I'll take a look when time permits. The problem is we are getting these files from a large organisational customer and they are not well structured with lots of long text fields containing many blanks between textual info and the structure of the text file may change without notice. So we have to have a parametric and flexible system that can adopt to these changes without any and if possible zero (hardly imaginable) programming. Especially scanning those long text fields to discover info is quite cumbersome. Any tips or tools in this area is highly appreciated. Thanks again.