Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

insert spaces and carrige return in Excel cells

Options
  • 30-10-2007 4:49pm
    #1
    gaizka71
    Closed Accounts Posts: 99 ✭✭


    Hi,
    it is a long time since I had to do anything with programming, well this is not real programming but it is the closest thing... and I am a bit stuck.

    This is the situation.
    I have an excel fiel with 15 cells that contain different nature of information, text and numbers.
    Based on this file I have to create a plain text file.
    Each colum in the excel file has to contain an exact number of characters in the text file, if the original has more I have to cut and if there are less I have to add spaces in the text file, and the last colum has to contain ascii characters 13 and 11 to insert a carrige return in the text file.

    I was thinking about using concatenate to link the colums into one but do not really know what to do about the spaces or the carrige return, any ideas?
    any other approaches?
    Thanks a lot.


Comments

  • Options
    #2
    Eoin
    Registered Users Posts: 21,257 ✭✭✭✭Eoin


    I don't think I quite understood the thread, but would putting the values in double quotes be an option? That will preserve all carriage returns etc.


  • Options
    #3
    gaizka71
    Closed Accounts Posts: 99 ✭✭gaizka71


    I will try to explain.
    I have to create a text file with a given estructure like this.

    1st position is a constant, 1 digit
    2 to 6th position is a five digit number
    7th to 14th is a date YYYYMMDD
    15th is a constant 1 digit
    16th to 27th could be a six digit number or a 12 digit number but i have to ocupie the 12 spaces
    28th to 57th is a String... same as above.
    ...
    114th to 252 is reserved space need to be filled with spaces.
    253th to 254th constant to characteres
    255 to 256 ascii code 13 and ascii code 10

    and then the next record... this is what I need to create

    The original info in on excel format, the constants I will add, and I thought that concatenating the cells would be a good idea....

    Thanks again for your help.


  • Options
    #4
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    I would suggest you forget about Excel and use something like Perl. It could do that for you fairly easily.


  • Options
    #5
    gaizka71
    Closed Accounts Posts: 99 ✭✭gaizka71


    thanks for your comments but i have not used pearl at all... and the source info comes in excel format...


  • Options
    #6
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    This link has example code for what you are trying to do. There is also a free tool available here that seems to do what you want.

    These were the first two entries that came up when i googled for "excel export fixed width text"

    Google is your friend.


  • Advertisement
  • Options
    #7
    gaizka71
    Closed Accounts Posts: 99 ✭✭gaizka71


    Thanks a lot, looks like it is exactly what I need.
    Ta!


Advertisement