Import csv files with same named structure

[anissw]

Hi- I need to daily import files that start with same name, but the remaining part will be Bill of lading ID (e.g. TEST_BOL 1234.csv). The next day will be TEST_BOL 1235.csv) and so on. I need for Access to import the file based on the "TEST_BOL" into my database and then delete the file from the folder. I don't know how to initialize Access to look for the prefix and import in. Can someone help me??

Thanks
anissw

 

[namliam]

Use the dir function (or the "FSO" File System Object) to find the full filename using a wild card (i.e. *)

Just FYI, strongly advice you to NEVER delete a file unless there is a VERY easy way to find it again.
Otherwize archive your file in a subfolder someplace.

 

[anissw]

Hi namliam-

I apologize for the delay in replying. Thank you so much. I am going to try the dir function. Just a quick question, instead of having the file delete once I import in, can I have the file copy into a stored folder? The temp folder is currently using a software (RAMP) to import data from a SFTP folder into the temp folder. The filename structure first few characters are the same, but last section is the BOL number. Once imported in, I need the file to be copied into an archive subfolder and removed from the temp. Is there a function (script) to have the file copied into the subfolder?

I'll be in touch with a few days about the FOS status. Again thank you-

 

[namliam]

yes you can best use the FSO object to work with files...
for a quick example:
http://social.msdn.microsoft.com/Fo...nother-using-vba-in-ms-access?forum=accessdev

 

[anissw]

Hi namliam-

Thanks so much for replying. I am in the right direction to utilizing the DIR function as the function goes out to look for the files with the name and it recognizes and reads it in completely into the error message. However, my message comes up saying Microsoft Access cannot find the object TEST_BOL 1234567 05.07.14.csv and to be sure the file exists. Maybe there's something I am overlooking?? Here's my function below:

Function TEST_BOL_IMPORT()
Dim TEST_BOLImport As String

DoCmd.SetWarnings False
TEST_BOLImport = Dir$("\\TEST.net\public\Warehouse\OSM\Imports\TEST_BOL*.csv")
DoCmd.TransferText acImportDelim, "", "TEST_BOL_IMPORT", "\\TEST.net\public\Warehouse\OSM\Imports\" & TEST_BOLImport, True, ""


End Function

 

[namliam]

You dont have an import specification entered, which is causing the (shitty) error message.

 

[anissw]

Hi namiliam-

I am really, really lost. Where would I code the import specification. I am looking for syntax examples and can't find any. Any assistance would be appreciated.

Thanks again.

 

[anissw]

Hi namliam-

I was able to setup the function after I went through the script again. Thanks so much. Just one question. My script will copy files into the archive folder without problems, but I would like the file in the temp folder to be renamed to a different name. Since my file name will start with 'TEST_BOL' and other values are different, how can I setup the NAME function in Access to look for the 'TEST_BOL' file with wildcard included. My process will copy files out from temp directory to archive, rename the file in the temp directory, import renamed file into Access based on import specifications, and then delete renamed file from temp location.

Thanks-

 

[anissw]

Hi namliam-

I was able to setup the function after I went through the script again. Thanks so much. Just one question. My script will copy files into the archive folder without problems, but I would like the file in the temp folder to be renamed to a different name. Since my file name will start with 'TEST_BOL' and other values are different, how can I setup the NAME function in Access to look for the 'TEST_BOL' file with wildcard included. My process will copy files out from temp directory to archive, rename the file in the temp directory, import renamed file into Access based on import specifications, and then delete renamed file from temp location.

Thanks-

 

[namliam]

Sorry you lost me while asking your question...
What is your current code, what is it doing and what is it you want it to do?

If you are looking to rename the file instead of copying/moving it... IIRC there isnt an actual FSO.Rename, however you can do the same with the simple FSO.MoveFile