EXCEL Import

[Guest.Visitor]

Can anyone walk me through import a .PRN file into EXCEL? I am completely stupid on this one, but someone at a client site wants to try to do this. Thanks in advance.... Eric Hill Muscle Shoals, AL hille@wlv.com

[Guest.Visitor]

Eric, I don't think anyone would need to walk you through it. Just do it! Excel should sense that you are bringing in a text file. At this point it should bring up the import text file screen and it will walk you through the column parsing part. It really is quite simple. Bill

[Guest.Visitor]

Thanks, Bill. I'll give it a try..... Eric

[Guest.Visitor]

If you have done any VBA programming (the language behind Office Macros), I could post a set of macros that does this activity automatically. Bill

[Guest.Visitor]

Sorry for such a belated response. I only infrequently leave the AS/400 forums to read the other boards. I?m very experienced in downloading .PRN files into Excel and Access. If your .PRN files aren?t comma delimited (i.e., .CSV) then the response you received would only help you open the file in Excel, but would not help you parse the data into an organized flat-record table. This database format greatly facilitates analysis of the data. I?m a CPA and VBA programmer who composes macros that automatically parse print (or any form of ASCII text) files into Excel or Access. The macros are customized for the specific report and can even handle reports with very complex and irregular record layout structures. I?m currently composing a business plan and am looking to include as many different reports as possible in the ?Proof of Concept? section. I plan on offering the service for a few hundred dollars per report, but am willing to offer it for free if I can use your report in my business plan, and if you?re willing to provide a brief testimonial regarding the service. I only require a few pages of a report to establish the record layout pattern (including the last page). I would prefer the ASCII file MountRainier@Hotmail.com but you can also just fax the pages of the report ((800) 783 7880). Feel free to black out any sensitive/confidential data. I have a rough PowerPoint demo presentation I can send you if you want more information.

[Guest.Visitor]

Mark, I?m very experienced in downloading .PRN files into Excel and Access. Me Too! If your .PRN files aren?t comma delimited (i.e., .CSV) then the response you received would only help you open the file in Excel, but would not help you parse the data into an organized flat-record table. This database format greatly facilitates analysis of the data. This statement is misleading. If the import file wizard is installed in Excel (the default may be not to install it) and an ASCII text file is opened, the wizard allows the user to parse the data into columns and set column sizes and characteristics. I prefer to use text files rather than .CSV files because a quote or apostrophe anywhere in the data will throw the import for a loop. I?m a CPA and VBA programmer who composes macros that automatically parse print (or any form of ASCII text) files into Excel or Access. The macros are customized for the specific report Yeah, I've done that also. and can even handle reports with very complex and irregular record layout structures. I prefer to use Monarch if the layout is too complex. I plan on offering the service for a few hundred dollars per report A few hundred dollars for about a half-hour's work? I'm gonna have to start my own business on the side doing this. Bill

[Guest.Visitor]

Eric - I am totally stupid when it comes to this also, but have gotten into it a bit over the past few months. My practice is usually to try and make a work file that is all character fields. This way I can throw out column headings and then the data. When my report is generated in this work file, I then do a CPYTOPCD and slap a .PRN extension onto the file name. We have a shared folder the users can get to on another drive. The users can then open EXCEL and open this .PRN file. As mentioned, this takes the user thru a very brief import wizard and they're done. I'd be VERY interested in seeing some of these macro commands and codes, as I'm not at all familiar with the VBA world. Mike

[Guest.Visitor]

Bill, Excel's Import File Wizard is fine if the file is delimited or fixed width. Eric was asking about a .PRN file which denotes a production report intended to be printed rather than downloaded into another application. Most production reports have irregular record layout structures which cause the downloaded data to be useless to work with. You're correct that report mining applications such as Monarch, DataImport, ValetMiner, etc., have the ability to rearrange the data into a columnar format. Unfortunately, these applications are expensive (Monarch = $699, Single User Professional Edition), require training (they offer 2-day courses (also expensive)), and often lack the flexibility to create data extraction models (a.k.a., templates or masks) for reports with complex record layout structures. "A few hundred dollars for this service?" You cannot underestimate the value of information -- it's the one strategic resource common to every organization. Besides, I was offering the service to Eric for free to build up my business plan. "Carpe Data"

[Guest.Visitor]

Mike, Sorry but the macros (actually algorithms of formulae, filters and VBA code) I create to parse a report into Excel or Access are designed for a specific report and will not work for any other report. However, I am looking for examples to use in the "Proof of Concept" section of my business plan. Send me a copy of your most complex reports (to MountRainier@Hotmail.com) and I'll send you back the corresponding algorithms to run in Excel. All I ask in return is a few words of praise to quote in my business plan. Mark

[Guest.Visitor]

Mike, Ask and you shall receive! Here is what should be the basic code:

 ' ' ExpenseProcess - A system to convert downloaded text spool files ' into separate sheets in a workbook. Option Explicit Dim pvarFileToOpen As Variant Dim pblnClearThem As Boolean 'Used to control when to clear all worksheets Dim pintCounter As Integer '------------------------------------------------------------------------------ ' getFileName - Takes in a qualified file name and outputs ' just the file name. '------------------------------------------------------------------------------ Function getFileName(strQualifiedName As Variant) As String Dim intEndPos As Integer, intBegPos As Integer intBegPos = Len(CurDir$) + 2 'The curr dir is part of the name. intEndPos = InStr(1, strQualifiedName, ".txt") - 1 'Remove extension. getFileName = Mid$(strQualifiedName, intBegPos, (intEndPos - intBegPos + 1)) End Function '------------------------------------------------------------------------------ ' LoadTheSheet - Put the File Contents Into the Sheet. '------------------------------------------------------------------------------ Sub LoadTheSheet() Dim intRowNo As Integer Dim strLastLine As String, strLineIn As String Cells.Font.Name = "Fixedsys" Range("A1").Select Application.ScreenUpdating = False intRowNo = 0 You would substitute your own file open logic instead of the following line. Open pvarFileToOpen(pintCounter) For Input As #1 Do While Not EOF(1) Line Input #1, strLineIn intRowNo = intRowNo + 1 Cells(intRowNo, 1).Value = strLineIn strLastLine = strLineIn Loop Close #1 ParseColumns SetNegatives Application.ScreenUpdating = True End Sub '------------------------------------------------------------------------------ ' ParseColumns - Split the Data in the One Column into Several. '------------------------------------------------------------------------------ Sub ParseColumns() Dim arrColumns As Variant I hard code the column lengths here. Parsing text to columns for the reports I was processing wasn't always consistent. This ensures consistency. arrColumns = Array(Array(0, 1), Array(30, 1), Array(40, 1), Array(50, 1), _ Array(60, 1), Array(70, 1), Array(80, 1), Array(90, 1), Array(100, 1), _ Array(110, 1), Array(120, 1)) Range("a7:a200").Select 'Data always started at row 7 Selection.TextToColumns Destination:=Range("A7"), DataType:=xlFixedWidth, _ fieldinfo:=arrColumns Selection.Columns("a").AutoFit Range("a1").Select End Sub '------------------------------------------------------------------------------ ' SetNegatives - Convert from trailing to leading minus sign. '------------------------------------------------------------------------------ Sub SetNegatives() Dim cellPointer As Range, cellSelected As Range Dim varFormula As Variant Range("a1").Select Set cellPointer = ActiveCell.SpecialCells(xlLastCell) Range("a10", cellPointer).Select Set cellSelected = Selection For Each cellPointer In cellSelected varFormula = cellPointer.Formula If IsNumeric(varFormula) And Right(varFormula, 1) = "-" Then varFormula = "-" & Left(varFormula, Len(varFormula) - 1) cellPointer.Formula = varFormula End If Next Range("a1").Select End Sub 

I tried to give you all of the relevant logic. Obviously, not everything is here, but should give you a good base upon which to expand. Bill