import txt to excel

[Deek45]

Good afternoon,
I am trying to import a large txt file into to excel, the issue is that the information is not line up in a manor which text to column can be accurate. Please refer to the sample below. The information from line to line is in the same formate but does not line up column to column.

00/15-04-065-10W6/0 DEVON ET AL REDROCK 15-4-65-10 0427245 A20A Stoneham Drilling Inc. 10 31-Dec-2010 07:00:00 PM Grande Prairie 0K29 Devon Canada Corporation Drill To LD
00/07-36-074-06W4/0 DEVON NEC BP 7B KIRBY 7-36-74-6 0426605 0X84 Nabors Drilling Limited 88 01-Jan-2011 01:30:00 AM Bonnyville 0BK8 Devon NEC Corporation Drill To LD
AA/09-02-075-06W4/0 DEVON NEC BP 9A KIRBY 9-2-75-6 0425186 0X84 Nabors Drilling Limited 96 01-Jan-2011 06:15:00 AM Bonnyville 0BK8 Devon NEC Corporation Drill To LD
00/14-30-087-11W5/0 CDN FOREST OTTER 14-30-87-11 0425041 0Z41 Ensign Drilling Inc. 523 31-Dec-2010 01:30:00 AM St. Albert 0T82 Canadian Forest Oil Ltd. Drill To LD
02/15-30-024-25W4/0 ECA ENTICE 15-30-24-25 0425608 A57H Predator Drilling Inc. 8 01-Jan-2011 09:30:00 PM Midnapore 0026 EnCana Corporation Set Surface
00/15-28-024-27W4/0 ECA ENTICE 15-28-24-27 0424574 A08C Savanna Drilling Corp. 448 02-Jan-2011 10:15:00 AM Midnapore 0026 EnCana Corporation Drill To LD
00/08-01-025-23W4/0 ECA PARFLESH 8-1-25-23 0424359 A57H Predator Drilling Inc. 8 01-Jan-2011 09:00:00 AM Midnapore 0026 EnCana Corporation Set Surface
00/11-07-027-24W4/0 ECA ENTICE 11-7-27-24 0419005 A57H Predator Drilling Inc. 9 02-Jan-2011 01:00:00 AM Midnapore 0026 EnCana Corporation Set Surface
00/10-18-027-24W4/0 ECA ENTICE 10-18-27-24 0428079 A57H Predator Drilling Inc. 9 01-Jan-2011 07:15:00 PM Midnapore 0026 EnCana Corporation Set Surface

 

[alan.m]

Hi Deek45,
you've got a real problem.
Where are you getting your data from and can you go back and export it
TAB DELIMITED? Some programs (eg: Lotus Notes) will let you export to Lotus 123 format and that would be great to!

 

[Ruddles]

If you have a CSV export option, that's good too.

 

[Deek45]

The example above is the only format that I can get the data.

The structure of the data is always the same, does that make a difference?

 

[Ruddles]

Agreed, it does have a very regular structure:-

• some sort of identifier followed by a space
• some words
• four numerics with hyphen separators
• a 7-digit numeric value
• a field alpha-numeric-numeric-alpha
• some words
• a number (1-2 digits?)
• a date and time AM/PM
• some words
• a postal code? (always four characters)
• some words
• a string which looks to be in some sort of standard format

You're going to have to delve into VBA, I think, if you want a process with enough intelligence to identify which fields look like four numerics with hyphen separators, which fields look like 7-digit numbers, which ones are valid date/times, etc. I would probably tackle it by importing the whole lot as it stands and then writing code to shuffle the fields in each row left and right using the identifiable fields to line everything up.

Are you sure the fields are separated by spaces and not TAB characters? That would make the process a lot easier.

 

[Deek45]

Agreed, it does have a very regular structure:-

• some sort of identifier followed by a space
• some words
• four numerics with hyphen separators
• a 7-digit numeric value
• a field alpha-numeric-numeric-alpha
• some words
• a number (1-2 digits?)
• a date and time AM/PM
• some words
• a postal code? (always four characters)
• some words
• a string which looks to be in some sort of standard format

You're going to have to delve into VBA, I think, if you want a process with enough intelligence to identify which fields look like four numerics with hyphen separators, which fields look like 7-digit numbers, which ones are valid date/times, etc. I would probably tackle it by importing the whole lot as it stands and then writing code to shuffle the fields in each row left and right using the identifiable fields to line everything up.

Are you sure the fields are separated by spaces and not TAB characters? That would make the process a lot easier.

The actual txt file is broken into columns. I could send you and example if you like to see.

 

[Ruddles]

Check your PM.