convert txt file to excel

[funkykayli]

I have a .pdf file which I converted to a .txt file. I want to convert the .txt file to an excel file. The problem is the data is all over the place so it won't convert properly. Is there a way to write vb code to take the .txt file and convert to an excel file with the data in the correct column and row? I'm not sure what type of .txt file it is. It is not tab or comma delimited. Here is a sample...


Itemized Statement of Loss - Claim

lmn


Warning: The information contained in this document is confidential and proprietary.
The output below displays only Billable Expenses.
It is for the exclusive use of Company X Mutual and its Policyholder. Do not distribute beyond these groups.


The report type is ’SLR - Itemized Statement of Loss (WC) - Claim’.
Date As Of: 02/29/2008

CRITERIA
Account Number
includes:
5555555 - COMPANY B
Policy Effective Date
starts at 05/01/2001 -- inclusive

OPTIONS
Subtotal By
equals 03 - Eff.Date/Pol.Num Logo
equals Company X - Company X Money Computation
equals N - Net Financial View
equals 1 - External Overview Page
equals Yes

SECURITY
{Non Policy Market} IN (’01’,’03’,’0E’,’0J’,’0K’)
OR {Account Servicing Business Group} IN (’0001’,’0004’,’0011’,’0017’,’0021’,’0027’,

’0031’,’0062’,’0065’,’0066’
)
OR {Servicing Bus. Grp. Id} IN (’0001’,’0004’,’0011’,’0017’,’0021’,’0027’
,
’0031’,’0062’,’0065’,’0066’
)


Report Generated by Company X Mutual Group Information Warehouse Environment


Itemized Statement of Loss - Claim

lmn


Warning: The information contained in this document is confidential and proprietary.
The output below displays only Billable Expenses.
It is for the exclusive use of Company X Mutual and its Policyholder. Do not distribute beyond these groups.


LOB: Workers Compensation Minimum Policy Effective Date: 05/01/2001
Valuation Date: 02/29/2008
Account Numbers: 5555555 Run Date: 03/11/2008


Claim Number Claim ID Claimant Name Status Policy Inc Indem Inc Med Inc Exp Total Inc
Loss Date Report Date Close Date Tenure Effective Date Paid Indem Paid Med Paid Exp Total Paid
Jurisdiction State Location Code/Desc O/S Reserve
Nature of Injury Part of Body Catalyst Cause
Supp Nature of Injury Supp Part of Body

Effective Date: 05/01/2001
Policy Number: XXX-20X-084388-21-22 - COMPANY XYZ


WC 303646413 893064285 PRZYBYLKOWSKI,JOHN Closed XXX-20X-084388-21-22 $0 $243 $30 $273
07/11/2001 07/17/2001 07/17/2001 9 05/01/2001 $0 $243 $30 $273
PA 000200-GENALITE JESSUP PA $
43-PUNCTURE 35-HAND 4120-RODS 0RA-STRUCK BY/AGAINST OBJECTS
0175-PUNCTURE 0330-HAND


WC 303647110 813093985 JIMENEZ,LUIS Closed XXX-20X-084388-21-22 $0 $185 $21 $206
08/02/2001 08/03/2001 08/03/2001 1 05/01/2001 $0 $185 $21 $206
PA 000200-GENALITE JESSUP PA $
25-FOREIGN BODY 14-EYE(S) 8000-FOREIGN OBJ. 0RC-STRUCK BY FLYING OBJECT-EYE INJURY
0235-FOREIGN BODY 0130-EYE


WC 303648323 653147685 CICIO,NICHOLAS Closed XXX-20X-084388-21-22 $0 $3,038 $375 $3,413
07/20/2001 09/05/2001 09/09/2002 3 05/01/2001 $0 $3,038 $375 $3,413
PA 000200-GENALITE JESSUP PA $
28-FRACTURE 35-HAND 2200-HAND TOOL/NO POWE 0YB-HAND TOOLS
0210-FRACTURE 0330-HAND


WC 303648750 483165785 HANSEN,HARRY J Closed XXX-20X-084388-21-22 $0 $526 $97 $623
09/17/2001 09/17/2001 10/24/2001 0 05/01/2001 $0 $526 $97 $623
PA 000200-GENALITE JESSUP PA $
10-CONTUSION 32-ELBOW 4700-TREES 0RA-STRUCK BY/AGAINST OBJECTS
0160-BRUISE/CONTU 0313-ELBOW


Report Generated by Company X Mutual Group Information Warehouse Environment
Page



Itemized Statement of Loss - Claim

lmn


Warning: The information contained in this document is confidential and proprietary.
The output below displays only Billable Expenses.
It is for the exclusive use of Company X Mutual and its Policyholder. Do not distribute beyond these groups.


LOB: Workers Compensation Minimum Policy Effective Date: 05/01/2001
Valuation Date: 02/29/2008
Account Numbers: 5555555 Run Date: 03/11/2008


Claim Number Claim ID Claimant Name Status Policy Inc Indem Inc Med Inc Exp Total Inc
Loss Date Report Date Close Date Tenure Effective Date Paid Indem Paid Med Paid Exp Total Paid
Jurisdiction State Location Code/Desc O/S Reserve
Nature of Injury Part of Body Catalyst Cause
Supp Nature of Injury Supp Part of Body

Effective Date: 05/01/2001
Policy Number: XXX-20X-084388-21-22 - COMPANY XYZ


WC 303649762 363218985 CARTEGNA,JOHN A Closed XXX-20X-084388-21-22 $0 $138 $24 $162
10/12/2001 10/15/2001 10/15/2001 0 05/01/2001 $0 $138 $24 $162
PA 000200-GENALITE JESSUP PA $
40-LACERATION 36-FINGER(S) 4100-METAL 0HC-PUSHING,PULLING-MANUAL HANDLING
0170-CUTS 0340-FINGER


WC 440476064 42990485 DEMAREE,DAVID D Closed XXX-20X-084388-21-22 $1,650 $9,761 $383 $11,794
05/24/2001 05/29/2001 10/18/2002 3 05/01/2001 $1,650 $9,761 $383 $11,794
IN 000300-PRAIRIE INDUSTRIES LEBANON IN $
16-DISLOCATION 38-SHOULDER 0400-BODILY MOTION 0AA-FALLS ON SAME LEVEL
0190-DISLOCATION 0450-SHOULDER


WC 440478534 643071685 ALANIS,AGUSTIN Closed XXX-20X-084388-21-22 $0 $2,899 $156 $3,055
07/18/2001 07/20/2001 02/18/2002 2 05/01/2001 $0 $2,899 $156 $3,055
IN 000300-PRAIRIE INDUSTRIES LEBANON IN $
28-FRACTURE 36-FINGER(S) 4000-MECH. POWER 0NC-CAUGHT IN MACHINE DRIVE MECHANISMS
0400-MULTIPLE 0340-FINGER


WC 440482976 373237985 SHIVES,LESLIE Closed XXX-20X-084388-21-22 $0 $1,239 $128 $1,367
10/18/2001 10/23/2001 10/23/2001 21 05/01/2001 $0 $1,239 $128 $1,367
IN 000300-PRAIRIE INDUSTRIES LEBANON IN $
13-CRUSHING 36-FINGER(S) 6700-NOC 0NL-CAUGHT IN/UNDER/BETWEEN
0165-CRUSH INJURY 0340-FINGER


Report Generated by Company X Mutual Group Information Warehouse Environment
Page




 

[MarkAndrews]

Maybe you can use this which Richard S helped me with?

NB - change file locations in the code

Sub OpenTXTAsExcel()
Const NEW_DRIVE As String = "G" ':\Raw\Product Management\Jen Delaney\Vendor Reports\Lexmark\Weekly Stock And Sales\Kingfield Reports\"
Const NEW_DIR As String = "G:\Raw\Product Management\Jen Delaney\Vendor Reports\Lexmark\Weekly Stock And Sales\Kingfield Reports\"
'Const NEW_DRIVE As String = "H"
'Const NEW_DIR As String = "H:\MarkAndrews\"
Dim currDir As String
Dim sFullName As String, sNewName As String
Dim myArray
myArray = Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), Array(41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 2), Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2), Array(66, 2), Array(67, 2), Array(68, 2), Array(69, 2), Array(70, 2), Array(71, 2), Array(72, 2))
currDir = CurDir
ChDrive NEW_DRIVE
ChDir NEW_DIR
sFullName = Application.GetOpenFilename("TXT Files (*.txt),*.txt")
If sFullName = "" Then MsgBox "No File Selected": Exit Sub
sNewName = Left$(sFullName, Len(sFullName) - 3) & "xls"
FileCopy sFullName, sNewName
Workbooks.OpenText Filename:=sNewName, DataType:=xlDelimited, _
    comma:=True, fieldinfo:=myArray
    
ChDrive Left$(currDir, 1)
ChDir currDir
End Sub
'Const NEW_DRIVE As String = Directory to look in
'Const NEW_DIR As String = Filepath the file is to be saved in
'

 

[funkykayli]

It worked by converting the data to excel but everything is all over the place. I was looking for a way to place the data in separate columns.

 

[passinthru]

I think the PDF to text conversion left you with a mess that no program can untangle. There are too many different types of data.

You might be able to get some of the data into the proper columns by doing a Text To Columns conversion (look under Data on the menu bar), but that's going to wreak havoc with some of the other data.

I think you'd be better off using the Foxit PDF reader to copy the data directly from PDF and pasting it in Excel. Foxit is better about letting you copy tables.

http://www.foxitsoftware.com/

Actually, Foxit is better than Adobe in ALL ways.