Trouble importing a non delimited text file

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
Hi
I am attempting to import a text file into excel. The text file is not comma delimited. It is from a mainframe output so it really is not delimited at all. When I import it into excel the whole file comes into column A. This would be ok but I am not sure how to separate it into separate fields from there. I need to turn the below 1 field into 5. They are Date, Time Description ID and Number. Using the first row as and example
The date field would be 10/03/05 , Time 06:50:05 Description would be every thing from the end of the time to ID. ID is ID#606 and the Number is 01010119-2. As you can see below all the rows are not set up exactly the same row 7 has no IDxxx. Some rows are total blanks. Other rows have banners etc the only row I am interested in are the ones that begin with a date.
Any Ideas would be appreciated. Is there a way to import it directly form the text file into the separate fields rather than importing it all into column A first?

Thanks
L

10/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-2

10/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-6

10/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G

10/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-1

10/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE

10/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-1

10/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-2
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
The first thing I would do, would be to insert a new column A and number this 1 to the last record. Then I'd sort the data by (now) column B. This would give you all the records that start with a date together. You can delete all the ones that are of no interest to you. If required you can then get back to the original order of data by resorting on column a.

From there you would use data text to columns, but you may have to do this several times and possibly keep sorting the data to get like formatted rows together.

Does that get you started
 
Upvote 0
With a little playing around I got this
Book1
ABCDE
1DateTimeDescIDNumber
210/03/0506:50:05OP 8350 OPN-NORMAL OPEN ID60601010119-2
310/03/0506:51:33OP 8350 OPN-NORMAL OPEN ID60601010119-6
410/03/0506:53:01OP 8350 OPN-NORMAL OPEN ID60601010119-G
510/03/0512:43:57COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE
610/03/0512:43:57JSI 4180 CUSTMR CALLED STAT'N JOHN01010119-1
710/04/0501:29:42BM 9425 AUTO CLS. STAY 1 IDM01010119-1
810/04/0501:31:10BM 9425 AUTO CLS. STAY 1 IDM01010119-2
Sheet1


The first two columns were straight forward, using fixed width, then the 4&5th columns together using delimited and the # as the delimiter ( thats why its missing from the ID. Then separate 4 and 5 using space delimiter, then tidy up any that don't follow the rules - identified by sorting the data.
 
Upvote 0
Try this solution. You will get a value error on the rows you didn't want.
Book3
ABCDE
110/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-210/03/0506:50:05OP 8350 OPN-NORMAL OPENID #606 01010119-2
210/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-610/03/0506:51:33OP 8350 OPN-NORMAL OPENID #606 01010119-6
310/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G10/03/0506:53:01OP 8350 OPN-NORMAL OPENID #606 01010119-G
410/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-110/03/0512:43:57#VALUE!#VALUE!
510/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE10/03/0512:43:57#VALUE!#VALUE!
610/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-110/04/0501:29:42BM 9425 AUTO CLS. STAY 1ID #M 01010119-1
710/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-210/04/0501:31:10BM 9425 AUTO CLS. STAY 1ID #M 01010119-2
Sheet1
 
Upvote 0
I need to make a slight modification, because I didn't take into consideration the different lengths the date might have. i.e. 9/25/05 has 7 characters while 10/25/05 has 8 characters. The changes I made to column B and C should fix that.
Book3
ABCDE
110/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-210/03/0506:50:05OP 8350 OPN-NORMAL OPENID #606 01010119-2
210/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-610/03/0506:51:33OP 8350 OPN-NORMAL OPENID #606 01010119-6
310/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G10/03/0506:53:01OP 8350 OPN-NORMAL OPENID #606 01010119-G
410/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-110/03/0512:43:57#VALUE!#VALUE!
510/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE10/03/0512:43:57#VALUE!#VALUE!
610/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-110/04/0501:29:42BM 9425 AUTO CLS. STAY 1ID #M 01010119-1
79/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-29/04/0501:31:10BM 9425 AUTO CLS. STAY 1ID #M 01010119-2
Sheet1
 
Upvote 0
Liz_I3 said:
Hi
Other rows have banners etc the only row I am interested in are the ones that begin with a date.

Babycody,
if using formula method you would also need to incorporate a soloution for rows 4 and 5, as they also begin with dates.
 
Upvote 0
Thank GorD
Yes this is a big help. I have my sheet working using variations of your fomulas on my spreadsheet. But now I want to turn them into VBA so that the user can import and run a macro. I have a lot done, but having difficultiles getting the corrrect syntax for the below formula. It does not like my Search??

Thanks for you help
L

Sub ThirdField()
Dim i As Integer
Dim st As String
Dim z As String
z = ActiveWorkbook.ActiveSheet.Name
st = "xx"
i = 1
Do While Len(st) > 0
i = i + 1
st = ActiveWorkbook.Worksheets(z).Cells(i, 3).Value
ActiveWorkbook.Worksheets(z).Cells(i, 3).Formula = "=MID(C" & i",Search("ID","C" & i,1)-1)"
Loop
End Sub
[/code]
 
Upvote 0
Sorry this line above should read
ActiveWorkbook.Worksheets(z).Cells(i, 4).Formula = "=MID(C" & i",Search("ID","C" & i,1)-1)"

I want to read the text in column C so the data I am extracting from C would be in column D.

when the code is run SEARCH is highlighted and I get the a Compile error
Expected end if statement

L
 
Upvote 0
Problem with delimited text file

Hi Liz

I usually use a small trick to in import any text format data into excel and convert them the way I want it to be. First copy the text data from the single column into Notepad. The open the Notepad file using Excel by delimitting with space identifier. You will have your data in separate cells. Some prosessing is required to group some cells based on data structure. I normally insert a colunm at A and give a serial number statrting from 1 to all the rows in my data range, even the blank rows within the data range. By sorting over appropriate headers, which I include, I can manipulate blocks of comparable data. After deleting unrequired rows, I sort again on colunm A to restore the original order.

Alternative, I use mega formula to cut and reformat the text into several cells

Regards

Prem, Mauritius
 
Upvote 0
Hey I finally got it working , it took me so long for such a tiny bit of code, but heck it works. here it is. If anyone can improve on it please post back.

thanks for you post mbeejan, but I cannot use the space delimiter because it would separate it into too many columns.

Sub ThirdField()
Dim i As Integer
Dim st As String
Dim z As String

z = ActiveWorkbook.ActiveSheet.Name
st = "xx"
i = 1
z = ActiveWorkbook.ActiveSheet.Name
st = "xx"
i = 1

Do While Len(st) > 0
i = i + 1
st = ActiveWorkbook.Worksheets(z).Cells(i, 3).Value
ActiveWorkbook.Worksheets(z).Cells(i, 4).Formula = "=MID(C" & i & ",1,search(" & Chr(34) & "ID" & Chr(34) & ",C" & i & ",1)-1)"

Loop

End Sub
 
Upvote 0

Forum statistics

Threads
1,224,394
Messages
6,178,338
Members
452,841
Latest member
GenAkaman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top