how to do this???

H

Henrootje

Hello Access-guru's, wizards and helpful people!

I am trying to import several textfiles into several tables.
Now I thought the following code up which works swell:


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="<my Importspecification>"
TableName = "<my Tablename>"
File2BImported= "<my File to be imported>"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


But I copy this code for every file and table I have to import.
Is it somehow possible to create for example a table containing the
tablenames, Importspecifications and so on?


Importspecification TableName File2BImported
Transactions tblTransactions
c:\temp\transactions.txt
Materials tblUsedMaterial
c:\temp\materials.txt
.............. ..............
....................


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="Transactions"
TableName = "tblTransactions"
File2BImported= "c:\temp\transactions.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


So that it will take the first record out of the table, fill the
variables using the first record in the table, execute the code and
then proceed with the second record, execute the code and so on and on
untill the end of the table? (seciond run would be as below;)


Code: 
Importspecification ="Materials"
TableName = "tblUsedMaterial"
File2BImported= "c:\temp\materials.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


I do not mind if it would be a matrix in code, not using a table. These

values will not change often, seldom even, but I want to reuse code
where I can, to prevent typingerrors and stuff.


I think it should be not that hard but to me unfortunately it is :(


TIA, Henro!
 
T

Terry Kreft

Sure, something like:-

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("Select * FROM Importspecifications",
dbOpenSnapshot)

With rs
Do Until .EOF
Call DoCmd.TransferText( _
acImportDelim, _
.Fields("Importspecification"), _
.Fields("TableName"), _
.Fields("File2BImported"), _
False, ""
)

.MoveNext
Loop
.Close
End With

Set rs = Nothing
Setdb= Nothing
 
H

Henrootje

Thank you Terry, for this contribution.

I am working on it but I have a problem implementing this;

Our company consists of 5 regions. Everything is identical for each
region but!
the name of the file to import is preceded by a number.
So If I want to do region 5 I would like to have a 5 added at the
beginning of File2BImported.

For this goal I would like to use something like this:

i = InputBox("What region?")

Category = "Materials"
FileLocation = "c:\files\"

File2BImported = Filelocation & Category & "\" & i & Category & ".txt"

--your code as suggested below---

Category, FileLocation, TableName, Importspec should be drawn from the
table.

Your solution works but then I have to enter the same data 5 times in
the table, one time for each region. Since the only difference between
them is one number in one field I think that could be done otherwise?
Although there are 5 regions, only one will be imported at a time, but
it is the users discretion which one that will be.

I cannot simply substitute .Fields("File2BImported") with
File2BImported can I?
If so, that would solve my problem straight away!


Terry Kreft schreef:
Sure, something like:-

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("Select * FROM Importspecifications",
dbOpenSnapshot)

With rs
Do Until .EOF
Call DoCmd.TransferText( _
acImportDelim, _
.Fields("Importspecification"), _
.Fields("TableName"), _
.Fields("File2BImported"), _
False, ""
)

.MoveNext
Loop
.Close
End With

Set rs = Nothing
Setdb= Nothing
--

Terry Kreft


Henrootje said:
Hello Access-guru's, wizards and helpful people!

I am trying to import several textfiles into several tables.
Now I thought the following code up which works swell:


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="<my Importspecification>"
TableName = "<my Tablename>"
File2BImported= "<my File to be imported>"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


But I copy this code for every file and table I have to import.
Is it somehow possible to create for example a table containing the
tablenames, Importspecifications and so on?


Importspecification TableName File2BImported
Transactions tblTransactions
c:\temp\transactions.txt
Materials tblUsedMaterial
c:\temp\materials.txt
............. ..............
...................


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="Transactions"
TableName = "tblTransactions"
File2BImported= "c:\temp\transactions.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


So that it will take the first record out of the table, fill the
variables using the first record in the table, execute the code and
then proceed with the second record, execute the code and so on and on
untill the end of the table? (seciond run would be as below;)


Code: 
Importspecification ="Materials"
TableName = "tblUsedMaterial"
File2BImported= "c:\temp\materials.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


I do not mind if it would be a matrix in code, not using a table. These

values will not change often, seldom even, but I want to reuse code
where I can, to prevent typingerrors and stuff.


I think it should be not that hard but to me unfortunately it is :(


TIA, Henro!
Click to expand...
Click to expand...
 
T

Terry Kreft

If the numbers are consecutive and you want to import all of the files then
you could do something like

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim iRegion as integer

Set db = Currentdb
Set rs = db.OpenRecordset("Select * FROM Importspecifications",
dbOpenSnapshot)

With rs
Do Until .EOF
For iRegion = 1 to 5
Call DoCmd.TransferText( _
acImportDelim, _
.Fields("Importspecification"), _
.Fields("TableName"), _
iRegion & .Fields("File2BImported"), _
False, ""
)
Next
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set db= Nothing


Or if you want to do 1 set of files you could do as you suggest below,
something like

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim iRegion as integer

iRegion = val(Inputbox("Which Region?"))

' Test here that iRegion is valid before proceeding

Set db = Currentdb
Set rs = db.OpenRecordset("Select * FROM Importspecifications",
dbOpenSnapshot)


With rs
Do Until .EOF
Call DoCmd.TransferText( _
acImportDelim, _
.Fields("Importspecification"), _
.Fields("TableName"), _
iRegion & .Fields("File2BImported"), _
False, ""
)
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set db= Nothing



--

Terry Kreft


Henrootje said:
Thank you Terry, for this contribution.

I am working on it but I have a problem implementing this;

Our company consists of 5 regions. Everything is identical for each
region but!
the name of the file to import is preceded by a number.
So If I want to do region 5 I would like to have a 5 added at the
beginning of File2BImported.

For this goal I would like to use something like this:

i = InputBox("What region?")

Category = "Materials"
FileLocation = "c:\files\"

File2BImported = Filelocation & Category & "\" & i & Category & ".txt"

--your code as suggested below---

Category, FileLocation, TableName, Importspec should be drawn from the
table.

Your solution works but then I have to enter the same data 5 times in
the table, one time for each region. Since the only difference between
them is one number in one field I think that could be done otherwise?
Although there are 5 regions, only one will be imported at a time, but
it is the users discretion which one that will be.

I cannot simply substitute .Fields("File2BImported") with
File2BImported can I?
If so, that would solve my problem straight away!


Terry Kreft schreef:
Sure, something like:-

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("Select * FROM Importspecifications",
dbOpenSnapshot)

With rs
Do Until .EOF
Call DoCmd.TransferText( _
acImportDelim, _
.Fields("Importspecification"), _
.Fields("TableName"), _
.Fields("File2BImported"), _
False, ""
)

.MoveNext
Loop
.Close
End With

Set rs = Nothing
Setdb= Nothing
--

Terry Kreft


Henrootje said:
Hello Access-guru's, wizards and helpful people!

I am trying to import several textfiles into several tables.
Now I thought the following code up which works swell:


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="<my Importspecification>"
TableName = "<my Tablename>"
File2BImported= "<my File to be imported>"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


But I copy this code for every file and table I have to import.
Is it somehow possible to create for example a table containing the
tablenames, Importspecifications and so on?


Importspecification TableName File2BImported
Transactions tblTransactions
c:\temp\transactions.txt
Materials tblUsedMaterial
c:\temp\materials.txt
............. ..............
...................


Code: 
Dim Importspecification, TableName, File2BImported as string


Importspecification ="Transactions"
TableName = "tblTransactions"
File2BImported= "c:\temp\transactions.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


So that it will take the first record out of the table, fill the
variables using the first record in the table, execute the code and
then proceed with the second record, execute the code and so on and on
untill the end of the table? (seciond run would be as below;)


Code: 
Importspecification ="Materials"
TableName = "tblUsedMaterial"
File2BImported= "c:\temp\materials.txt"


DoCmd.TransferText acImportDelim, Importspecification, TableName,
File2BImported, False, ""


I do not mind if it would be a matrix in code, not using a table. These

values will not change often, seldom even, but I want to reuse code
where I can, to prevent typingerrors and stuff.


I think it should be not that hard but to me unfortunately it is :(


TIA, Henro!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quickest method to take data from a table, modify it and put it in another table 5
user interaction during import? 2
Import mutiple name text files into Access 2003 1
help please : importing many .txt files in a folder to one table 23
Access 2003 conversion docmd.transfertext to linked table 1
Was unable to Append all the data due to.... 3
[VS2003] Updating table - how to do that ??? 6
Import csv file to Access 2003 table 2

Top