Macro Correction(.txt to xlsb)

[Doc.AElstein]

Macro for last post

See that last post
for explanation

Code: Select all

' Improved Text Import using  Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})    https://eileenslounge.com/viewtopic.php?p=274367#p274367
Sub TextFileToExcel_IndxUnJgJgWay()  '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35100          http://www.eileenslounge.com/viewtopic.php?p=268809#p268809
Rem 1 Workbooks,  Worksheets info
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("macro.xlsb")      ' CHANGE TO SUIT
' Set Ws = Wb.Worksheets.Item(2)      ' second worksheet
 Set Ws = Wb.Worksheets("IndxUnJgJgWay") ' CHANGE TO SUIT
Dim lr As Long: Let lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row       '   http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim NxtRw As Long
    If lr = 1 And Ws.Range("A1").Value = "" Then
     Let NxtRw = 2      '  If there is no data in the worksheet we want the second row to be the start row
    Else
     Let NxtRw = lr + 1 '  If there is data in the worksheet, we want the data to be posted after the last used row
    End If
Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\" & "NSEVAR_UnjJg.txt" ' "NSEVAR.txt"   ' CHANGE TO SUIT    From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629     DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic  NSEVER.txt: https://app.box.com/s/245h7i5nh6an8vw08g8t08fvu30ylih2
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by vbCr & vbLf ( Note vbCr & vbLf = vbNewLine )
Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5   etc...
' we can now make an array for all the rows, and we know our columns are A-J = 10 columns
Dim arrOut() As Variant

Rem 3 A 1D array of 1D arrays is built up by _....
Dim Cnt As Long
Dim arrUnjgdJgdRws() As Variant: ReDim arrUnjgdJgdRws(1 To RwCnt)
    For Cnt = 1 To RwCnt '               _.. considering each row of data
    Dim arrClms() As String
     Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
     Let arrUnjgdJgdRws(Cnt) = arrClms() ' Fill the next 1 D array element in the unjagged jagged array of rows
    Next Cnt
'3b   Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
 Let arrOut() = Application.Index(arrUnjgdJgdRws(), Evaluate("=Row(1:" & RwCnt & ")"), Evaluate("=Column(A:J)"))
Rem 4  Finally the array is pasted to the worksheet at the next free row
' Let Ws.Range("A" & NxtRw & "").Resize(RwCnt, 10).Value2 = arrOut()
 Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = arrOut()
' Ws.Columns("A:J").AutoFit
Rem 5 to remove  http://www.eileenslounge.com/viewtopic.php?p=272606&sid=7e8ad1b708dd49a811498ccac6b1e092#p272606    .....     when i click on any cell that has output   there is an option numbers stored as text, convert to numbers,help on this error,ignore error,edit in formula bar,error checking options(these are the options coming)
'This does not need to be done in the  Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})  way
' Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISNUMBER(1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "),1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & ",A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "))")  '   Based on  =IF(F2="";"";IF(ISNUMBER(1*F2);1*F2;F2))
' Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISERROR(1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "),A" & NxtRw & ":J" & RwCnt & ",1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "))")
' Let Ws.Range("B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISERROR(1*B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "),B" & NxtRw & ":D" & RwCnt & ",1*B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "))")

End Sub

[DocAElstein]

Hello,
I did a text file to Excel answer yesterday, that was not particularly much use, but by coincidence another obvious general way to put a text file in Excel occurred to me whilst I was doing it….
I remember this old Thread has collected a few different ways including some unusual novel ideas, so I thought I would add this idea as well, just for future reference…
It’s very simple, perhaps the simplest, and might even be very efficient. It also works if you have different numbers of elements in different lines in the text file. (At first glance it seems also not to suffer from the problem of numbers in the text file coming out as text in the Excel File, the problem we had with some other ways used in this Thread originally)

Its so simple, its silly I never noticed it before.
The start of the macro follows one of the typical .. VBA Open For Get the whole string, manipulate it , then paste it in .. type way, with just a few differences: Instead of messing about looping , Spliting, building up an array, we replace all that codswollop with just one single code line like
TotalFile = Replace(TotalFile, ",", vbTab)
(where TotalFile is a string variable containing the entire text file)

Assuming the text file uses the typical line separator, vbCr & vbLf , then all we do is change the separator used in a line, ( I am using as an example the typical comma , in that example code line ) to what is used in the Windows Clipboard for the “cell wall” or cell separator, when it holds the values from, ( or for ) , an Excel range
( In the Windows Clipboard, the vbCr & vbLf is used to separate the lines / rows of an Excel range of valuer that it holds, so we don’t need to change that )

So then we just put that slightly modified text string of the entire file in the Windows Clipboard, and paste it out.
Simple
All in all, this seems a very simple and efficient way, and I don’ know why it never crossed my mind before.

Maybe I have missed some good reason why this way is not used?, and someone smarter can tell me?

_._____

Simple example. (Download both uploaded files and put them anywhere as long as they are in the same place, then run Sub Eeyore() )

Here’s the text file
A,B,c
1,2
Eeyore



Here’s the macro to put it in Excel. Rem 2 is the simple idea bit. In many other macros that section has a lot of looping, splitting, and stuff to build up the final array that you then finally paste out. If you have different numbers of text elements on different lines, as in the example, then that section would typically be even more complicated.

Code: Select all

 Sub Eeyore()  '   https://eileenslounge.com/viewtopic.php?f=30&t=35100&start=20
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                    ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Eeyore.txt"   '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum

' Rem 2 The simple stuff, change seperator to  vbTab
 Let TotalFile = Replace(TotalFile, ",", vbTab, 1, -1, vbBinaryCompare)

' The clipboard stuff, and paste out
Dim objClip As Object '                                                                        If you declare a variable as Object, you are late binding it.  https://web.archive.org/web/20141119223828/http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
 Set objClip = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")                       ' https://web.archive.org/web/20140610055224/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
objClip.SetText TotalFile
 objClip.PutInClipboard

ThisWorkbook.Worksheets.Item(1).Paste Destination:=Range("A1")
End Sub

Here’s the results you should get:

Eeyore.JPG

Alan

[HansV]

You might shorten the lines

Code: Select all

Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile

to

Code: Select all

    Open PathAndFileName For Input As #FileNum
    TotalFile = Input(LOF(FileNum), FileNum)

Warning: your method will produce incorrect results if the text file contains commas within a value. For example, in a file with contents

aa,"bb,cc",dd,"ee,ff"
"gg,hh",ii,"jj,kk",ll

the more traditional methods will result in

S1479.png

but yours will produce

S1480.png

[DocAElstein]

I never saw that Input( , ) function way before, useful to know about, Thx.


I can’t think of any easy way to get over that problem with the element with a separator in it, at least not one that wouldn’t make it very complicated.
(I guess what ever process allows for that in the traditional ways, by the convention of having, and checking for, the enclosing quotes, must itself add a complexity, but one which we just don’t see. So for the case of that situation not occurring then the clipboard way might still be an efficient alternative.
I am a bit annoyed I never thought of this before. - In the meantime I thought of quite a few situations I had in the past when this way would have considerable simplified a few things. )

( I think it was strange choice to have a common convention of using a separator which is a fairly common character like the comma . I wonder what bright spark came up with that stupid idea back when computers came about.
For now I would like to assume it was, and therefore blame, Chris Greaves, if I may. He often claims to have been around computers in the early days, so he is a good candidate. If I was around computers back then , I would have gone on the streets to protest against it.
I realise a lot of other people were about as well, but I don’t know them all. And if Chris sees this he will have to prove his innocence by explaining why or who or what else was responsible for such a stupid decision, then maybe I will learn something about why such a thing came about.

The first time I put things in a text file, I never knew what things like csv or delimiter were. I decided myself to separate things with something, which was the pipe, just because I saw it on my keyboard and it seemed instinctively a good idea and choice at the time. The pipe looked like a wall, so like the vertical cell wall.
Now I know a bit more about these things my choice would probably be either the pipe or some other obscure character that was unlikely to appear in typical text or numbers.
Since the day before yesterday, I might consider using the Tab , as a good idea, as it would simplify the import into Excel further, particularly if there is a directer way in VBA to put the contents of a text file into the windows clipboard, which I expect there might be. )

[LisaGreen]

WOW!!

Alan!!! You're on a roll!! Great stuff!!

Lisa

[DocAElstein]

Hi Lisa,
It’s quite frustrating that I missed the obvious Clipboard way in the past, its so simple its stupid.
( I have seen some single liner formulas done by people to get at fields in a Delimited Text String, which you could apply to the main string to get at , for example, specific rows. So potentially you could do some manipulation of the text file all on the same single line, which would once again simplify some more complicated macros involving looping).

I suppose one other small warning about this way though, is that it relies on the Microsoft Windows Clipboard working properly. - One of a few reasons I prefer older Office versions is because the clipboard is a bit quirky in newer versions. Up to about 2013 its reliable and stable, after that is can give problems. To overcome those problems, a fix often involves using much more complicated API coding to use the Clipboard, so that extra complication might offset the advantages of the otherwise simplified Clipboard way.

I don’t know how good this sort of Clipboard way would be for very big files.
Alan

[SpeakEasy]

>one other small warning about this way though, is that it relies on the Microsoft Windows Clipboard working properly

To be pedantic, your method actually relies on the somewhat limited msForms dataobject working properly - and Microsoft have in the past acknowledged there to be issues with the msForms dataobject under windows 8 and 10 (having Explorer windows open, for example, can cause issues!)

[DocAElstein]

Not pedantic, IMO, - its good to get things right, good to know, thanks.
If I remember correctly, problems I ever experienced in the past, or know of, would have been when using coding using the dataobject thing.