Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Intermittant 'good' paste (2002 SP3)

    Hi all Excellers, long time no post (for me anyway).

    I have some CSV data (a sample is shown below), that I need to parse into Excel. All except for the 2 date fields, must be formatted as text in order to preserve leading zeros where present.
    Now I don't want to have to go through the "Text import wizard" or "Data->Text to columns" each time to select the data types.
    So, I set up a simple TextToColumns script that parses the selected data in column A into separate columns as specified above.

    So far so good.

    BUT (you all knew that this was coming didn't you), often when I paste the data into a sheet, Excel parses it on the commas and doesn't do the formatting.
    Sometimes it pastes into column A and allows me to run my TextToColumns script, sometimes is parses and then offers me the chance to use the "Text import wizard", which means I have to go thgrough and specify each columns data type.

    I tried recording the paste into sheet (where it auto-parses) and then the use of the Text import wizard, but the resulting script contained just:

    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
    DisplayAsIcon:=False

    The Text import part didn't get recorded.

    So, does anyone have any idea why I can sometimes get the text pasted intoi a single column and not otherwise?
    Following on from that, is it possible for me to always have it pasted in a single column?
    And I guess, is it possible to automate the text import wizard operation?

    Any help is much appreciated.

    I can attach sample sheets if necessary.

    <pre>01, ,1,1997-07-17,2020-12-31,01234569
    01, ,2,2000-10-23,2020-12-31,01234569
    01, ,3,1997-06-13,2020-12-31,01234569
    02, ,1,1997-02-10,2001-12-02,012349
    02, ,3,1997-02-10,2001-12-02,12349
    03, ,1,1997-02-10,2020-12-31,12349
    03, ,3,1997-02-10,2020-12-31,12349
    03,01,1,2004-08-10,2020-12-31,012349
    03,01,3,2004-08-10,2020-12-31,012349
    03,02,1,2004-08-10,2020-12-31,012349
    03,02,3,2004-08-10,2020-12-31,012349
    </pre>


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

    Re: Intermittant 'good' paste (2002 SP3)

    It's best to open the text file in a new window, then copy the contents into your workbook.

    Check out the Workbooks.OpenText method to see how to automate the Text Import Wizard. You can get a feeling for it by recording opening a text file (through File | Open...).

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    Hi Hans, I don'tunderstand what you mean by "open the text file in a new window, then copy the contents into your workbook."

    How does this differ from opening the text file and pasting it in (which was what I said that I did)?

    And also on the "OpenText" method, I would rather paste the data in as it tends to be quicker to openthe csv file in Notepad and paste into the correct area on my sheet.

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

    Re: Intermittant 'good' paste (2002 SP3)

    If you open the file in Notepad, then copy/paste the contents into Excel, you can't control how it is pasted, as you have found. That is why you should open the text file in Excel using Workbooks.OpenText. That will give you complete control over the way the data are parsed. Workbooks.OpenText opens the text file in a new window, not in the destination workbook, so you will still have to copy the data - after parsing them.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    Hi Hans,
    I still don't quite get it I'm afraid.
    I have a lot of these .CSV files. Currently I just open them from whatever folder I happen to be in in Windows Explorer (Sent To->Notepad). This is easy. Then I copy and paste into Excel.

    Sometimes Excel pastes all into column A (this is ideal), sometimes it parses into columns and presents mw with the "floating icon" to select the Text import wizard (not so good).
    I can find no common denominator for why it sometimes chooses one or the other.

    Now, how would I go to a folder and get Excel to open the file in a new window using the Workbooks.OpenText method that you suggest?

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

    Re: Intermittant 'good' paste (2002 SP3)

    Here is an example macro. It assumes that there are two columns, the first is imported as text, the second as general (Excel decides).

    Sub Test()
    Dim varFile As Variant
    Dim wbkCurr As Workbook
    Dim wbkText As Workbook
    varFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
    If varFile = False Then
    ' user cancelled
    Exit Sub
    Else
    Set wbkCurr = ActiveWorkbook
    Workbooks.OpenText Filename:=varFile, DataType:=xlDelimited
    Set wbkText = ActiveWorkbook
    With wbkText.Worksheets(1)
    .Range("A1").CurrentRegion.TextToColumns _
    Destination:=.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    Semicolon:=True, _
    FieldInfo:=Array(Array(1, 2), Array(2, 1))
    .Range("A1").CurrentRegion.Copy _
    Destination:=wbkCurr.Worksheets(1).Range("A1")
    End With
    wbkText.Close SaveChanges:=False
    End If
    Set wbkCurr = Nothing
    Set wbkText = Nothing
    End Sub

    Look up OpenText in the VBA help to learn how to expand the FieldInfo argument for more columns.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    I tried that, but it fails as when it opens the .CSV file, it has already parsed the data into separate columns. So .Range("A1").CurrentRegion. is more than one column wide and it fails because it texttocolumns can only be used on a single column range.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    I even tried altering the OpenText parameters to parse the file at open time thus:
    <pre>
    Workbooks.OpenText Filename:=varFile, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 2)), _
    TrailingMinusNumbers:=True
    </pre>

    But it still loaded the file with the columns as General and stipped off the leading zeros.

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

    Re: Intermittant 'good' paste (2002 SP3)

    Ah, Excel's US-centricity rears its ugly head. The macro worked OK on my Dutch language system because it uses a different list separator, but as soon as I used the default separator, things went wrong.
    I'm afraid you will have to use the old-fashioned Basic instructions for reading a text file line by line. See <post#=430137>post 430137</post#> for an example.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    But surely adding the parameters on the OpenText call itself should cause the correct parsing???

  11. #11
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    I just tried changing the deler to | and executiong this command

    Workbooks.OpenText Filename:=varFile, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="|", FieldInfo _
    :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 2)), _
    TrailingMinusNumbers:=True

    and it totally ignores the parsing information!

    It OpenText just broken?

    Here is some of the input data:
    <pre>01| |M|1996-08-01|2099-12-31|0123456789
    01| |1|1996-08-01|2099-12-31|0123456789
    01| |3|1996-08-01|2099-12-31|0123456789
    02| |M|1996-08-01|1999-06-03|01234569
    02| |1|1996-08-01|1999-06-03|01234569
    02| |3|1996-08-01|1999-06-03|01234569
    03| |M|1996-08-01|2004-08-20|01234569
    03| |1|1996-08-01|2004-08-20|01234569
    03| |3|1996-08-01|2004-08-20|01234569
    03|01|M|2004-08-10|2004-08-19|01234569
    03|01|M|2004-08-27|2099-12-31|0123456789
    </pre>


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

    Re: Intermittant 'good' paste (2002 SP3)

    Try changing the extension from .csv to .txt. Excel handles .csv files differently than other text files.

  13. #13
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    Sheesh, it just shouldn't be this difficult!

    Can't Microsofty understand that sometimes people would like to decide for themselves what they want to do!

    Anyway that works.

    I'd still like to know why the paste sometimes does one thing and sometimes does another.

    But anyway, thank you once more Hans for all your assistance.

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

    Re: Intermittant 'good' paste (2002 SP3)

    I agree this is frustrating. Microsoft tried too hard to built artificial intelligence into the way csv files are handled.

  15. #15
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intermittant 'good' paste (2002 SP3)

    Not just that, but if I specify Fixed Width when using texttocolumns, Excel assumes that regardless of the width of columns that I set, I really want it to remove leading blanks!

Posting Permissions

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