Excel 2007 import question

Posted on 2011-10-27
Last Modified: 2012-05-12
Hi -

I was handed a 6MB mainframe export text file that I needed to import into Excel 2007. The import goes fine but I have data that bleeds over under the R AND E (which should have come in as seperate columns) . I have attached a pic.

I have tried every variation of importing into Excel to no avail, I had nothing to do with the mainframe export and have no clue as to what format this was 'officially' exported in or even what type of system it came from.

Is there a way to seperate the R into a column, the E into a column and the rest into a seperate column? It would not be a problem if I didn't have like almost 1000 of these sections.

I thought of just creating a R and E column but then I have a random R and E hanging around in the first row of each of the almost 1000 sections I have of this BKMP data.

Any ideas I will surely welcome.

Thanks! screencap
Question by:rinkydink
    LVL 11

    Expert Comment

    by:Swapnil Nirmal
    please post sample data
    LVL 10

    Accepted Solution

    Mainframe exports are usually fixed-width columns (i.e., x number of characters per column).  You generally see this bleed when text fills an entire column and is flush against the start of the next column.  If feasible, you can open a blank Excel document, then use the From Text import on the Data tab (Excel 2007) and map the column breaks.  If the number of columns make that prohibitive or if this needs to be a repeatable process, you might need to do some pre-processing using Regular Expressions or similar patterning to insert a tab or other character between columns so that Excel can recognize the breaks.
    LVL 1

    Author Comment

    I attached a sample of the export, with personal data replaced with random numbers, I have to do that or I get electrocuted I think LOL

    But this is the format of the text file where the problem occurs.

    Thanks! bk.txt
    LVL 10

    Expert Comment

    If interpreted correctly:
    1st column = 10 characters
    2nd column = 25 characters
    3rd column = 22 characters
    4th column = 3 characters
    5th column = 3 characters
    6th column = 3 characters
    7th column = 3 characters
    8th column = remaining characters

    As stated previously, you could import the data from a blank Excel document and map the columns using the import data functionality (what version of Excel are you running?).  Alternately, you could use a regular expression to insert tabs, which Excel will honor when opening:

    Match: (.{10})(.{25})(.{22})(.{3})(.{3})(.{3})(.{3})(.*)
    Replace: \1\t\2\t\3\t\4\t\5\t\6\t\7\t\8

    Depending on your regular expression engine, the \ in front of the numbers may need to be replaced with $: $1\t$2\t$3\t$4\t$5\t$6\t$7\t$8.  I use Programmer's Notepad (, which uses the first format.

    Is this a one-time conversion or something you'll need to do on a recurring basis?

    LVL 1

    Author Comment

    Sorry for the delayed response. I'm gonna give the pnotepad a try [thanks for the link] but at this point I have it pretty much cleared up with the people who were doing the exporting from the mainframe....AKA people who shouldn't be doing exporting from a mainframe LOL

    Thanks again.

    LVL 10

    Expert Comment

    Great - always nice to be able to address in the source whenever possible, but well aware that it can be an uphill battle sometimes.  Hopefully, Programmer's Notepad and regex adds value to your tech toolkit.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now