Macro Correction(.txt to xlsb)

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub STEP2()
    Dim w1 As Workbook
    Set w1 = ActiveWorkbook
    Dim ws1 As Worksheet
    Set ws1 = w1.Worksheets.Item(2)
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    On Error Resume Next
    
    
    myFile = "C:UsersWolfieeeStyleDesktopNSEVAR.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    ' Split into wholes line
    lineData() = Split(MyData, vbNewLine)
    Set rng = Range("A2")
    
    ' For each line
    For i = 0 To UBound(lineData)
    
        ' Split the line
        strData = Split(lineData(i), "|")
        
        ' Write to the sheet
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
    Range("A:A").Select
    
    
     Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
        
    
    ws1.Columns("A:Z").AutoFit
    
    
    Range("A1").Select

w1.Save
    
End Sub



I have this maco & this macro is works fine
But i have some issues with this macro(If i am in sheet2 & runs this macro then this macro puts the data in sheet2 & if i am in sheet3 & run the macro then it puts the data in sheet3 & i have to fix this issue, whenever i run the macro it should put the data in sheet5 only)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

Range("A2"), Range("A:A") and Range("A1") do not specify which worksheet they refer to, so Excel will use the range on the worksheet that is the active sheet when you run the macro.
So you have to change those to refer to the correct sheet (probably ws1)
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub STEP2()
    Dim w1 As Workbook
    Set w1 = ActiveWorkbook
    Dim Ws1 As Worksheet
    Set Ws1 = w1.Worksheets.Item(2)
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range
    
    myFile = "C:\Users\**I've been banned**\Desktop\NSEVAR.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    ' Split into wholes line
    lineData() = Split(MyData, vbNewLine)
    Set rng = Range("A2")
    
    ' For each line
    For i = 0 To UBound(lineData)
    
        ' Split the line
        strData = Split(lineData(i), "|")
        
        ' Write to the sheet
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
    Ws1.Range("A:A").Select
    
    
     Selection.TextToColumns Destination:=Ws1.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
        
    
    Ws1.Columns("A:Z").AutoFit
    
    
    Ws1.Range("A1").Select

w1.Save
    
End Sub



I edited but still its not working

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

The line

Set rng = Range("A2")

still refers to the active sheet.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub STEP2()
    Dim w1 As Workbook
    Set w1 = ActiveWorkbook
    Dim ws1 As Worksheet
    Set ws1 = w1.Worksheets.Item(2)
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    On Error Resume Next
    
    
    myFile = "C:UsersWolfieeeStyleDesktopNSEVAR.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    
    lineData() = Split(MyData, vbNewLine)
    Set rng = ws1.Range("A2")
    
    
    For i = 0 To UBound(lineData)
    
        
        strData = Split(lineData(i), "|")
        
        
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
    ws1.Range("A:A").Select
    
    
     Selection.TextToColumns Destination:=ws1.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
        
    
    ws1.Columns("A:Z").AutoFit
    
    
    ws1.Range("A1").Select

w1.Save
    
End Sub




now i am not getting correct output Sir
all data is pasted to column 1

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

You appear top split the data twice: in the line

strData = Split(lineData(i), "|")

and again in

Selection.TextToColumns ...

Why is that?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

https://drive.google.com/file/d/10f9T1G ... sp=sharing

this is the text file which i am putting it to sheet2 by macro
plz see
I just copied the macro from various question & i made this sir

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

See this thread where you had the same problems (thanks to Doc.AElstein for pointing it out)
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

But that thread doesnt have the solution Sir

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

You reported "problem solved" there. Moreover, several issues were pointed out to you in that thread, but you haven't done anything with them.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

No HansV Sir, i have not reported that problem as Solved

I made the changes according to ur suggestion but now a new error has came after editing a macro
I have uploaded the file with macro in it Sir
Plz have a look
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

As mentioned in the other thread, there is no need to use Selection.TextToColumns, since you already split the data in

Code: Select all

    For i = 0 To UBound(lineData)
    
        
        strData = Split(lineData(i), ",")
        
        
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
So you can remove the Selection.TextToColumns part!
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub STEP2()
    Dim w1 As Workbook
    Set w1 = ActiveWorkbook
    Dim ws1 As Worksheet
    Set ws1 = w1.Worksheets.Item(2)
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    On Error Resume Next
    
    
    myFile = "C:UsersWolfieeeStyleDesktopNSEVAR.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    
    lineData() = Split(MyData, vbNewLine)
    Set rng = ws1.Range("A2")
    
    
    For i = 0 To UBound(lineData)
    
        
        strData = Split(lineData(i), ",")
        
        
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
End Sub




plz run the macro & see the output Sir
Its not perfect

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

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)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

It's better to use TextToColumns instead of splitting the data row by row.

Code: Select all

Sub STEP2()
    Dim w1 As Workbook
    Dim ws1 As Worksheet
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    myFile = "C:\Users\**I've been banned**\Desktop\NSEVAR.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1

    lineData() = Split(MyData, vbNewLine)
    Set w1 = ActiveWorkbook
    Set ws1 = w1.Worksheets.Item(2)
    With ws1.Range("A2").Resize(UBound(lineData) + 1)
        .Value = Application.Transpose(lineData)
        .TextToColumns Destination:=ws1.Range("A2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, Comma:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
            Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
    End With
End Sub
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Run time error 1004
application defined or object-defined error
highlighted line
With ws1.Range("A2").Resize(UBound(lineData) + 1)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction(.txt to xlsb)

Post by HansV »

Please remove the line

Code: Select all

    myFile = "NSEVAR.txt"
I inserted that for my own test.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction(.txt to xlsb)

Post by zyxw1234 »

Awesome HansV Sir
Thnx Alot for Helping me in solving this Problem
Problem Solved
Have a Awesome Day

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Macro Correction(.txt to xlsb)

Post by Doc.AElstein »

This post is just by way of a bit of clarifying/summary, for future reference in the next versions of the Thread, here or elsewhere…

We are looking at variations on the basic idea of importing the data values from a text file into an Excel worksheet. Usually the value separator/delimiter in the text file is a comma , and the line separator a vbCr & vbLf ( vbCr & vbLf = vbNewLine ).
There are occasionally a few variations of those separators in use,( which usually sets off a few more of the similar duplicate erratic postings), but in nigh on a hundred text files from the OP I never saw one using the Pipe yet, | , so the original macro frequently presented as near perfect, that tries to import a Pipe delimited file in two ways, is just a red herring like the majority of what the OP posts. ( It likely originated here ….. )

This thread , (the one I am writing in now), and that thread , also referenced in this Thread, this post, are close to identical in every aspect including the same data in the files. Only the file names are different and the worksheet required to have the data imported into is different, (OP asked it to be the fifth, but seemingly the second is wanted by him… - the OP frequently can’t distinguish between different words and different numbers ! ) ).
_.______

This is not important and does not really matter but I find it can help later in clarifying what has/is going on in the inevitable future chaos of Thread versions.. …
zyxw1234 wrote:
04 Aug 2020, 20:07
i have not reported that problem as Solved..
You did, and have, several times
and
zyxw1234 wrote:
04 Aug 2020, 16:28
But that thread doesn’t have the solution …
It did and it still does . The last macro I gave back then answered the question perfectly in that thread and also answers the same question here, in this thread , ( it just needs the file names and the worksheet reference changed, that’s all )
The last macro I gave back then in that thread does exactly the same as Hans penultimate macro suggestion in this thread
They both do this:
Bring in the text files as a long single string, then Split it by vbCr & vbLf to get the rows, and then for each row we Split by the comma to get the columns. The small difference in those two macros is that mine puts all the values in an array , then pastes them all out in one go, Hans penultimate macro pastes out each row , one after the other.
_.____________

when i click on any cell that has output there is an option numbers stored as text
In this thread version, we have introduced a new issue of that little sign at the left of a cell ……... 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)…
As far as I know that is not an error, and not particularly significant for anything the OP is doing, ( but its sometimes easier to humour the OP then go through the agonising attempt at explaining anything to him…)
As far as I know that thing is just an indication that a number is being strored as a text.
To remove that, I guess we need to change any numbers stored as text to be as normal numbers

Hans final macro here in this thread , pastes the Transpose of the Split derived row array into the worksheet, so all the values appear in the first column, then it does the TextToColumns to put the value appropriately into the columns. I expect the TextToColumns thing puts the values in the types it thinks appropriate, so things looking like numbers are put in as normal numbers.

For the sake of completeness, for my last macro: … You could add a code line, for example using Evaluate Range techniques, to change any numbers in the final output to be shown as normal numbers.. something of this form:

Code: Select all

 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)
 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) & "))")
( It is based on a formula like this
=IF(F2="";"";IF(ISNUMBER(1*F2);1*F2;F2))
That may not use the best formula, but it is intended just to demonstrate the idea. The formulas its based on are in the uploaded workbook )

Here is the complete modified macro. Its almost identical to my last macro . I’ve done it more for my reference than anything else for when this Thread re appears in the next version. ( BTW. also for my later reference, my last macro and the modified macro have the answer for the September/ October re run issues of the Threads, which may likely ask for appending the data into the worksheet when there is already some data in it )

_.________________

Macro.xlsb : https://app.box.com/s/uwpnuqmnc1uxpl0wpfrbh52iqr1enfcv
NSEVER.txt : https://app.box.com/s/245h7i5nh6an8vw08g8t08fvu30ylih2
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Macro Correction(.txt to xlsb)

Post by Doc.AElstein »

Hello
I thought it would be worthwhile and interesting to add a follow up post here for a couple of reasons
_1) I noticed something a while back here at the Lounge while answering a question , https://eileenslounge.com/viewtopic.php ... 91#p266691 , which allows, coincidentally , for an improved way to import text files by VBA , which I used a few times now already , and then , coincidentally the last time I did it, yesterday, (https://www.ozgrid.com/forum/index.php? ... ost1239241 ) , I noticed that …
_2) This alternative way to import text files by VBA coincidentally also gets over the last problem we had here in this thread….that problem was: 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)

_1) What was noticed is that a 1 D array containing elements that are themselves 1 D Arrays ( sometimes I think referred to as jagged arrays ) appear to be held by Excel as if it were a 2 D array, at least as far as Index first argument is concerned.
Just to explain that pictorially.. if this was my actual jagged array, say a 1 D array with 3 Elements, which are themselves arrays containing two arbitrary values, pseudo like …_

Code: Select all

a b     c d     e f
-…then we can actually treat it for the first argument of Index as if it was a 2D array. Pseudo like

Code: Select all

a b
c d
e f
We can use this strange phenomena to improve the way I did my text import macro in this and other similar codings…
The previous way I did, which is a very common way people use, is:
You bring in the entire text string that is the complete text file, into a single string variable. Then..
_(i) That string is split by the line separator to get a 1 D array of all the lines ( “rows” )
_(ii)a That array is looped through , and at each loop we split again each element by the value separator to get a 1D array of the “columns”, and then further
_(ii)b at each loop we loop again along the elements ( “columns” ) in that 1 D "columns " array– At each of these “innermost” loop points we fill either a spreadsheet or an array with an element
What we can now do is do away with the inner loop _(ii)b and instead at _(ii)a just fill a 1 D array with the 1 D array of the “columns”
Then finally, to get our array / range we apply a code line of pseudo like
Index( OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..} )
( Of course you can pick out whatever set of rows or columns that you want instead )

_2) The last problem we got in this Thread, does not seem to occur in this way. (I suppose this is probably because the way Index works is sort of, it houses each returned value in a Variant and then somehow something looking like a number gets guessed correctly to be a number when that is pasted out to spreadsheet.

I have not seen this phenomena mentioned before. It seems quite useful. The improved text import way is one use of it.
I am curious if anyone else either came across it, or maybe can think of useful ways of using it?

The macro in the next post is almost the same macro I provided previously, but using the Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
The important differences are this bit .._

Code: Select all

Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To 10)
Rem 3 An array is built up by _....
Dim Cnt As Long
    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
    Dim Clm As Long   '
        For Clm = 1 To UBound(arrClms()) + 1
         Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt 
_.. gets changed to

Code: Select all

 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)")) 
That change is to remove the last columns loop and replaces it by putting the array of columns into the next element of our 1 D Unjagged Jagged Array of rows, that is to say each element of the main 1D array is a 1 D array of all the column values.
Then finally we need an extra line to get the final 2 D range array via the
the Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})

In short, we replace a loop that is itself done lots of times with a single Index type code line. In other words we replace a loop of loops with a single Index type code line.
Potentially I would have thought that it could be a significant improvement in time, but I don’t personally have enough experience with real data to give typical figures

Alan

P.S. At first glance this way appears to be limited to “unjagged” jagged arrays, ( That is to say the element number must be the same in all the inner 1 D arrays ) , so I modified the first line in the OPs text data file since just that first line had a different number of values in it than the other 15265 rows . I might think of a way to over come this limitation later.

_.___________________

macro.xlsb : https://app.box.com/s/naxiaan0ch7vx5j0dpulguuoothv8rr8
NSEVAR_UnjJg.txt : https://app.box.com/s/449x8qvqjp3vs6ruz3vs6befui5fogzd
Last edited by Doc.AElstein on 08 Sep 2020, 12:31, edited 7 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also