Convert Text file to Excel 2007
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Convert Text file to Excel 2007
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.
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Text file to Excel 2007
Have you tried opening the text file in Excel? It should automatically start the Text Import Wizard.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
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.
I need to create a macro (I think) to be able to save the specs for future imports of the text files.
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
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?
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Text file to Excel 2007
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.
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
I will try that today HansV - thank you.
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
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.
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Text file to Excel 2007
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.
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
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?
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?
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
Here is the text file and the specs.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Text file to Excel 2007
Can we omit the first and last line of the text file? They don't conform to the specs...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Text file to Excel 2007
Try the attached version. Click the button, select the text file, then click Open.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: Convert Text file to Excel 2007
This is just too awesome. Does EXACTLY what I need.
HansV - thank you SO much (once again)
HansV - thank you SO much (once again)