Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Excel's text import wizard by code?

CenturyChildCenturyChild Posts: 12Member

I have a problem with opening text files with Excel. My text file
consists of tab separated columns. Some "columns" have regular text,
some math calculations and some numbers with a + sign in front of them.
Now the regular strings and the calculations are opened and displayed just fine, but the ones starting with a + sign don't. And therefore those columns cannot be read -> I get a read error when trying to do so.

Now, I have determined that when I open the .txt file manually from
Excel.. the 'text import wizard' section where you get to select the
format for each of the to be created columns (i think its the wizard's page 3) is the one that matters, because when I select the format to
be text for those columns that start with a + sign it works fine and
displays the data correctly (that is with the actual + sign).

However, if you first open the .txt file into Excel and try to manually
alter the format of the columns (using the "Format cells" option) it
doesn't work either.. Why?

It seems that the only way of opening the .txt file correctly is by
using the 'text import wizard' and IT'S column format option..?

How can i do this through my code? The regular xlDelimited parameters
doesn't seem to do the trick..

Comments

  • PavlinIIPavlinII Posts: 404Member
    :
    : I have a problem with opening text files with Excel. My text file
    : consists of tab separated columns. Some "columns" have regular text,
    : some math calculations and some numbers with a + sign in front of them.
    : Now the regular strings and the calculations are opened and displayed just fine, but the ones starting with a + sign don't. And therefore those columns cannot be read -> I get a read error when trying to do so.
    :
    : Now, I have determined that when I open the .txt file manually from
    : Excel.. the 'text import wizard' section where you get to select the
    : format for each of the to be created columns (i think its the wizard's page 3) is the one that matters, because when I select the format to
    : be text for those columns that start with a + sign it works fine and
    : displays the data correctly (that is with the actual + sign).
    :
    : However, if you first open the .txt file into Excel and try to manually
    : alter the format of the columns (using the "Format cells" option) it
    : doesn't work either.. Why?
    :
    : It seems that the only way of opening the .txt file correctly is by
    : using the 'text import wizard' and IT'S column format option..?
    :
    : How can i do this through my code? The regular xlDelimited parameters
    : doesn't seem to do the trick..
    :
    Hi, I've tried code
    [code] ChDir "C:_temp"
    Workbooks.OpenText Filename:="C:_temp extak.txt", Origin:=852, StartRow _
    :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True[/code]and file:
    [code]sdf +12 10 fds
    adf +22 11 fda
    asf +21 01 fsa[/code]
    seems to be loaded correctly..

    +12 value (like text) using "Format cells" option doesn't change to 12, because only the FORMAT is changed, values aren't requerried.. (If you use F2, Enter keys, the values goes right..)
    Excel (ver. 2k, XP) can (after selecting area of those cells) offer you small exclamation mark, that popups a menu with "convert to number" ... But I did not find how to to this in code...
    But there is one way... to write this method manually.
    [code]Dim xCell As Range
    For Each xCell In Selection.Cells
    xCell.NumberFormat = "General"
    xCell.Value = xCell.Value
    Next xCell[/code]

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


  • CenturyChildCenturyChild Posts: 12Member

    Well I tried your code but the result was that the cell values did
    turn into Text format as you said, but the cell values did not
    update to the actual field values. Instead the #NAME? were left behind
    to all the cell that contained the + sign in front of other chars.

    Did I screw up somewhere? I'm actually doing this in C++ so there
    might be a little difference somewhere in the required syntax.

    (If you're wondering why am I not asking these questions in the
    C/C++ section of this forum.. the reason is that I already did that
    and someone advised me to come here instead where there seems to
    be a larger amount of knowledge in the subject.)

    Here my code:
    -------------------------------------------------


    Excel::RangePtr specr;
    specr = pSheet->Cells->Item[1, 5];

    for (int conv = 1; conv <= d; conv++)
    {
    if (conv > 1)
    specr = specr->Offset[0,1];

    specr->NumberFormat = "Text";
    specr->Value2 = specr->Value2;
    }
  • PavlinIIPavlinII Posts: 404Member
    :
    : Well I tried your code but the result was that the cell values did
    : turn into Text format as you said, but the cell values did not
    : update to the actual field values. Instead the #NAME? were left behind
    : to all the cell that contained the + sign in front of other chars.
    :
    : Did I screw up somewhere? I'm actually doing this in C++ so there
    : might be a little difference somewhere in the required syntax.
    :
    : (If you're wondering why am I not asking these questions in the
    : C/C++ section of this forum.. the reason is that I already did that
    : and someone advised me to come here instead where there seems to
    : be a larger amount of knowledge in the subject.)
    :
    : Here my code:
    : -------------------------------------------------
    :
    :
    : Excel::RangePtr specr;
    : specr = pSheet->Cells->Item[1, 5];
    :
    : for (int conv = 1; conv <= d; conv++)
    : {
    : if (conv > 1)
    : specr = specr->Offset[0,1];
    :
    : specr->NumberFormat = "Text";
    : specr->Value2 = specr->Value2;
    : }
    :
    Hi, C/C++ is not my favorite language in combination with excel :)
    If "->" is equal to "." in vb object syntax..
    if specr=specr->Offset[0,]; works and moves your "cursor" to next cell..
    the rest should work too. But I had NumberFormat = "General"; in my code (to convert +15 (text) to 15 (number)

    PavlinII

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


  • CenturyChildCenturyChild Posts: 12Member

    : Hi, C/C++ is not my favorite language in combination with excel :)
    : If "->" is equal to "." in vb object syntax..
    : if specr=specr->Offset[0,]; works and moves your "cursor" to next cell..
    : the rest should work too. But I had NumberFormat = "General"; in my code (to convert +15 (text) to 15 (number)
    :
    : PavlinII


    Yes well my entire Excel-handling software is already written with
    C++ so I'm not interested in considering anything else. :)

    I tried to use the "General" format also, no difference..

    But I feel that we're on the right track here with this approach..
    Gotta figure this one out.
  • PavlinIIPavlinII Posts: 404Member
    :
    : : Hi, C/C++ is not my favorite language in combination with excel :)
    : : If "->" is equal to "." in vb object syntax..
    : : if specr=specr->Offset[0,]; works and moves your "cursor" to next cell..
    : : the rest should work too. But I had NumberFormat = "General"; in my code (to convert +15 (text) to 15 (number)
    : :
    : : PavlinII
    :
    :
    : Yes well my entire Excel-handling software is already written with
    : C++ so I'm not interested in considering anything else. :)
    :
    : I tried to use the "General" format also, no difference..
    :
    : But I feel that we're on the right track here with this approach..
    : Gotta figure this one out.
    :
    There must by some simple way to solve this.. (The darkest place is under the candle)
    Try my VB code in your excel (ALT+F11, and paste it to first sheet, run F5, or in sheet alt+F8).. If you don't know VB,
    For Each xCell In Selection.Cells should be the only line you may not understand (like VB) but you should understand (like english :) ).. Just sets xCell to each cell in selected one by one..

    If this code works with your data file, there must be mistake in syntax (or the way of using syntax).. If that vb code doesn't work, send me few sample lines of your text file, I'll take a look at them and if we have code in VB, translation to Cpp will not be problem..

    PavlinII
  • CenturyChildCenturyChild Posts: 12Member

    : :
    : There must by some simple way to solve this.. (The darkest place is under the candle)
    : Try my VB code in your excel (ALT+F11, and paste it to first sheet, run F5, or in sheet alt+F8).. If you don't know VB,
    : For Each xCell In Selection.Cells should be the only line you may not understand (like VB) but you should understand (like english :) ).. Just sets xCell to each cell in selected one by one..
    :
    : If this code works with your data file, there must be mistake in syntax (or the way of using syntax).. If that vb code doesn't work, send me few sample lines of your text file, I'll take a look at them and if we have code in VB, translation to Cpp will not be problem..
    :
    : PavlinII
    :

    Well, I'm no VB master, but I am quite experienced with C++ so
    I do fully understand what your code does. :) And the C++ version
    of your code works perfectly except when it comes to updating (or
    requerying as you call it) each cell value affected by the format
    change. That's when it leaves the "error text" #NAME? instead of
    the actual "+N255" or "=+3G5" or whatever actually reads in the
    fields..

    So, my code fragment

    specr->Value2 = specr->Value2;

    doesn't seem to do the job of requerying the cell value.
    ('specr' is a range pointer pointing in turn to the individual
    cells. Whether I use the 'Value2' or the 'Value' of the cells
    doesn't make a difference.)

    So the problem is.. Instead of requerying the true cell values it
    assigns to the cells the values of the "visible" (not the actual)
    values of the cells therefore leaving the #NAME? crap around.

  • PavlinIIPavlinII Posts: 404Member
    :
    : : :
    : : There must by some simple way to solve this.. (The darkest place is under the candle)
    : : Try my VB code in your excel (ALT+F11, and paste it to first sheet, run F5, or in sheet alt+F8).. If you don't know VB,
    : : For Each xCell In Selection.Cells should be the only line you may not understand (like VB) but you should understand (like english :) ).. Just sets xCell to each cell in selected one by one..
    : :
    : : If this code works with your data file, there must be mistake in syntax (or the way of using syntax).. If that vb code doesn't work, send me few sample lines of your text file, I'll take a look at them and if we have code in VB, translation to Cpp will not be problem..
    : :
    : : PavlinII
    : :
    :
    : Well, I'm no VB master, but I am quite experienced with C++ so
    : I do fully understand what your code does. :) And the C++ version
    : of your code works perfectly except when it comes to updating (or
    : requerying as you call it) each cell value affected by the format
    : change. That's when it leaves the "error text" #NAME? instead of
    : the actual "+N255" or "=+3G5" or whatever actually reads in the
    : fields..
    :
    : So, my code fragment
    :
    : specr->Value2 = specr->Value2;
    :
    : doesn't seem to do the job of requerying the cell value.
    : ('specr' is a range pointer pointing in turn to the individual
    : cells. Whether I use the 'Value2' or the 'Value' of the cells
    : doesn't make a difference.)
    :
    : So the problem is.. Instead of requerying the true cell values it
    : assigns to the cells the values of the "visible" (not the actual)
    : values of the cells therefore leaving the #NAME? crap around.
    :
    :
    So, make requering that value (data type) more manually...
    Dim Temp as Long
    Temp=xCell.Value2
    xCell.Value2=Temp
    ..
    this "magic conversion" works in VB, C++ will need retyping that value.. :)
Sign In or Register to comment.