Link to home
Start Free TrialLog in
Avatar of rinkydink
rinkydink

asked on

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! User generated image
Avatar of Swapnil Nirmal
Swapnil Nirmal
Flag of India image

please post sample data
ASKER CERTIFIED SOLUTION
Avatar of mark_harris231
mark_harris231
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rinkydink
rinkydink

ASKER

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
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?

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.