How do I stop an import process from re-using column names

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I wrote a VBA code to loop through a folder and look for
any file with a .txt extension and then import those files
into a database that I name during the process.
Everything works fine except for the fact that when I
import more than one table, all the column names are
exactly the same as the first table that is created. Does
anyone have any idea what the problem may be? I can post
the code if you would like.

Thank You
 
Here is the code.

Private Sub RenameFiles()

Dim strFolder
Dim strFileName As String
Dim strNewName As String
Dim InputFldr As String

InputFldr = "Type the path of the folder where the
files are located"
strFolder = InputBox(InputFldr)

strFileName = Dir(strFolder & "\*.xls")

Do Until Len(strFileName) = 0

' Only rename the ones with embedded periods.
If strFileName Like "*.*.*" Then

' strip off the ".xls" to get new name
strNewName = Left(strFileName, Len
(strFileName) - 4)

' remove any embedded periods (.)
strNewName = Replace(strNewName, ".", "")

' replace the ".xls" suffix.
strNewName = strNewName & ".txt"

' Rename the file.
Dim fs As FileSystemObject
Dim f As File

Set fs = New FileSystemObject
Set f = fs.GetFile(strFolder & strFileName)


f.Name = strNewName
Set fs = Nothing
Set f = Nothing
End If

' Get next file name.
strFileName = Dir()

Loop

End Sub

'This code is for the import button'

Private Sub Command11_Click()

Call RenameFiles

Dim myfile
Dim mypath
Dim InputMsg As String
Dim InputTblName As String
Dim mytable

'The below code allows you to chose the folder where the
text files are located'
InputMsg = "Type the path of the folder that contains the
files you want to import."
mypath = InputBox(InputMsg)

'The below code allows you to set the table name for the
output'
InputTblName = "Type the name of the table you want to
create."
mytable = InputBox(InputTblName)


myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", mytable,
mypath & myfile
myfile = Dir
Loop

End Sub

'This code is for the change extension button'
Private Sub Change_Extension_Click()

On Error GoTo Err_Change_Extension_Click

Dim stDocName As String

stDocName = "RunApp_test"
DoCmd.RunMacro stDocName

Exit_Change_Extension_Click:
Exit Sub

Err_Change_Extension_Click:
MsgBox Err.Description
Resume Exit_Change_Extension_Click

End Sub
 
Hi Bob,

Your loop below imports all the files to a single table called
"mytable", so there's no possiblity of the field (column) names
changing. If you want each different chunk of the imported data to have
its own column names, you need to import each file to a new table. For
instance you could use something like this to name each table after the
file imported to it:
DoCmd.TransferText acImportDelim, "Tab_Spec", _
Left(myfile, InStr(myfile, ".") - 1), _
mypath & myfile

Of course the above assumes that the import specification "Tab_Spec"
doesn't define field names itself but imports them from the header row
of the file.
 
I tried using the code and it creates a table for every
file I import. I need it to import all the files in each
folder to one table which I name. Then I am going to
change folders, and I need it to import all the files in
the new folder to a table I name. That is why a created
the variable 'mytable' because I can name it anything I
want. If I am importing everything into the same table,
why does the data change and the column headers stay the
same?

Thank You,

Bob
-----Original Message-----
Hi Bob,

Your loop below imports all the files to a single table called
"mytable", so there's no possiblity of the field (column) names
changing. If you want each different chunk of the imported data to have
its own column names, you need to import each file to a new table. For
instance you could use something like this to name each table after the
file imported to it:
DoCmd.TransferText acImportDelim, "Tab_Spec", _
Left(myfile, InStr(myfile, ".") - 1), _
mypath & myfile

Of course the above assumes that the import specification "Tab_Spec"
doesn't define field names itself but imports them from the header row
of the file.

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", mytable,
mypath & myfile
myfile = Dir
Loop
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Bob

First, you say your code creates a new table for every file you import.
I can't see how this can happen. The snippet below is from the code you
posted. The Do ... Loop iterates through all the .txt files in the
folder without changing the value of mytable, so they must all be
imported to the one table. I pasted it into my test database and that's
what it did.

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
'but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", _
mytable, mypath & myfile
myfile = Dir
Loop

So I don't understand what's happening here, unless the code you posted
is different from what you're actually running.

Second, you say the procedure uses the same field names when you import
the files from another folder. This is probably because you're still
using the same import specification - "Tab_Spec" - and the field names
are defined in that. If you want different field names in the table
created from each folder, you'll have to use a different import
specification for each.

I tried using the code and it creates a table for every
file I import. I need it to import all the files in each
folder to one table which I name. Then I am going to
change folders, and I need it to import all the files in
the new folder to a table I name. That is why a created
the variable 'mytable' because I can name it anything I
want. If I am importing everything into the same table,
why does the data change and the column headers stay the
same?

Thank You,

Bob
-----Original Message-----
Hi Bob,

Your loop below imports all the files to a single table called
"mytable", so there's no possiblity of the field (column) names
changing. If you want each different chunk of the imported data to have
its own column names, you need to import each file to a new table. For
instance you could use something like this to name each table after the
file imported to it:
DoCmd.TransferText acImportDelim, "Tab_Spec", _
Left(myfile, InStr(myfile, ".") - 1), _
mypath & myfile

Of course the above assumes that the import specification "Tab_Spec"
doesn't define field names itself but imports them from the header row
of the file.

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", mytable,
mypath & myfile
myfile = Dir
Loop
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
I also came to the realization that "tab_spec" is probably
the root of the problem. I am probably going to try to
create a check box which has a box for each of the three
specifications I need, then inputs the result into a
variable for the specification field. But I have no idea
how to do that.
-----Original Message-----
Bob

First, you say your code creates a new table for every file you import.
I can't see how this can happen. The snippet below is from the code you
posted. The Do ... Loop iterates through all the .txt files in the
folder without changing the value of mytable, so they must all be
imported to the one table. I pasted it into my test database and that's
what it did.

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
'but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", _
mytable, mypath & myfile
myfile = Dir
Loop

So I don't understand what's happening here, unless the code you posted
is different from what you're actually running.

Second, you say the procedure uses the same field names when you import
the files from another folder. This is probably because you're still
using the same import specification - "Tab_Spec" - and the field names
are defined in that. If you want different field names in the table
created from each folder, you'll have to use a different import
specification for each.

I tried using the code and it creates a table for every
file I import. I need it to import all the files in each
folder to one table which I name. Then I am going to
change folders, and I need it to import all the files in
the new folder to a table I name. That is why a created
the variable 'mytable' because I can name it anything I
want. If I am importing everything into the same table,
why does the data change and the column headers stay the
same?

Thank You,

Bob
-----Original Message-----
Hi Bob,

Your loop below imports all the files to a single table called
"mytable", so there's no possiblity of the field
Click to expand...
(column)
names
changing. If you want each different chunk of the imported data to have
its own column names, you need to import each file to a new table. For
instance you could use something like this to name each table after the
file imported to it:
DoCmd.TransferText acImportDelim, "Tab_Spec", _
Left(myfile, InStr(myfile, ".") - 1), _
mypath & myfile

Of course the above assumes that the import specification "Tab_Spec"
doesn't define field names itself but imports them from the header row
of the file.

On Wed, 21 Jul 2004 06:43:58 -0700, "Bob"

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", mytable,
mypath & myfile
myfile = Dir
Loop

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Bob,

Create a form (I'll call it frmImportParams) with a combobox on it (I'll
call it cboImportSpec). Make the combo's RowSource a list containing the
names of the specifications.

Provided the form is open you can refer to the value of the combobox
(and most other controls) in code with the syntax
Forms!NameOfForm!NameOfControl
so you'd use
DoCmd.TransferText acImportDelim, _
Forms!frmImportParams!cboImportSpec, _
mytable, mypath & myfile

You can of course add textboxes to this form so the user can specify the
table name and path there too, rather than using a succession of input
boxes.



I also came to the realization that "tab_spec" is probably
the root of the problem. I am probably going to try to
create a check box which has a box for each of the three
specifications I need, then inputs the result into a
variable for the specification field. But I have no idea
how to do that.
-----Original Message-----
Bob

First, you say your code creates a new table for every file you import.
I can't see how this can happen. The snippet below is from the code you
posted. The Do ... Loop iterates through all the .txt files in the
folder without changing the value of mytable, so they must all be
imported to the one table. I pasted it into my test database and that's
what it did.

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
'but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", _
mytable, mypath & myfile
myfile = Dir
Loop

So I don't understand what's happening here, unless the code you posted
is different from what you're actually running.

Second, you say the procedure uses the same field names when you import
the files from another folder. This is probably because you're still
using the same import specification - "Tab_Spec" - and the field names
are defined in that. If you want different field names in the table
created from each folder, you'll have to use a different import
specification for each.

I tried using the code and it creates a table for every
file I import. I need it to import all the files in each
folder to one table which I name. Then I am going to
change folders, and I need it to import all the files in
the new folder to a table I name. That is why a created
the variable 'mytable' because I can name it anything I
want. If I am importing everything into the same table,
why does the data change and the column headers stay the
same?

Thank You,

Bob
-----Original Message-----
Hi Bob,

Your loop below imports all the files to a single table
called
"mytable", so there's no possiblity of the field (column)
names
changing. If you want each different chunk of the
imported data to have
its own column names, you need to import each file to a
new table. For
instance you could use something like this to name each
table after the
file imported to it:
DoCmd.TransferText acImportDelim, "Tab_Spec", _
Left(myfile, InStr(myfile, ".") - 1), _
mypath & myfile

Of course the above assumes that the import
specification "Tab_Spec"
doesn't define field names itself but imports them from
the header row
of the file.

On Wed, 21 Jul 2004 06:43:58 -0700, "Bob"

myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a
time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec",
mytable,
mypath & myfile
myfile = Dir
Loop

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top