Import .txt File with normal Space Delimiters

[GillsITWorld]

Hello!

I am trying to use a macro so that it will look for this .txt file and open it as a fixed width delimited so that I can have it edit and save as an excel.

.txt = BlankContracts.txt (Wont let me attached says incorrect file, just a .txt file)

So basically I will have an auto scheduler pull up the correct macro enabled excel form, this macro will then look for this file "BlankContracts.txt" on "C:\Users\Gill\Desktop" then open the txt file in excel as a fixed width delimited. Then I want it to search for the word "CONLST" and delete that row and the 4 rows after each time it finds the word.

[Kenneth Hobs]

I am not sure what fixed width means, per se. Some fixed widths are variable in location for the start but maybe a set width for field length.

To attach a txt file in this forum, rename as a csv file and then explain that you did that it your post.

As for finding the file, Dir() will tell you if it exists in the folder\path\filename.ext.

You can record a macro for how it would import to Excel. Then save it as csv, xlsx, xlsm, or such.

[Paul_Hossler]

(Wont let me attached says incorrect file, just a .txt file)

Rename it with an additional .zip extension and try again

[GillsITWorld]

Ok, following your instructions, I have attached the file, with a false .zip file extension.

26757

[Paul_Hossler]

So ...

Delete lines like 1-4 (CONLST line + 3) (you said 4)

Lines 5 + 6 look to be header lines -- leave or delete?? If Delete, than CONLST+5 more lines

Row 7 is data

Row 8 (CUR GP%) seems to be data, but a different layout than row 7. How do you want to handle that?


26760

[GillsITWorld]

Ok, so yes you are correct, I didn't even realize that Row 5 also had header info.
So Delete lines (CONLST line + 3) for each time CONLST appears and the rest of the data should be fine as is.

[GillsITWorld]

Just looking for follow up!

[p45cal]

It's not so easy, some Item#s have 2 rows associated, others 3, nor is there a constant number of rows to remove at the top of each page.
On Sheet1 of the attched is, I think, 90% of a solution; the problem for me was knowing how to split column C (Headed Merged) - I guessed at the widths and the headers.
To finish this off properly I need to know how to split that column better. To that end, Sheet4 has the result before any splitting of Column C. I've copied column C of that sheet to the sheet Column to split, and highlighted the rows with the most text in. If you can copy that sheet and show me where to split the data and give me header names I can finish the job - perhaps you can use Text-to-Columns?

This solution uses Power Query. The tables can be refreshed by right-clicking the table and choosing Refresh. Only it won't work when you get this file because BlankContract.txt is not in the same place on your computer. To correct this for Sheet1, right-click that table and choose Refresh - it will complain. Dismiss the message, right-click again and choose Table then Edit Query…. A new window will pop up where you need to click on the top step of the Applied Steps on the right, called Source,

26839

then click on Edit Settings:

26838

Then browse to your file, click OK.
Then click Close & Load top left:

26840

and you may just need to do another right-click and refresh on the table.

[p45cal]

Just looking for follow up, on follow up!

[GillsITWorld]

HAHA! Thank you, with the world in chaos this has taken a little of a back seat compared to a lot of whats going on. However I'll be in office next week and will be able to play a little more!