Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited.
Delimited files are easily processed. With fixed-width files, Excel has a go at figuring out where to insert the column breaks, but more often than not, you'll have to add some, delete others and move some more to get the desired result.
- To insert a break, just click where you want it
- To move a break, just drag it to where you want it
- To delete a break, double-click it.
The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain.
But there is a solution.
Create a new workbook containing two sheets. One will house the raw data; the other will split it into columns.
Copy your raw data into the top of the first sheet using the traditional copy/paste method. This will all be stacked up in column A.
In the second sheet, we're going to create a frame for separating the data out into its constituent parts.
In row 3, put your column headers. In row 1 put that column's start position in the file. And in row 2, put the length of the field.
Now we'll use the MID function, together with dollars to hold our references, to put data in row 4.
You only need to create the formula in cell B4. Once you've done that, copy it across to the right-most column. Then copy that row down to cover as many records you copied into the original sheet.
I use a spreadsheet just like this regularly, calling it Template. And I pin it to my Excel Recent Documents list so that it's always only two clicks away.
After looking at several text import into Excel forums, I'm surprised at the extent to which it isn't known that Excel saves import queries and reruns them readily with the Refresh function. But my question is a little more esoteric. "With fixed-width files, Excel has a go at figuring out where to insert the column breaks." Is there any way, a setting or configuration, that keeps Excel text import form doing that? I'm exporting a dataset from Oracle, one of the columns is a CLOB sometimes over 3000 bytes. As I optimize my query, adjusting the output now and then, I've gotten tired of scrolling through a 3K+ column deselecting all those column breaks.
Dan...fantastic tip! I am faced with the task of importing a fixed width file on a regular basis that contains over a thousand columns. Your tip has saved me an immense amount of time!
Saved my life!!!
Amazingly simple, that is once I realized what you are doing
Saves me lots of time and allowed me to fully automate my import process
You could also record a macro and replay it
Pingback: Maintaining the same fixed width layout for columns for delimited text
Great tip and easy to apply. Thank you.
Fantastic tip Dan! So simple and clean.
Thanks
I been looking for this. It will save my life, if I can make it work.
When I copy: MID(Data!$A1,B$1,B$22) across cells, it doesn't change the cell reference.
Also, the Formula does not seem to work. I am sure I am doing something wrong, but I can't see it.
Using Excel 2007 (maybe I have something set wrong excel options)
I renamed Sheet1 = Data
Copy\paste data into Data A1
Sheet 2 -
added Start, Length and calculations for that.
added the actual header name I want to use to line 3
Applied the MID(etc...)to B4
thanks for any insight
whoa...holy cipes batman ! Got it to work.
I had the MID( etc..) only in B4. Not across cells not down the number of rows.
I pasted after the formula was set and BINGO !
I've got a record with approx. 300 columns that has been eating my lunch.
Saved my life.
Thank You
CRIPES..that didn't work either. I 'm back to my 2nd post.
any help is appreciated.
This is just not correct:
"The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain."
Just before leaving the wizard for importing the fixed width column based text file, click the properties button, then you can save the import settings.
The solution offered here is a lousy way of doing something, that has been built in to excel for ages.
See http://www.jkp-ads.com/articles/importtext.asp for the proper way to do it.
There is a way to save the fixed format breaks in Excel using vb. Open up new workbook and turn on the "record macro". Import your file and set the columns breaks. After completing that, go to the developer tab in the workbook in which you created and turn off the "record macro". in the Visual Basic macro for that workbook, you will find the code that Excel recorded to parse out the data you manually imported. The macro will point to the file name that you selected to import. There is a way in visual basic to add more code to allow you to select the file that you want to import in. Will require some knowledge of vb but you can completely automate the import process.
Several things going on here...
1) A fully-automated import process which Curt is well on the way to describing using VB,
2) Saving your fixed-format import by creating a template excel file - which works an is for very large set files in the hundreds of columns - thanks for that,
3) And then Dan's method which is great for small sets as you sometimes are given the format length so I just plug in the numbers - but note your data must be able to be in column A down the rows - using a text editor helps with this as stacks the rows for pasting easily.
Padawan Excel Wizard - (second class)
Love it. I think better than the macros.
you can also import it into access, after yo parse the fixed field, go to Advanced during the import process and save the import layout
import to access then export to excel.
You can also copy and paste a layout right into access from an excel spread sheet by mimicing the name, type, start, width of the way Access does it
Thanks so much. It's my second week at my new job and you just helped me look really good to my new boss.
Clever idea! Finally a way to save Import settings.
Thanks a lot! That saved me a lot of time. Greetings from Germany
Oh my gosh, I think I love you. I was pulling my hair out trying to pull in a file to compare it to IRS requirements and this did the trick. Thanks so much!
Comment #1 is perfect. Did not know you can use the import(get external data) and refresh options under the Data tab to perform the same functionality.
Thank you!!!!
I added some additional features to what you had described above.
For starters I added a 3rd worksheet called Specs where I call out Column Names, field lengths, and other pertinent info. The field lengths populate the second row on the second spreadsheet(Results).
For row 1, starting with the second column of data, I use the following formula to auto populate the starting points to eliminate the "human" element "=$B1+SUM($B2:C2)" Where B1 = 1 and C2 will update as you drag the formula over to your right most column.
For row 3, on the second spreadsheet(Results), I use the following formula to pull in the Column Name "=Specs!A6" The Cell ID(A6 in this example) I had to manually change as my column names were in Column A which wasn't too big of a deal.
All of that was in addition to what was so graciously shared here in this forum post. All I need to do for future fixed width files is change the third spreadsheet(Specs) and all of the values for field lengths, starting points, and if kept in the same format the column names should populate after the first creation of the file. It has made data validation A-mazing! Thank you.
Thank you for this awesome solution.
I can't understand why Microsoft removed the save option from the Import settings. When importing the same text file with many columns over and over, having to define the fields each time is more than just a chore.
The solution presented here solved this irritating omission beautifully. I also added the "specs" worksheet to automatically create the formulas required to split up the raw data into fields. Since the "specs" were a table in columnar format, I had to copy it to another sheet in a row format, i.e. transpose it, so it could be used easily for the automatic field creation.
Worked like a charm! Now all I have to do is copy and paste the text into the raw data sheet cell A1 and the extracted data appears in the "Extracted" tab. Sweet...
Fantastic tips, thanks a ton!
This worked perfectly for what I needed. Thanks!
This really saved the day! I had a very large data set and no access to SAS to export the ASII files. I had only data description with starting and ending values. I imported the data set into cell A1;however, I had to set the ending value of each row as a fixed column width input value in Excel's import function. This way each row represented one observation.
I imported the starting and ending values from the Codebook from the gov't data, calculated the length of each variable by importing this data file (by the way: (ending-starting)+1) For example, I had a variable that started at 2486 that was 11 characters long. I used your Mid(Cell 'sheet1' A1,2486,11) which I then copied down to 35,000 observations so that it would pull the variable from each observation. I then multiplied each value by 1 in a new column (to convert back to number format). I repeated this for each variable I needed. (At first I tried to extract every variable but this created a file that was much too large for Excel to handle and was very time consuming to copy formulas to the thousands of cells) The process above only takes a couple minutes per variable. Thanks Again!
Oh and I used Pivot tables in Excel to organize my data.
its really help for me , thank you a lot.
Brilliant, at first I hadn't realised the significance of the $ signs and locking the input cells, but I have it working now. Thank you, saved me lots of time.
Ingenious solution. I had a fixed length text file with over 500k rows (31 of them). So going thru this each time and ensuring it was right would have been nearly impossible.
Great solution. Thanks!
Awesome, one question though. My Data sheet has, let's say 84,000 rows. When I create my formula of =MID(Data!$A1, B$1,B$2) - how do I get that formula to go down 84,000 rows of B column?
Hover over the bottom right corner of the cell and double-click.
Nice! I habitually have to lay out fixed-length-field, space-padded 465-character call record files with hundreds of thousands of records. I got so irritated with having to place the column positions every time. This worked first time, thanks.
This is amazing! Thank you
Thanks for sharing this. I really helped save a lot of time.
Is there a way to skip some of the fixed width fields. I have a 100 field txt file but I only need the first 6 fields, need to skip 25 fields, then need to next 8 fields and nothing else.
DOH! I think I figured it out.---just don't put the unnecessary column in the work tab!