Import many text files into excel.

Hi,
I have over 400 text files which I need to edit in some way. There are a few columns (for example with date 20120101 ) And I have date.txt with all dates from particular time.

1) I want to import txt files
2) Then check if there is a date from date.txt and if not insert row with that date to the file (and add 0 value to other columns)
3) Save new txt files

As I said I have over 400 txt files and I need to automise it somehow. I was thinking about excel and writing some macros but, is it make any sense? Or mayby there is better way of solving that problem?

This could be done with excel -- could you upload a few sample txt files and the date.txt file as well - show how the file is prior to processing and how it should appear post processing. If any data is to be recorded in the excel workbook we would need a sample of that as well. Show samples where the date exists and where the date does not exist and show the outcome with these situations. Be VERY specific about the process flow - it may be clear to you but to whoever codes a solution there needs to be clear precise logic rules. Upload sample files (select Go Advanced, Manage uploads) and attach the files making sure you name them so we can see how the file appears before processing and how it appears post processing. Be very specific and show your logic. You will need to upload a few files so perhaps zip them up. Without sample files/sample workbook it is very hard to give you an answer.

I will have a file for you to process the files to your specifications uploaded in the next few hours for you. Just so you know I have not forgotten about you.

See attached workbook - all the instructions on how to use are on sheet 1 - essentially you import the Formula text file with the data, then import the Date text file and the data is then processed. I added in the option you wanted to calculate the Change. I compared the results to your example and it matches all except for one calculation - just check that your example is correct - I followed the formula you gave me so it may be a mistake in your example. All the figures are the same bar line 3 where you had -1.74 and I calculated it as -.241 so just check that. There is plenty of error handling so if you get any errors make sure you report back in the this thread the exact error if any occur. The code is written so you browse to select the data files and browse to where you want the data saved. I will not post the code as it is too long. If you have any problems let me know.
Once you are happy with the workbook you can delete the pictures I placed into the workbook that is part of the instructions on how to use the workbook. Every time you want to generate a text file just start up the attached workbook and import the data to be processed. You do not need to delete any data out of the file as the code will remove the old data and replace it with the new data you import so you do not need to maintain the attached workbook. The end result is the text file generated when you press the Export Data button.
Any questions just post back.

To read a txt-file in one go:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Actually snb I know the first method (the second I did not) however I have avoided the first method and I will explain why. It may be irrelevant with a text file however with word files I have used similar coding to read word documents in one go using scripting dictionary. The problems I have encountered has to do with file encoding. So for example

Please Login or Register  to view this content.

The problem I run into has been due to OLE objects - with word documents perhaps created in other programs or older versions of Word I find sometimes I run into word tying up the OLE object due to the file encoding - it will halt excel and wait for an input from the user to select the type of encoding (eg. Windows Default, Western European etc etc) so the code can then read the file. With text files I have not played around with reading in one go. Have you had any problems with encoding in text documents reading them like you have demonstrated above with your first example?
Due to the OLE conflict I tend to read text files as I did with the code I posted and I don't have any problems. Using your second example is essentially what I have done except with a lot more code so I could certainly adopt your second method rather than line by line however for my own edification have you run into any problems as I have described using scripting fso reading text documents?
My guess would be that encoding is probably not a problem with text files however due to technical difficulties I have had with word objects I have tended to steer away from your first example when I have to read hundreds of documents (or when I am unsure how the text file has been generated) due to problems with differences between files due to the possibility of file encoding problems. For example I may get hundreds of documents from multiple sources so they are generated by many different programs/methods. Essentially what I am asking is in your vast experience have you run into problems with TEXT documents reading them with your first code example above? This is mainly for my own edification. Can your first example run into problems with encoding problems with multiple text documents.
I will certainly adopt your second method due to the succinct coding however I am interested in your comments with your first code example when dealing with multiple text documents or text documents potentially generated by many different methods.
I hope this makes sense and look forward to your experience.
PS: What's up with your Avatar?