jmw666
asked on
Automatic import txt table
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|ADDRE
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.
The table structure is:
ID|FIRSTNAME|SURNAME|ADDRE
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.
Use DoCmd.TransferText
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
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.
ASKER
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]?
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]?
ASKER
davereynold,
Could you give me the code?
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.
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
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!
I've used both methods and IMHO using the Common Dialog control is easier to implement.
Good luck!
ASKER
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 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",
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
' import the text file to MyTable using spec MyImpSpec
Docmd.TransferText acImportDelim,"MyImpSpec",
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
' user selected a file
Msgbox "User selected a file"
Else
' user hit Cancel
MsgBox "User didn't select a file."
End If
ASKER
File location is in C:\dbsouce\jm\table1.
The table structure is:
ID|FIRSTNAME|SURNAME|ADDRE
So what these two parameters "MyImpSpec","MyTable" should be?
The table structure is:
ID|FIRSTNAME|SURNAME|ADDRE
So what these two parameters "MyImpSpec","MyTable" should be?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi jmw666,
Anything happening?
Anything happening?
ASKER
sorry, davereynolds, I am too busy at this moment. I will try your code asap.
ASKER
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
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.
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.
ASKER
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.
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?
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?
ASKER
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.
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.
ASKER
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.
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|ADDRE
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.
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|ADDRE
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")
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")
ASKER
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.
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.
ASKER
sorry davereynolds, I forgot add" Microsoft Common Dialog Control 6". I will retest your code.
ASKER
After add in the Common dialog control, the code still not working.
What is the problem?
ASKER
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.
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.
ASKER
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.
I will feedback to you after test your code again.
Hi jmw666,
Anything happening?
Anything happening?
ASKER
I still can not go through your code. Every time the erro message are different. Are there any simpler way to do such job?
jmw666, are you same jmw666 as in askme.com? Just curious... =)
ASKER
Yes.
ASKER
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.
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.