We help IT Professionals succeed at work.

Automatic import txt table

jmw666
jmw666 asked
on
126 Views
Last Modified: 2012-06-07
I have a table in txt format named table1. Table1 will keep changing to different name. File location is in C:\dbsouce\jm\table1.

The table structure is:

ID|FIRSTNAME|SURNAME|ADDRESS|CITY|STATE|POSTCODE|

Can any body help me to develop a module to import the txt table? For example, if I have a form with a text box and a button, when user type in the txt table's name and click the button, the tex table would automaticly transfer to db1.mdb.    
Comment
Watch Question

Commented:
Use DoCmd.TransferText

Commented:
The easiest way is to import the table manually and save the import specification once you can import the file properly...
then attach the following code to a form button (it should also have a text box which you type the name f your file named txt_file_name).

DoCmd.TransferText acImportDelim , [specificationname], table1, "C:\dbsouce\jm\" & me!txt_file_name

hope that helps
A minor but important enhancement to mike1086's suggestion- let your user select the file to import by using the standard File Open dialog box that you see everywhere in Windows rather than simply prompting for a file name in a text box. You can do this by adding the Common Dialog control to your form (you may have to click on the More Tools icon to find it) or you can use API calls. Let me know if you want more info.

Author

Commented:
mike1086,

Thanks for yuor help. I know how to import manully but I do not know how to save the 'specification'. What is the name of [specificationname]?

Author

Commented:
davereynold,

Could you give me the code?
Looks like my responses from Friday 6-23 got put in the bit bucket when EE crashed. Let's try again:
In A97 the only way to create a specification is to use the Wizard to actually start the import process. When it displays the grid with the first few records, click on the Advanced button. This will open the Import Specification dialog- fill in the grid with your field info and then save (i.e. as "MyImpSpec").

Then when you issue the Docmd.TransferText command, replace [specificationname] in Mike's example with "MyImpSpec".

I'll repost the code to use the File Open dialog soon.
jmw666,
To allow the user to browse for the file, you must put a Common Dialog Control on your form- you may have to select the More Tools icon while in design mode and look for "Microsoft Common Dialog Control 6". It will probably be assigned a name like "ActiveXCtl0" when it gets put on the form. The control will be invisible at run time so you need to interact with it via code (i.e. create a button with the following code in the Click event):

       With ActiveXCtl0 ' Ask for new file location.
         .DialogTitle = "Please Select New Data File"
         .Filter = "All(*.*)|*.*"
         .FilterIndex = 1
         .ShowOpen
         ' If user responded, put selection into text box on form.
         If Len(.FileName) > 0 Then
            ' user selected a file
         Else
            ' user hit Cancel
         End If
       End With

The property ActiveXCtl0.FileName will contain the full path to the selected file.

HTH


Incidently, some of the EE experts have a stong preference to invoke the File Open dialog by using API calls, and there is a long winded discussion with several examples at https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10488079 
I've used both methods and IMHO using the Common Dialog control is easier to implement.

Good luck!

Author

Commented:
davereynold,

For the following code,

If Len(.FileName) > 0 Then
                                ' user selected a file
                             Else
                                ' user hit Cancel
                             End If

could you give me sample code for" 'user selected a file" and " 'user hit Cancel"?

Thanks.
For example:

If Len(.FileName) > 0 Then
   ' import the text file to MyTable using spec MyImpSpec
   Docmd.TransferText acImportDelim,"MyImpSpec","MyTable",.FileName
   MsgBox "Done", vbInformation
Else
   ' operator did not select a file or hit Cancel button
   Msgbox "Cancelling import", vbCritical
   Exit Sub
End If

You should search Help for "TransferText Method" to be sure that you code the command correctly (i.e. does the first record in your text file contain the field names?).
If Len(.FileName) > 0 Then
' user selected a file
     Msgbox "User selected a file"

Else
' user hit Cancel
     MsgBox "User didn't select a file."

End If

Author

Commented:
File location is in C:\dbsouce\jm\table1.

                    The table structure is:
                  ID|FIRSTNAME|SURNAME|ADDRESS|CITY|STATE|POSTCODE|

So what these two parameters "MyImpSpec","MyTable" should be?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hi jmw666,
Anything happening?

Author

Commented:
sorry, davereynolds, I am too busy at this moment. I will try your code asap.

Author

Commented:
davereynolds,

Thanks for your great help! Here is my test code which comes from you.Private Sub Command1_Click()
With ActiveXCtl0
..DialogTitle = "Please Select New Data file"
..Filter = "All(*.*)|*.*"
..FilterIndex = 1
..ShowOpen
If Len(.FileName) > 0 Then DoCmd.TransferText acImportDelim, "MyImpSpec", "MyTable", .FileName
MsgBox "Done", vbInformation

End With
End Sub

It seems COMMON DIALOG CONTROL works well, but import process has some problem. All the table imported are erro message table!

Private Sub B()
Dim ImportTextFilePath As String
Dim TableName As String
Dim SpecName As String

ImportTextFilePath = ActiveXCtl0.FileName
TableName = "Mytable"
SpecName = "MyImpSpec"

DoCmd.TransferText acImportDelim, SpecName, TableName, ImportTextFilePath


End Sub


Hi jmw666,
Generally, when every record ends up in the error file it indicates that your imported data does not agree with your destination table definition, or that you have an error in your import specification.
If you need assistance with this I need the following info:
1. Description of the ASCII file you are importing.
2. Your import specificiation.
3. Your destination table definition.

Author

Commented:
I am importing many TAB-delimited text file each day.
Each text file goes to an Access database as a new table.
These Access new tables name should be same as the original text file table name.


I have followed your instruction.
I saved Specification as “MyImpSpec”.
I did not predefine any table. But in the code I used “Mytable” as Table Name.

Thanks for your great help. I will increase the points when I accept the answer.
Hi jmw666,
I am confused.

Do you wish to create a new table in Access with the same name as the text file every time you import a text file?To do this you must:
1. Ensure that the text file contains the field names in the first record.
2. Your import spec must indicate that field names are in the first record.
3. You must strip out the file name from the path returned from the common dialog control and use it as the table name (TableName) in the TransferText command.

OR

Do you want to keep using the same table ("MyTable") over and over again? 1. Your text files will not need to have the field names as the first record
2. You will have to clear the contents of the import table ("MyTable") before each import. This easy to do:
  CurrentDB.Execute "DELETE MyTable.* FROM MyTable;"
3. The TransferText command will always use "MyTable" as the table name (TableName).

Which method is better for you?

Author

Commented:
Hi! davereynolds,
 I do wish to create new table in Access with the same name as the text file every time I import a text file.

In the previous practice with your code, all the erro table had the same name as the text file.




Author

Commented:
Is it possible you give me the working code again? I have difficult to assign the table name.

I am sorry I have taken too much time from you. I will double the points.
Okay, to summarize:

You need to load text files, each with the same format but different names, each into a new Access table that has the same name as the text file.

Each text file has the field names as the first record, i.e. ID|FIRSTNAME|SURNAME|ADDRESS|CITY|STATE|POSTCODE where the "|" is actually a tab.

You have set up an import specification that describes the text file as tab delimited.

Now we need to prompt the user for the path to the text file, and then actually import it- see next comment.
Let us assume that you have a button (cmdImport) on your form that the user clicks to start the import process. The code in the click event for this button is:

Sub cmdImport_Click()
' prompt for path to text file
' the name of the common dialog control on the form is "ActiveXCtl0"
       With ActiveXCtl0 ' Ask for new file location.
         .DialogTitle = "Please Select File to Import"
         .Filter = "All(*.*)|*.*"
         .FilterIndex = 1
         .ShowOpen
         If Len(.FileName) > 0 Then
            ' user selected a file whose full path is returned in FileName
            ' extract the text file name & use it for table name
            Dim i As Integer
            Dim PathLen As Integer
            Dim NextChar As String
            Dim TableName As String
            TableName = ""

            PathLen = Len(.FileName)
            For i = PathLen To 1 Step -1
              NextChar = Mid(Path, i, 1)
              If NextChar = "\" Then
                  Exit For
              Else
                  TableName= NextChar & TableName
              End If
            Next i
            ' text file name is now in TableName

            ' import the text file to table using spec MyImpSpec
            ' note that the field names for the new table are expected in the first record of the text file!
            Docmd.TransferText acImportDelim, "MyImpSpec", TableName, .FileName, True
            MsgBox "Done", vbInformation
         Else
            MsgBox "Cancelling import", vbCritical
            Exit Sub
         End If
       End With

End Sub


jmw666- the code above assumes that the text files that are being imported always have a unique name (so a new table is created in Access) without the 3 character extension (i.e. "jmw666" rather than "jmw666.txt")

Author

Commented:
Hi! davereynolds,

I have just tested your code. This time some thing stuck at first line. "Sub cmdImport_Click()"

I assigned a new button on a new form and change the button's name to "cmdImport".I changed both Caption and Name properties.

I copied your code to On Click event. And manuly imported a table first. Saved the Spec name as "MyImpSpec".

For process Spec name, do I have to adjust some thing in Advanced Import dialog window?

Do I have to manually import a text file first to get Spec name then paste your code?

Thanks.

Author

Commented:
sorry davereynolds, I forgot add" Microsoft Common Dialog Control 6". I will retest your code.

Author

Commented:
After add in the Common dialog control, the code still not working.
What is the problem?

Author

Commented:
When I run the code, I got yellow hight the first line "Sub cmdImport_Click".
Do you get an error message before it highlights the first line? If so what does it say?

If you do not get an error message, it is possible that you have inadvertently set a breakpoint on that line- is there a red circle in vertical bar next to the line? If so, simply click on it to remove it.

Author

Commented:
Yes, I did get an error message. Since I am working on different computer, I have to go back to the computer to test the code again.

I will feedback to you after test your code again.
Hi jmw666,
Anything happening?

Author

Commented:
I still can not go through your code. Every time the erro message are different. Are there any simpler way to do such job?
CERTIFIED EXPERT

Commented:
jmw666, are you same jmw666 as in askme.com? Just curious... =)

Author

Commented:
Yes.

Author

Commented:
davereynolds,

Thanks for your help. I belive you give me good answer. Many times I nearly get your code working but I still can not reach the end.

We may discuss this question late on.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.