Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving as Tab Delimited (2000 )

    We have deployed an excel template to internal remote customers. This template consists of 3 heading rows. It has 16 columns.
    It is used to input up to 99 rows of information. The file is then saved as a tab delimited text file and imported to be read by a program and loaded into an Oracle database. This works great in all cases where data is input into the 16th column. However, not all columns are mandatory.
    In row 17 (row 14 of data), if data isn't keyed into all the columns, no tabs exist to maintain the spacing of the data after the last column entered. This also occurs on line 30 of the spreadsheet (and possibly on others though I haven't gone that far to check it out since this isn't easily done).
    In rows 3 - 16 and 18 - 29, if the last column entered (for example) is column 5, there are enough
    tabs put in automatically when saved as a tab delimited to maintain the spacing. Why would
    just a couple of the lines not put in the necessary tabs? This doesn't seem template related
    because we've deleted out lines 4 - 100 of this worksheet and rekeyed the information. It still
    doesn't work. We have used ASAP Utilities to reset the last cell. Any ideas why Excel would ignore some blank cells and not others when saving as text delimited?

  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: Saving as Tab Delimited (2000 )

    I am surprised it doesn't "choke" more on having so many blanks. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Why not create a separate sheet with concatenated text in one column that has all the formatting you desire (including the desired tabs)? You could do it with formulas relatively easity. Then saves this sheet as a text file.

    Or if you do it a lot, create your own "text exporter" to look thru the rows and transform it ans write it to a text file.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving as Tab Delimited (2000 )

    Do all of your customers use the same version of Excel? Do you get the same behaviour if you take a problem workbook and Paste Special|Values into a completely fresh workbook? (I do not on a test Excel 2000 workbook.) As you are doing this repetitively, it may be worth setting up a simple VBA routine that takes the values out of a file and puts it into a new workbook with the desired name. HTH
    Gre

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving as Tab Delimited (2000 )

    Unfortunately, our customers have various levels of computer skills and we gave them the Excel template so that they could enter data "offline". We don't want to add formulas or macros to it because we have no control of their environment or PC set up or even what version of Excel they are using (97 or above). We just can't figure out why it is only those rows of the Excel file that cause a problem. Thanks for the info though.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving as Tab Delimited (2000 )

    If it's a template, then you should be able to carry out all the VBA processing at your end - including the TSV conversion. Sorry: there was no question in my mind of passing out code to your customers. Post back and I'll dig out some code for you. HTH
    Gre

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving as Tab Delimited (2000 )

    Your problem may be related to that described in MSKB article Column Delimiters Missing in Spreadsheet Saved as Text. Would it be possible to fill the 17th column with dummy values (for example, a 1 or an x) in all "used" rows? I gather from the MSKB article that this would guarantee that the correct number of tabs is inserted. The Oracle import could just ignore the 17th column.

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving as Tab Delimited (2000 )

    Thank you for the response. The Knowledge Base article indeed explained our problem. Now that we know why, we can explore other ways of doing this. Thanks so much for all the ideas and answers.

Posting Permissions

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