Deal with text files automatically (1 Viewer)

[wasim_sono]

Dear All

I have assigned a project to pick data from text file(s) and then import it in MS Access. Then run a query and a report and make a PDF format of report.Then place that reports on FTP server. All of it to be done by just clicking one or two buttons i.e. a script should be written that performs all the tasks. Is it possible? if yes then how? Please feel free to ask if any thing required.

 

[Ranman256]

yes. use a macro to import the text file into a 'holding' table. This table can have a checkbox, to allow the user to mark true of the items to actually import. (pick data)

Next run a query that appends the data marked into the actual table.
now run a report that exports to PDF, using:
docmd.OutputTo acOutputReport ,"rptMyReport",acFormatPDF,"c:\folder\file.pdf"

then FTP the pdf. Not sure what you can do here since access doesnt FTP.

 

[The_Doc_Man]

Ranman's overview is good, but I'll add a few more thoughts to give you some hints for more research.

First and foremost, you must know the format of the text files very well or you will have a difficult time working with it quickly. You suggest that you are PICKING data from multiple files but that choice of words includes the idea that not all of the file is usable. In order for us to give you more focused advice, you might wish to tell us a little bit more about the expected number of files in a given session, the approximate size of each, and how much data you expect to extract. Other useful ideas include telling how you might recognize the targeted data in each file.

For example: Is it always a sequence of a fixed number of lines of text appearing at a fixed line number in the file? Is it something that appears following a specific keyword? Knowing about this process might help us suggest how to parse out the data.

Making a query is trivial once you have the data import problem resolved. You make it once and store it under some convenient query name. From that point forward, anything that can run a query will do the job, and Macros have a .RunQuery action.

Making a report based on a query is ALSO trivial and, if you are not faced with extremely difficult appearance requirements, might even be something you can define via the Report Wizard. Again, once the report structure is defined, you can store it under some convenient name and use it as needed.

IF you take Ranman's suggestion of using macros, you will need a small function to perform a DoCmd.OpenReport using VBA with some specific parameters that define PDF output to a file. Has to be done in a function because the Macro RunCode action doesn't run subroutines. Again, once it is written, anything that can call the routine will work, including a Macro RunCode action.

The last part of this is trickier as RanMan suggested. You could perhaps, with a pot-load of work, make some sequence of events using the CDO library (Collaboration Data Object) to transfer that file. However, it MIGHT be quicker if you were allowed to directly map or otherwise use a URS connection to the right folder on the FTP server and just use a FileCopy operation to move the PDF from your system to the FTP server. That would require cooperation of the FTP Server's administrator and probably your IT Security Administrator.

If you cannot directly connect then you will need a shell script to perform the FTP operation and will have to use VBA to "shell out" to that script. Here is where a little bit of research to learn what will be allowed might save you a ton of work later.

 

[wasim_sono]

Thank you very much Ranman256 and The_Doc_Man for your valueable replies and very sorry for my late response. Actually I am searching the solution as I'm still stuck to find the solution. The files I am dealing are fixed width. I used following code on a button in my form.

strFullPath = "H:/Akmal Imtiaz/Hyderabad/"
'DoCmd.RunSavedImportExport "import-Reading data"
DoCmd.OutputTo acOutputReport, "report1", acFormatPDF, "strFulPath" & "test.pdf"

But still not successful as files saving on FTP servers are with .txt extension and so the access does not recognize these files as text files.

Please guide me how can access recognize these files as text files.

 

[wasim_sono]

Thank you very much Ranman256 and The_Doc_Man for your valuable replies and I am very sorry for m late reply as I'm still searching for solution and stuck. Actually text files have fixed width and store on FTP with '.txt' extension due to which Access doesn't recognize these files as text and wizard stop.
I used following code on a button.

strFullPath = "H:/Akmal Imtiaz/Hyderabad/"
'DoCmd.RunSavedImportExport "import-Reading data"
DoCmd.OutputTo acOutputReport, "report1", acFormatPDF, "strFulPath" & "test.pdf"

Now I think I have to run DOS prompt within Access and rename the file name and then execute code. Is it possible? if yes then how?