Is it possible to clean up and import such a raw text file as this to excel?

[davidsky1]

Please see attached documents. I am trying to automate the importing of extremely raw, auto generated text, into an excel table, which will be formatted with macros to look like the attached excel sheet.

Is it possible to automate the importing of such a rough text file? would the text have to be output in another way, i.e. would CSV make it much easier?


Thirdly, if CSV is easier, is there a way for me to somehow make this file CSV (something I tried, but if you notice in the sample text, this is not a fixed width file so it is very difficult to separate columns, even manually). This is for a client of mine who wants automation done, but I am not sure if it is possible. Feedback is appreciated.

[davidsky1]

Okay, I have figured out one thing, which is that if I space the headers with 2 tabs, and also 2-3 tabs for each column of data, everything begins to line up.
I am wondering if there is some sort of script to automatically implement such spacing rules...
I have attached the text file which shows the result of inserting 2 tabs.

Not really sure how to go forward with this project, or if automation of importing is actually possible....

[rscsmith]

Hi David

This imported quite easily for me (Excel 2003). I loaded it as Tab Delimited. I think it looks ok (see attached upload).

The formatting is easy, but I can't see how you determine what is an Augie/Joey vendor. Also, how do you decide which ones are Critical, and which ones belong on Sheet2?

What are the rules? Also, the column headings and number vary. Is there a 'map' of which columns have which headings, and which ones are deleted?

Is sorting required? I can't see any pattern in the formatted sheet.

Cheers, Rob.

[davidsky1]

Rob,

Thanks, I should have connected the dots about it being a "tab" delimiter. For now it looks like only a simple template is needed. Actually, the template will remove the necessity of writing VBA code to format.

Now the only thing needed is to write code for a button that imports the data from the text file, which is updated every day. I am guessing that using the macro recorder will do the trick as long as the same txt filename is used every time to extract the data. Thanks for the tip

david