Convert Text file to Excel 2007

[Michael Abrams]

I have a text file with no headers.

I DO have the file specs for the text file. Field name and character count. We no longer have Access databases, so they powers to be want the text file imported into Excel so they can filter, sort etc.

Is there a (simple) way to convert the text file to Excel using the specs I have?

Thank you very much.

[HansV]

Have you tried opening the text file in Excel? It should automatically start the Text Import Wizard.

[Michael Abrams]

Yes - but it doesn't allow me to name the fields.

I need to create a macro (I think) to be able to save the specs for future imports of the text files.

[Michael Abrams]

Here is a thought - is there a way to create a template in Excel using the file specs, so when I import the text file it will import using the specs?

[HansV]

You could create and save a worksheet with the header row.
You'd only need to copy that row, then right-click the 1st row of the imported text file and select 'Insert copied rows' from the context menu,

If you prefer a macro, that would be possible. Unlike Access, Excel does not have the concept of an Import Specification, so you'd have to run the macro.

[Michael Abrams]

I will try that today HansV - thank you.

[Michael Abrams]

I just realized, the text file has no headers.

So I manually typed the header names in the spreadsheet but have no idea how to get the text file imported to fit the headers.

I am doing this for 20 years, and feel like a shmuck with this one. Can you possibly step by step this one?

And I absolutely apologize - it just ain't clicking in my brain.

[HansV]

You could open the text file in Excel (File > Open, specify Text Files as file type), then copy/paste the result into the sheet with the headers.

Or see the section "Import a text file by connecting to it (Power Query)" in Import or export text (.txt or .csv) files.

Post back if you need more help.

[Michael Abrams]

I may need to try a macro (and save it as an xlsm)

Not sure if I noted that the text file is fixed width so when the Import Wizard pops up I need to manually move the break lines.

I need to create a template so when I import the text file I can run a macro to do the work of delimiting .

Is this possible?

[HansV]

Yes. Could you attach a sample text file?

[Michael Abrams]

Here is the text file and the specs.

[HansV]

Can we omit the first and last line of the text file? They don't conform to the specs...

[Michael Abrams]

yes

[HansV]

Try the attached version. Click the button, select the text file, then click Open.

SPECS.xlsm

[Michael Abrams]

This is just too awesome. Does EXACTLY what I need.

HansV - thank you SO much (once again)