PLEASE HELP: automate process to import tables from notepad to acc

[sam]

Hi All,

Is there a way to import tables that are in text file (notepad) into access
automatically? at a specified time?

How can I do this?

For eg:

I have a location where I receive 10-15 tables in text format (notepad) and
I want to import all those tables in access (access.2007) at a specified
time. Can this be automated?

Thanks in advance.

 

[John W. Vinson]

Hi All,

Is there a way to import tables that are in text file (notepad) into access
automatically? at a specified time?

How can I do this?

For eg:

I have a location where I receive 10-15 tables in text format (notepad) and
I want to import all those tables in access (access.2007) at a specified
time. Can this be automated?

Thanks in advance.

Sure. You can use VBA code using the TransferText method to link to, or
import, the files. You may want to use the Dir() function (see the VBA help
for both TransferText and Dir) to loop through the files in the folder, or if
the names of the files won't change, you might want a table of filenames.

 

[Dorian]

Yes, you can set up a database that does this automatically upon opening and
then schedule the database to open via the Windows scheduler.
Since your database is operating unattended it cannot take any action that
requires interactive input (like displaying a message) and it must be able to
recover from any error.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[sam]

Hi John,

I did get transfertext to work, But I am having issues with missing values
in the text file. What I mean is, in the text file, there are missing values
in many rows and columns, and this is giving issues with delimiters. How can
I resolve this?

Thanks in advance

 

[John W. Vinson]

Hi John,

I did get transfertext to work, But I am having issues with missing values
in the text file. What I mean is, in the text file, there are missing values
in many rows and columns, and this is giving issues with delimiters. How can
I resolve this?

Can you give a specific example? If the missing values have no delimiters, how
could Access (or any program or any human!!) ascertain what should have been
there, or even what's missing?

That is, if the data should be

A B C D E F

and is in fact

A D F

it could be that B, C and E are missing; or it could be that the first three
fields are A, D and F and there's no data for the fourth, fifth and sixth
field!

 

[sam]

Hi John,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

Here is how it looks in access after i use the transfertext command:
And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

Column Name:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True

Thanks in advance

 

[John W. Vinson]

Hi John,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

Here is how it looks in access after i use the transfertext command:
And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

Column Name:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True

It would appear that the delimited by tab characters (ASCII code 9) which
aren't being seen as delimiters but rather being imported as is into a single
text field. I don't see anything wrong with your TransferText command, and I
actually copied your data to a file on my machine and got the same result! I'm
perplexed; it seems that the TransferText is not correctly recognizing the
delimiters.

However, using File... Get External Data... Import *does* seem to work
correctly. You may need to do so and use the Advanced button on the import
wizard to create and store an Input Specification. I'll raise this issue with
the other MVPs and see if anyone's seen this problem.

 

[sam]

Hi John,

I did the import wizard to create and store an Input Specification and it is
working fine now.

However, The issue I have now is, I have 7 text files to create 7 different
tables everyday, it there a way to delete the existing tables?

Also, the text files have date in their name, something like this:

"Text1_05.04.2010"
And this is giving me an error when i transfer the files by transfertext.
Once i remove the "05.04.2010" it works fine, I think it is the "." operator
which is giving me issues? is there any way I can make it work with
"Text1_05.04.2010"?

Thanks in advance

Hi John,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

Here is how it looks in access after i use the transfertext command:
And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

Column Name:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True

It would appear that the delimited by tab characters (ASCII code 9) which
aren't being seen as delimiters but rather being imported as is into a single
text field. I don't see anything wrong with your TransferText command, and I
actually copied your data to a file on my machine and got the same result! I'm
perplexed; it seems that the TransferText is not correctly recognizing the
delimiters.

However, using File... Get External Data... Import *does* seem to work
correctly. You may need to do so and use the Advanced button on the import
wizard to create and store an Input Specification. I'll raise this issue with
the other MVPs and see if anyone's seen this problem.

 

[John W. Vinson]

Hi John,

I did the import wizard to create and store an Input Specification and it is
working fine now.

However, The issue I have now is, I have 7 text files to create 7 different
tables everyday, it there a way to delete the existing tables?

Don't. Instead, use a static local table, and run a Delete query to delete all
the records, and then append the new data into it. Whichever way you do it -
deleting tables or emptying them - you should Compact your database regularly,
since the space occupied by those records will not be freed up unless you do!

Also, the text files have date in their name, something like this:

"Text1_05.04.2010"
And this is giving me an error when i transfer the files by transfertext.
Once i remove the "05.04.2010" it works fine, I think it is the "." operator
which is giving me issues? is there any way I can make it work with
"Text1_05.04.2010"?

That I really don't know! A period is, of course, meaningful in filenames
(msaccess.exe for example). You might try explicitly enclosing the filename in
quotes:

DoCmd.TransferText acImportDelim, , "Test1", """C:\REPORTS\Reports 1145\New
Folder\Text1_05.04.2010""", True

but I really don't know whether that will work or not. I'd be inclined to use
the Name function in VBA to rename the file before importing it if it doesn't.

 

[Bill Mosca]

Just a little reminder...Access will not import text files with extensions
other than the ones listed in the File type box of the import wizard
browser.

Those types are:
..csv
..txt
..tab
..asc

If you have any other extention that those you should be okay if you just
add a ".txt" to the end of the file name. If I remember right Access only
sees the part to the right of the last dot.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Hi John,

I did the import wizard to create and store an Input Specification and it
is
working fine now.

However, The issue I have now is, I have 7 text files to create 7
different
tables everyday, it there a way to delete the existing tables?

Also, the text files have date in their name, something like this:

"Text1_05.04.2010"
And this is giving me an error when i transfer the files by transfertext.
Once i remove the "05.04.2010" it works fine, I think it is the "."
operator
which is giving me issues? is there any way I can make it work with
"Text1_05.04.2010"?

Thanks in advance

Hi John,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

Here is how it looks in access after i use the transfertext command:
And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

Column Name:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to
access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports
1145\New
Folder\test1.txt", True

It would appear that the delimited by tab characters (ASCII code 9) which
aren't being seen as delimiters but rather being imported as is into a
single
text field. I don't see anything wrong with your TransferText command,
and I
actually copied your data to a file on my machine and got the same
result! I'm
perplexed; it seems that the TransferText is not correctly recognizing
the
delimiters.

However, using File... Get External Data... Import *does* seem to work
correctly. You may need to do so and use the Advanced button on the
import
wizard to create and store an Input Specification. I'll raise this issue
with
the other MVPs and see if anyone's seen this problem.