import multiple txt files to different tables (1 Viewer)

[lookforsmt]

Thank you Sir (The Doc Man)
i have made it clear that i want to learn building a vba code and its for the simple reason that there are so many people here who are more the willing to help, Mr. Colins

To give you background of my project. I like to do small small automation at my work.
I have seen some of my colleague on daily basis spends almost 1 to 2 hours on daily basis first to convert the text file to excel and then balance the excel and then balance with other docs. This is prone to errors due to the value involved in the field.

I want to import the txt files to separate temp tables and then move them to the Master table and then delete the temp tables.
Because i wanted to import tables which are the latest files (i.e. todays date: yymmdd) but the file has the last 6 digits which keep on changing.

Currently Mr. Colin has helped me with the function code but i am stuck due to my poor knowledge in VBA.

regards
Mahesh

 

[jdraw]

???
Forum members are here and will help, as you have seen.
But what does this really mean

I have seen some of my colleague on daily basis spends almost 1 to 2 hours on daily basis first to convert the text file to excel and then balance the excel and then balance with other docs. This is prone to errors due to the value involved in the field.

Convert to excel? Balance? Other docs?

Can you give us the overview of "a day at the office"? Please use simple English and remember we know nothing of you or your business or your environment.

 

[lookforsmt]

Well for Jdraw and all the other here. i request help whenever i am unable to find on the internet. As mentioned by Mr. Colin to view some of the videos and follow some of you. Well i will try my best.

Well thank you all. specially Mr. Colin who has given his full attention in completing my project. Although I have still not able to complete this project but feel ashamed to ask more questions.

 

[The_Doc_Man]

OK, let's talk about design issues. Treat this as my "brain dump" on a narrow design question, intended to help you see how to approach this sort of thing.

You import stuff to a table and then append it to a main table and get rid of the leftovers. OK, perfectly understandable. But where the question comes into play is this:

Between the time that you import data and the time that you do the append, what else happens? That is where I have a "concept gap" and don't know the purpose of going through the process here. Are you doing some filtration? Some reformatting? Are you checking for errors? What is happening to the data in the temporary tables before you do that final append?

Second part of that: Your code is importing multiple tables at a time (I saw four at a time in an earlier post). Why do you need that many tables simultaneously resident? Is there a comparison step here? A reconciliation? An elimination of duplicates? What happens that you need so many tables at once? If there is no action that involves having multiple tables open at once for comparison, cross-table filtering, etc. then use ONE temp table and do these linearly. There is NO advantage (and potentially a source of confusion) to having multiple tables open when you don't do anything with them in aggregate.

These kinds of questions would be answered by having a specific design goal in mind. An incredibly important part of any project design is to know BEFORE YOU WRITE THE FIRST LINE OF CODE (or before you create the first form) - what are you doing? And you need to be able to answer that in some detail. Think of the design as a "road map" and you are on a journey. Without a road map, how will you ever know that you have reached your destination? For ALL of my own major projects, the very first thing I ever did was use WORD (not Excel or Access) to build a "design bible" that answered all of the important questions and presented all of the important goals - including criteria for knowing that I had actually REACHED the goals.

There is an old carpenter's saying that my father-in-law used to say regarding due diligence on any work project: Measure twice, cut once. We on this forum ALL know in the most personal and intimate detail that there is this incredible sense of the need to make things happen, to show progress, to get results. But we have to learn to counterbalance this urge with the incredibly important idea to prevent ourselves from having too many mistakes that will require rework. And like another old saying, "If you don't have enough time to do it right the first time, how will you EVER find enough time to fix it when it breaks?"

Having a design in hand (and paper is irreplaceable for this purpose), you can tell where you are going and can tell when something is a misstep. It saves your sanity. Almost literally!

Now, having said all of that, let me commend you for something.

I have seen some of my colleague on daily basis spends almost 1 to 2 hours on daily basis first to convert the text file to excel and then balance the excel and then balance with other docs. This is prone to errors due to the value involved in the field.

Bravo and kudos to you for recognizing that you have this absolutely phenomenal resource on your desktop that can help you do your job better! That machine wasn't bought as a paperweight; it was bought as an office productivity tool and YOU have seen a way to improve your productivity.

I will also point out that your potentially improved job performance WILL NOT HURT YOU ONE BIT at your next performance review.

Just be warned: Seeing how to make things better - and then actually doing something about it - is how you get promoted and get thrown into the arena to tame many wild office beasts, not just the one you have in front of you right now. But if that idea appeals to you, go for it!

 

[lookforsmt]

Well Mr JDraw, i am afraid to give you any info about my work. but it is the normal work and the notepad file that we convert to excel and then sum the totals ofcourse using the sum function in excel but thats the routine we follow everday.
If i can import these files automatic in access and then prepare the report it would save a lot of time.

i can attach my db if you want to have look

 

[lookforsmt]

Thanks DocMan for the advice and suggestions.

Coming to the questions asked

Between the time that you import data and the time that you do the append, what else happens? That is where I have a "concept gap" and don't know the purpose of going through the process here. Are you doing some filtration? Some reformatting? Are you checking for errors? What is happening to the data in the temporary tables before you do that final append?

Once the data is imported to the temporary table i simply move it to the Master table and delete the temporary table. Now the data in the Master table is reconciled and totals balanced with other docs and then archived for future reference.

Second part of that: Your code is importing multiple tables at a time (I saw four at a time in an earlier post). Why do you need that many tables simultaneously resident? Is there a comparison step here? A reconciliation? An elimination of duplicates? What happens that you need so many tables at once? If there is no action that involves having multiple tables open at once for comparison, cross-table filtering, etc. then use ONE temp table and do these linearly. There is NO advantage (and potentially a source of confusion) to having multiple tables open when you don't do anything with them in aggregate.

There are 4 text files and can go upto 8 text files in a day.These are received from different departments and different team responsible to check and reconcile this.

Definitely i want to do this project not for any gains but for personal satisfaction. BTW this does not give me any pay rise or promotion.

I have done couple of projects by taking some of the db and modifying to my needs. This was though looking simple was challenging with the file name otherwise i would have completed this much earlier.

 

[jdraw]

We were all in your shoes at one time. I'm not asking for commercial/confidential info about your business.
You get some sort of input as .txt about some subject(business entity)and people convert some of said text into excel and the remainder into other documents. That's extremely vague and certainly wouldn't be considered confidential.
It's in post #45 that I see a reference to Sum. So I am guessing that your info is some sort of finance/accounting or statistics.
You could give us an overview and even give us a made-up example to show us the kind of thing (conceptually) you are dealing with. Invoices, shipments, inventory,payments all come to mind, but why do we have to guess at your business in general terms?

It appears that no one in your organization has reviewed the data and business processes with a thought toward automation. Congrats to you for recognizing that designed automation could reduce manual entry/transcription and calculation errors.
You will find that automation and database requires discipline with particular focus on data management.

Good luck with your project.

 

[lookforsmt]

Hi! Colin i am still getting error. can i upload my db if you can have a look at it pls

 

[lookforsmt]

Hi! just to be clear on my project i just put them in below sequence

1) Receive text files in particular folder
2) Import the text files data from this folder to access temporary table (Popup message to tell he user that xyz files are imported with count)
3) Copy only the text file name in a separate table (This will be unique to avoid duplication)
4) Transfer to Master table from temporary table (Popup message to tell the user nos of rows transferred from temp table name)
5) Delete data from temporary table (Popup message with the row count of data deleted)

I want to accomplish all the above by click of one button.

I have also attach my db.

 

[isladogs]

Hi

Having got this far, I thought it only fair to look at your db and the fault was mine entirely. In BOTH my recent posts I had left out the wildcard *.

I've now corrected both posts #34 & #40.
Apologies for the errors

So all you need to do is change your code to

path1 = NewestFilePath("E:\Import\Folder1\Daily download folder\[COLOR="Red"][B]"[/B][/COLOR], "[COLOR="red"][B]*[/B][/COLOR].txt")

NOTE
I've added the 2 items shown in red
It now compiles correctly & assuming your import spec is correct, it should now work.
Obviously I haven't tested that

 

[lookforsmt]

i have tried both the below codes and they work fine picking up the latest file.

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\", "*.txt")

path1 = "E:\Import\Folder1\Daily download folder\" & NewestFileInFolder ("E:\Import\Folder1\Daily download folder\", "*.txt"

The only question is how can i put either of the above code in the below code as i have 4 text files and if i go with the above code it copies the latest modified text file to all the tables, i guess since the path is same for all without differentiating the text file start name "ICCSC01001026" & Format(Date, "yymmdd")

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111101 & ".txt"

i have tried doing this but got an error

path1 = NewestFilePath("E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd")& ", "*.txt")

 

[isladogs]

You can't by this method.
So unless you can come up with a clear 'rule' for defining what file goes to which table you are stuck with a manual system

Going back to earlier comments, it would make far more sense to import all files to the same table. It would also be far simpler.
If you wanted, you could always add a field to the final table giving the name of the source file used for the data

 

[lookforsmt]

Coming all this far and if i opt for manual process it would be really hurting me.

Quote

So unless you can come up with a clear 'rule' for defining what file goes to which table you are stuck with a manual system

how can i define this any clue.

is there a way to use the wild card below on the last part of the name (111101) and complete the string so i get the result as: ICCSC01001026 yymmdd 111101

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111101 & ".txt"

thanks

 

[isladogs]

Coming all this far and if i opt for manual process it would be really hurting me.

Quote


how can i define this any clue.

is there a way to use the wild card below on the last part of the name (111101) and complete the string so i get the result as: ICCSC01001026 yymmdd 111101

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111101 & ".txt"

thanks

No idea how you can define it - that's been the issue from the start.
My advice (same as DocMan & jdraw) is use this experience to get the system modified in order to provide a much simpler import method

I've already answered the question about the path many posts ago ... but you didn't like it as it always looks for the same file

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & "111101.txt"

Time to stop going round in circles and redefine 'your' approach to storing the data so it can be imported easily.

I suggested using the same destination table for all imports with an extra field listing the source file is needed

But it could be much simpler still - look back to previous posts

Good luck with your project

 

[lookforsmt]

Well if you suggest the idea of importing all the text files in one table along with the text file name. I like the idea. Do i need to do any changes in the codes to achieve this.

Can you also advice me, once all the text files are imported in one table, can this be now moved to the master table based on the text file name.

Whether a) the text files are moved to individual tables or b) common table, as my final accomplishment was to get the text files to the Master table, so if it is done either by a) or b) it does not matter.

thanks

 

[isladogs]

Try and do this properly this time around.

First do some planning on paper.
Work out a strategy for getting the data.
Make it as simple as possible.

Get your bosses to agree to the new system in principle

This should include a sensible naming system for the source files
The 6 digit bit needs to go and be replaced with e.g 1, 2 etc

Have one final master table
Have one temp table or omit that step completely - is it really necessary?

Then do an import by looping through each of the import files for that day.
Identifying those files won't be an issue.
In fact you should be able to see how from earlier answers

Think it all through, then start a new thread with the plan.
Make sure it is clear.
You will hopefully get some fresh advice...

Over and out from me on this thread.

 

[lookforsmt]

Thanks Colin for the advice and suggestions.
Want to thank you for the time and patients in listening.

Definitely will give it a try. Hope to see you around.

bye for now

 

[The_Doc_Man]

I come back to my original question: Why do you have four tables apparently open at once when you don't do anything other than import them in some particular way? If the tables are inter-related then there might be something to do - but if they are each treated independently, you could do this as a loop taking files until you have no file that has NOT been processed. Since you say you keep a list of the files that WERE processed, you could do any of several tests including perhaps a DCount to see if a given file name appears in your table that contains the processed file names. The DCount will be either 0 or 1, simple test in that case.

Then if you DO this linearly, there is only one temporary table at a time, and you can empty it beforehand, import into it, do your "thing" whatever it might be, append, and keep on going until everything you see has been processed. Or as we sometimes say in our typical shorthand.... lather, rinse, repeat.

 

[lookforsmt]

Dear Sir Doc Man, absolutely what you mentioned above is what i need.
Thank you for stepping into this.
What you mentioned sounds simple but for me these are challenges as i mentioned earlier due to my limited poor lack of VBA knowledge.
I am doing this task on daily basis by converting the text files into excel and then with transfer method importing into access by a macro. But VBA really fascinates me.

My goal of this project was to import the 4 text files in a separate 4 separate temp tables and move them to 4 different Master tables and delete the temp tables.
Store the name of the 4 text files in separate table to avoid any repeat imports in case someone else does the same process again.

**START**
Step1: Manually copy the 4 text files in folder "E:\Import\Folder1\Daily download folder" ( Sample name of the text file: ICCSC01001026170916111101
Constant: ICCSC01001026
Changing date: 170916 as todays date
Random changing nos: 111101

Step2: Store the 4 names of the text files in "tbl_Files_Imported"
ICCSC01001026170916111101
ICCSC01001034170916111501
ICCSC01001037170916111501
ICCSC01001051170916111501

Step3: Validate table "tbl_Files_Imported" before importing the 4 text files to access 4 temp tables: "tbl_26_temp", "tbl_34_temp", "tbl_37_temp" & "tbl_51_temp" respectively.

Step4: Append to the Master table: "26_Master", "34_Master", "37_Master" & "51_Master" respectively and then delete the data from the 4 temp tables.
**END**

Challenge was to read the text file while importing as the latter part of the text name (111101) keeps changing.
So thaught if this can be acheived by writing function. but i had very little success. I did find some link in this forum with similar question but they wanted to get the center name instead of the right side in my case.

I am hoping if someone can pls help me in this.