Trouble importing a non delimited text file

[Liz_I3]

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

 

[GorD]

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

 

[GorD]

With a little playing around I got this

Book1
	A	B	C	D	E
1	Date	Time	Desc	ID	Number
2	10/03/05	06:50:05	OP 8350 OPN-NORMAL OPEN ID	606	01010119-2
3	10/03/05	06:51:33	OP 8350 OPN-NORMAL OPEN ID	606	01010119-6
4	10/03/05	06:53:01	OP 8350 OPN-NORMAL OPEN ID	606	01010119-G
5	10/03/05	12:43:57	COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE
6	10/03/05	12:43:57	JSI 4180 CUSTMR CALLED STAT'N JOHN		01010119-1
7	10/04/05	01:29:42	BM 9425 AUTO CLS. STAY 1 ID	M	01010119-1
8	10/04/05	01:31:10	BM 9425 AUTO CLS. STAY 1 ID	M	01010119-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.

 

[babycody]

Try this solution. You will get a value error on the rows you didn't want.

Book3
	A	B	C	D	E
1	10/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-2	10/03/05	06:50:05	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-2
2	10/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-6	10/03/05	06:51:33	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-6
3	10/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G	10/03/05	06:53:01	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-G
4	10/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-1	10/03/05	12:43:57	#VALUE!	#VALUE!
5	10/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE	10/03/05	12:43:57	#VALUE!	#VALUE!
6	10/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-1	10/04/05	01:29:42	BM 9425 AUTO CLS. STAY 1	ID #M 01010119-1
7	10/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-2	10/04/05	01:31:10	BM 9425 AUTO CLS. STAY 1	ID #M 01010119-2
Sheet1

 

[babycody]

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
	A	B	C	D	E
1	10/03/05 06:50:05 OP 8350 OPN-NORMAL OPEN ID #606 01010119-2	10/03/05	06:50:05	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-2
2	10/03/05 06:51:33 OP 8350 OPN-NORMAL OPEN ID #606 01010119-6	10/03/05	06:51:33	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-6
3	10/03/05 06:53:01 OP 8350 OPN-NORMAL OPEN ID #606 01010119-G	10/03/05	06:53:01	OP 8350 OPN-NORMAL OPEN	ID #606 01010119-G
4	10/03/05 12:43:57 JSI 4180 CUSTMR CALLED STAT'N JOHN 01010119-1	10/03/05	12:43:57	#VALUE!	#VALUE!
5	10/03/05 12:43:57 COMMENT: JSI:HISTORY FROM JULY 20TH TO DATE	10/03/05	12:43:57	#VALUE!	#VALUE!
6	10/04/05 01:29:42 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-1	10/04/05	01:29:42	BM 9425 AUTO CLS. STAY 1	ID #M 01010119-1
7	9/04/05 01:31:10 BM 9425 AUTO CLS. STAY 1 ID #M 01010119-2	9/04/05	01:31:10	BM 9425 AUTO CLS. STAY 1	ID #M 01010119-2
Sheet1

 

[GorD]

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.

 

[Liz_I3]

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]

 

[Liz_I3]

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

 

[mbeejan]

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

 

[Liz_I3]

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