Excel 2007 import question

Posted on 2011-10-27
Medium Priority
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
  • 3
  • 2
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 37039468
please post sample data
LVL 10

Accepted Solution

mark_harris231 earned 2000 total points
ID: 37039801
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.

Author Comment

ID: 37040040
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 10

Expert Comment

ID: 37040303
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?


Author Comment

ID: 37073140
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

ID: 37073178
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.

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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