Unconfigured Ad Widget

**Guest.Visitor** · 06-09-2000, 09:07 AM

EXCEL Import

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** · 06-09-2000, 11:03 AM

EXCEL Import

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

**Guest.Visitor** · 06-09-2000, 01:52 PM

EXCEL Import

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** · 08-22-2000, 01:36 PM

EXCEL Import

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** · 08-22-2000, 02:24 PM

EXCEL Import

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** · 08-22-2000, 03:11 PM

EXCEL Import

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** · 08-22-2000, 04:05 PM

EXCEL Import

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** · 08-22-2000, 04:19 PM

EXCEL Import

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** · 08-22-2000, 04:26 PM

EXCEL Import

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

**Guest.Visitor** · 08-22-2000, 04:38 PM

EXCEL Import

Mark, 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. We ARE talking abou the same thing. "Most" production reports are of the headings/detail/summary variety in which the Excel user would usually be interested only in the detail information, ignoring the other rows in the report. This is still doable, the user has full control with column parsing using the wizard and parses based upon the detail line layout and then later deletes the "noisy" rows if necessary. Programmatically, it will usually be easier; load data based upon it's particular characteristics. Bill

**Guest.Visitor** · 08-23-2000, 10:04 AM

EXCEL Import

Bill, I realize that even non-delimited files can be opened using Excel's Text Import Wizard, but the "doable" process you describe to clean up the report and remove "noisy" rows can be very time-consuming, especially for large reports, and especially when you have to perform that clean-up every day. My solution not only parses the current day's report but every subsequent days' report as well. The algorithms work off of pattern-recognition rules similar to the report mining application you mentioned, Monarch. Thus, even if my macro only saves the 30 minutes you stated earlier, that's still 182 hours per year saved. That's quite a savings, especially for a solution that I'm currently offering for free. Mark

Unconfigured Ad Widget

Announcement

EXCEL Import

EXCEL Import

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment