Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Massachusetts, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Fixed Width (Excel 97 )

    "The trickiest part of using Excel is preventing it from stripping off leading zeroes from numbers. To ensure the data has the correct number of characters we must use the format cells feature." A column can be custom defined for numbers. Is it possible to build a form, or run a macro, that would allow export of a non delimited text file? One where the text columns would be expected to have a specific number of characters (30 for address, 20 for town) even when they are blank? If you are thinking mainframe file you are correct. No packed fields, just characters A-Z, 0-9. "Fields are aligned in columns with spaces between each field' Text Import Wizard.
    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export Fixed Width (Excel 97 )

    You can do it via macro (See OpenText Method), or with the text import wizard, or even data - text to columns (which calls the import wizard)

    If you import as one column in excel, then use the macro recorder when you do a data - text to column - fixed width, you can get the code created for you to use with OpenText method and have the columns delimited how you want.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Yes, a macro could be used to export the data. We would need a sample of the workbook (sanatized of any proprietary data) with a description of exactly what you want outputted for each column before we could help you write the macro.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    Massachusetts, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    So I could have a user enter values into the columns, by hand or by form, then run some modification of "StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1)......" and EXPORT the worksheet contents in a fixed format?
    My experience with VB is running "record new macro" and replacing the input file with MyFile = Application.GetOpenFilename("Textt Files,*.txt"), Filename:=MyFile, _
    if you get my drift...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    I think Steve misread your message and thought you wanted to IMPORT a file. See my other message.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Jul 2001
    Location
    Massachusetts, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Awesome. Attached is the first few bytes. It has the wide open spaces of a mainframe format. Importing it is easy in Excel. As stated, the goal would be to input a few fields into Excel. Then export each column as a fixed width as one record per row. The actual total of columns would be 1500, but If I can learn to do it for 80. Thanks

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Unfortunatley, that is not a lot of help. Could you attach a workbook with dummy data and a description of what you want output. The workbook can be used for testing the code, and I really don't want to spend the time trying to figure out what the fields are and how wide they are. I also can't tell from 000-00-0000 what is in the Excel cell.
    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export Fixed Width (Excel 97 )

    Legare,
    Yes you are correct, I misread. I thought the program had exported a fixed width file and we were going to import that into excel.

    Steve

  9. #9
    New Lounger
    Join Date
    Jul 2001
    Location
    Massachusetts, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Mea Culpa. I did not think it through.
    Have attached a spreadsheet which shows
    a title row
    a data row with entries.

    Below that is what the record should look like when saved as a file (text if appropriate). I used x's in the first line to insure the correct length. The line in Blue would be the actual appearance in most editors or notepad. The physical record would be 89 bytes long and composed of A-Z, 0-9 and spaces. If there was a numeric field in the example I believe that custom formatting would control the numeric data's width as needed. The text is the hard part, plus exporting it without delimeters.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Is this really what your spreadsheet looks like????? Do you really have the Social Security Number spread accross five cells? Is there really a column named SSN-Filler? What does it contain? What is in the EMPLOYEE-ID column? Is it a text value (like the EMPID which is shown) or is it a numeric. I ask because if we write code for a Social Security Number that is spread accross five columns, that code is not going to work if it is a numeric value in one column formatted to insert the dashes. The COBOL Data Division code shows exactly what the output needs to look like, but I am not sure I know what the spreadsheet looks like.
    Legare Coleman

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export Fixed Width (Excel 97 )

    Without coding you could do "relatively easily" manually. If you wanted a macro to automatate, you could do something similar in code
    In a new sheet in A2 enter this formula:

    LEFT('One Record'!A2&REPT(" ",5),5)&LEFT('One Record'!B2&REPT(" ",5),5)&TEXT('One Record'!C2,"000")&'One Record'!D2&TEXT('One Record'!E2,"00")&'One Record'!F2&TEXT('One Record'!G2,"0000")&" "&LEFT('One Record'!I2&REPT(" ",12),12)&LEFT('One Record'!J2&REPT(" ",30),30)&LEFT('One Record'!K2&REPT(" ",15),15)&LEFT('One Record'!L2&REPT(" ",10),10)

    Copy it down the column as many rows as the data.
    Then save this worksheet as a text file

    This assumes that the SS is broken up into separate columns and are all numbers. If the questions Legare raises are valid, the formula will have to be modified.
    Steve

  12. #12
    New Lounger
    Join Date
    Jul 2001
    Location
    Massachusetts, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    Your observation is correct, the Cobol layout does show the pattern. It also shows that each field type is considered to be text. EMPID has to be treated that was as we have no control over how a group will set up their EMPID's. Apparently the SSN field may be filled in by folks across the pond who may choose to enter an ID prefixed with an "I". That would kill the idea of forcing columns to contain hard coded dashes though one could test for len = 9 and assume it is ssn.

    Candidly the spreadsheet has not been built yet. We were not sure that Excel could export into the fixed format needed.
    I know that there is a special function for converting a soc sec number into a display of xxx-xx-xxxx, but have not exported that field yet to examine it (assume it is display only). If a form would allow us to capture soc security and test the first byte (I or numeric) then we could test tdhat position. We have no form experience.

    Gentlemen: I have seen some of the many forum references on the railroad application. In your opinion, is this a high effort item?
    Could one build a table with a key for the length and mode (char or numeric) for each column and have a "module" operate across the entire row using the table values? Of course concatenanting that column might allow one to save the row as a non-delimited field, but then the max text length for a column is 256?
    I will take a look at Steve's suggestion; no pain, no gain. Thank you both.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export Fixed Width (Excel 97 )

    You could modify the formula into a custom function easily. It could incorporate data validation, (to check for numbers or text) and act accordingly. You would have to be specific about the range of what you expect in excel and what the text should be.

    You could have a table (as part of the function perhaps) which definded the segments.

    Taking data of this sort, converting to the column of 1 wide text string and exporting is not that complicated. What types of "checks" you want to add to the "wide text conversion from multi-columns" is probably the most difficult to ensure the conversion is correct and it does not encounter anything it doesn't expect and know how to handle.

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Fixed Width (Excel 97 )

    I think that Steve addressed some of your questions. As far as exporting to a fixed field length file, you can do that with VBA code. However, you have to know what the worksheet looks like before starting to write the code. If you want help writing the code, post a new reply with the actual workbook (with dummy data of course) when you have it designed.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •