Importing multiple files..

[Kathie via AccessMonster.com]

Hello,
I have to import monthly, files that were once *.csv but due to commas in
addresses, the interface program was changed to dump tab delimited. Now my
code is not finding the files in the folder? The code is below - can anyone
help? (The files still are named with the extension of *.csv)
Function Import_Records()
'On Error GoTo Import_Records_Err

'Delete the all records in MONTHLY_IMPORTS table
'Call Delete_the_Monthly_Imports_Data

Dim db As DAO.Database
Dim strInputDir, strImportFile As String, strTableName As String
Dim strFileExt As String
Dim strSQL As String
Dim strSelectFdr As Module

'strSelectFdr = Select_Year
' the pathname of the folder that contains the files for import
strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

' the file extension for the type of files to import
strFileExt = ".csv"
' the destination table in the database
strTableName = "Genetics Imports"

strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

Do While Len(strImportFile) > 0
DoCmd.TransferText acImportDelim, "Genetics Import", strTableName,
strImportFile, False
strImportFile = Dir
Loop
' Beep
MsgBox "The Import has Completed", vbOKOnly, ""

--
Message posted via http://www.accessmonster.com

[MGFoster]

Kathie via AccessMonster.com wrote:

Hello,
I have to import monthly, files that were once *.csv but due to commas in
addresses, the interface program was changed to dump tab delimited. Now my
code is not finding the files in the folder? The code is below - can anyone
help? (The files still are named with the extension of *.csv) <SNIP> ' the pathname of the folder that contains the files for import
strInputDir = "S:\Council\APPS\GENETICS\CURRENT\" <SNIP> strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

<SNIP>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The path name has 2 \ characters after the concatenation:

S:\Council\APPS\GENETICS\CURRENT\\*.csv

Change this:

strImportFile = Dir(strInputDir & "\*" & strFileExt)

to this:

strImportFile = Dir(strInputDir & "*" & strFileExt)

Or, change this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

to this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAS2H4echKqOuFEgEQImAgCfeajWeKkITIIlQaRNHUysDl EQKqkAn0k+
zO36ktKiywydxsDUN5tzOSvh
=eTF2
-----END PGP SIGNATURE-----

[kathie via AccessMonster.com]

Thanks for the response. Neither worked. The code was ok until the file
changed. The file changed (not the name) from a comma delimited to a tab
delimited. I tried even changing the extension to a *.txt and that did not
work. I have also changed the specifications in "Genetics Imports" and nodda.
... :(

MGFoster wrote:

Hello,
I have to import monthly, files that were once *.csv but due to commas in
addresses, the interface program was changed to dump tab delimited. Now my
code is not finding the files in the folder? The code is below - can anyone
help? (The files still are named with the extension of *.csv)

<SNIP>

' the pathname of the folder that contains the files for import
strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

<SNIP>

strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

<SNIP>

The path name has 2 \ characters after the concatenation:

S:\Council\APPS\GENETICS\CURRENT\\*.csv

Change this:

strImportFile = Dir(strInputDir & "\*" & strFileExt)

to this:

strImportFile = Dir(strInputDir & "*" & strFileExt)

Or, change this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

to this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Can you put a breakpoint after the line:

strImportFile = Dir(strInputDir & "\*" & strFileExt)

to see if the string strImportFile is correctly formatted? Try running
it in a Command window to see if it returns the files you want:

C:>dir S:\Council\APPS\GENETICS\CURRENT\*.csv

I don't believe this is a comma-separated vs. a tab-separated problem --
unless the .csv file has been opened by an Excel program and then saved
as an Excel file instead of a Text file. Can u open the .csv file using
Notepad? Does it look OK?

Have you changed the import specification to use Tab as the field
separator?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAS8XIechKqOuFEgEQKlYwCeNtuTwmCu0rr5Cn4w27EJUc qcX9AAoKzO
Wdv1D03wUl9siNsu1sB7BpFv
=u4VT
-----END PGP SIGNATURE-----

kathie via AccessMonster.com wrote:

Thanks for the response. Neither worked. The code was ok until the file
changed. The file changed (not the name) from a comma delimited to a tab
delimited. I tried even changing the extension to a *.txt and that did not
work. I have also changed the specifications in "Genetics Imports" and nodda.
.. :(

MGFoster wrote:

Hello,
I have to import monthly, files that were once *.csv but due to commas in
addresses, the interface program was changed to dump tab delimited. Now my
code is not finding the files in the folder? The code is below - can anyone
help? (The files still are named with the extension of *.csv)

<SNIP>

' the pathname of the folder that contains the files for import
strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

<SNIP>

strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

<SNIP>

The path name has 2 \ characters after the concatenation:

S:\Council\APPS\GENETICS\CURRENT\\*.csv

Change this:

strImportFile = Dir(strInputDir & "\*" & strFileExt)

to this:

strImportFile = Dir(strInputDir & "*" & strFileExt)

Or, change this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

to this:

strInputDir = "S:\Council\APPS\GENETICS\CURRENT"

[osmethod]

Kathie via AccessMonster.com wrote:

Hello,
I have to import monthly, files that were once *.csv but due to commas in
addresses, the interface program was changed to dump tab delimited. Now my
code is not finding the files in the folder? The code is below - can anyone
help? (The files still are named with the extension of *.csv)
Function Import_Records()
'On Error GoTo Import_Records_Err

'Delete the all records in MONTHLY_IMPORTS table
'Call Delete_the_Monthly_Imports_Data

Dim db As DAO.Database
Dim strInputDir, strImportFile As String, strTableName As String
Dim strFileExt As String
Dim strSQL As String
Dim strSelectFdr As Module

'strSelectFdr = Select_Year
' the pathname of the folder that contains the files for import
strInputDir = "S:\Council\APPS\GENETICS\CURRENT\"

' the file extension for the type of files to import
strFileExt = ".csv"
' the destination table in the database
strTableName = "Genetics Imports"

strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

Do While Len(strImportFile) > 0
DoCmd.TransferText acImportDelim, "Genetics Import", strTableName,
strImportFile, False
strImportFile = Dir
Loop
' Beep
MsgBox "The Import has Completed", vbOKOnly, ""

--
Message posted via http://www.accessmonster.com

Just checking with you....

DoCmd.TransferText acImportDelim, "Genetics Import", strTableName,

Have you redefined this to transfer tab delimited data instead of comma
delimited data?

osmethod