Excel 2007 import question

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
Who is Participating?
mark_harris231Connect With a Mentor Commented:
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.
Swapnil NirmalManager, Audit AnalyticsCommented:
please post sample data
rinkydinkAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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 (www.pnotepad.org), which uses the first format.

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

rinkydinkAuthor Commented:
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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.