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!
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!
please post sample data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
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}
Replace: \1\t\2\t\3\t\4\t\5\t\6\t\7
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
Is this a one-time conversion or something you'll need to do on a recurring basis?
ASKER
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.
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.