Intermittant ‘good’ paste (2002 SP3)

Topic

New Reply

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.

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

Reply | Quote

Replies

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…).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I even tried altering the OpenText parameters to parse the file at open time thus:

        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

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

Reply | Quote

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 for an example.

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote