Code modfication for notepad to excel

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

Re: Code modfication for notepad to excel

Post by HansV »

What error are you getting, and which line causes the error?
Best wishes,
Hans

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub Sample()
    Dim w1 As Workbook
    Set w1 = Workbooks.Open("C:UsersWolfieeeStyleDesktopsample1.xlsx")
    Dim Ws1 As Worksheet
    Set Ws1 = w1.Worksheets.Item(1)
    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:UsersWolfieeeStyleDesktopDF.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
    
End Sub



Problem Solved

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Thnx Alot For ur Great Support HansV Sir
Have a Great Day
If any correction are there in the code then plz let me know

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

Code for text file to Excel

Post by Doc.AElstein »

zyxw1234 wrote:
19 May 2020, 10:18
... data from notepad
There is no such thing as a Notepad file. We cannot “get data from Notepad” – that is nonsense. – That is a rubbish description.

Originally uploaded here were two files,
An Excel File, Sample1.xlsx
A Text file, DF.txt

A .txt ( and a .csv ) file extension is usually used on a Text file.
A Text file is not an Excel File. A Text file is not a Notepad file. A Text file is just text. Because a Text file is just text, there are no cells to separate values.
Because there are no cells to separate values in a text file, we sometimes separate them with a character such as
, ; | Tab etc.
Your original uploaded DF.txt used a comma , as the separator
Your original uploaded DF.txt looked like this as seen for example using a Text Editor. ( Notepad is just one of many available text editors )
Your text data was like this:
10,18052020,9.23,0015378
20,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00,0.00,10.00
20,1025GS2021,GS,IN0020010040,0.00,0.00
……..etc.
You are using a comma in DF.txt to separate the values. Because you are using a comma to separate your values , we sometimes call such a file a comma separated values file., and we often give a comma separated values text file the extension .csv. But you don’t have to. It’s is your choice. Both DF.txt or DF.csv is OK. You can use either for your text file.
You have used DF.txt for your comma separated values text file. That is a bit unusual, but it is OK. Its your choice.
_._______________________-
I expect as usual you have understand little , if anything , of what we have said. Or we have not understood you. Both I expect. … Never mind..
zyxw1234 wrote:
19 May 2020, 10:37
...there is data (file name DF.tXt) … i want that data should be putted .. to sample1.xlsx in first sheet
Let me make a guess at the question:
Question: ( My Geuss )
I have a text file, DF.txt . The values in the text file are separated with commas ,
I want to bring the values into the cells of the first worksheet of an excel file, Sample1.xlsx (Worksheet name can be anything )
There may be data already in the first worksheet of Sample1.xlsx , so the data must be added from the next free row. …… the file in which macro is placed is different, ( Macro is to be in another file, (macro.xlsm) )
Answer:
You can try to open a text file with Excel. It may or may not work for you as you want it to. But this is usually not the best way. Better is to open with like these ways

Lets look at your text file in detail
As expected we see that the text file has the comma , value separators. In addition we see the conventional line separator is the pair
vbCr & vbLf
( Note: vbNewline = vbCr & vbLf )

And here is the first three Lines of your Sample1.xlsx. This is an Excel File
Using ____ __Workbook_ sample1.xlsx
/ABCDEFGHIJ
110180520209.2315378
2201018GS2026GSIN002001008100100010
3201025GS2021GSIN002001004000100010
|Worksheet|_Sheet1_/___//

Macro answer here and in next post
As Hans said, the original code was doing something similar in two different ways. You only need one of those ways.
My macro , uses the first way, which is a “ Open __ For ___ As “ way
The macro takes in the text file as a single long text string. We split the text file into an array of rows by splitting by vbCr & vbLf . This allows us to determine the number of rows, and assume we need 10 columns, ( A – J ). So we know the size of our output, and can make an array for the output..
That array arrOut() , is built up by _.....
_.. considering each row of data
___.. splitting each row into columns by splitting by the comma ,
Finally the array is pasted to the worksheet at the next free row

_.________________________________________________________________________________________
Cross post: https://chandoo.org/forum/threads/fetch ... ost-264364
https://excel.tips.net/T003148_Importin ... kbook.html
https://excel.tips.net/T002519_Comma-De ... d_Mac.html

_.____________________
Edit Aug 2020: Here we go again, Groundhog day....
http://www.eileenslounge.com/viewtopic. ... 63#p272563
Last edited by Doc.AElstein on 06 Aug 2020, 08:21, edited 6 times in total.
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

Code for Text File to Excel

Post by Doc.AElstein »

For explanation, see last post

Code: Select all

'                                                                              From vixer zyxw1234 Avinash : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629     DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic                           Excel File, https://app.box.com/s/yyzt8ywwpkkn8vxtxumalp7eg3888jnu  Sample1.xlsx
Sub TextFileToExcel()  '  http://www.eileenslounge.com/viewtopic.php?p=268809#p268809
Rem 1 Workbooks,  Worksheets info
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Sample1.xlsx") ' CHANGE TO SUIT
 Set Ws = Wb.Worksheets.Item(1)     ' first worksheet
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 = 1      '  If there is no data in the worksheet we want the first row to be the start row
    Else
     Let NxtRw = lr + 1 ' If there is data in the worksheet, we ant 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 & "\csv Text file Chaos\" & "DF.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
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 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

Rem 4  Finally the array is pasted to the worksheet at the next free row
 Let Ws.Range("A" & NxtRw & "").Resize(RwCnt, 10).Value = arrOut()
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Thnx Alot Doc Sir for helping me in solving this Problem
I understood the game of csv file what u mean to say, Actually i dont know much about excel u r the professional, so u know very well about it & it will take some time for me to understand all the part

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Sorry for the same Doc Sir

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Code: Select all

Sub TextFileToExcel()
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = ActiveWorkbook
 Set Ws = Wb.Worksheets.Item(2)
Dim lr As Long: Let lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row
Dim NxtRw As Long
    If lr = 1 And Ws.Range("A1").Value = "" Then
     Let NxtRw = 1
    Else
     Let NxtRw = lr + 1
    End If

Dim FileNum As Long: Let FileNum = FreeFile(1)
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFilename=%26quot%3BC%3AUsersWolfieeeStyleDesktopNSEVAR%2Etxt%26quot%3B
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum

Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1

Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To 10)


Dim Cnt As Long
    For Cnt = 1 To RwCnt
    Dim arrClms() As String
     Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)
    Dim Clm As Long   '
        For Clm = 1 To UBound(arrClms()) + 1
         Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt


 Let Ws.Range("A" & NxtRw & "").Resize(RwCnt, 10).Value = arrOut()
 Wb.Save
 
End Sub

I used this macro & i got error
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
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Code modfication for notepad to excel

Post by Doc.AElstein »

See here ( from… when i click on any cell that has output there is an option numbers stored as text )
https://eileenslounge.com/viewtopic.php ... 04#p272704

and here
for my alternative….

_._________________

It needed one last extra code line….

Code: Select all

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 in a full modified macro ,
Sub TextFileToExcel_GroundhogDay12b()
, in the uploaded workbook
macro.xlsb : https://app.box.com/s/uwpnuqmnc1uxpl0wpfrbh52iqr1enfcv
NSEVER.txt : https://app.box.com/s/245h7i5nh6an8vw08g8t08fvu30ylih2


_._________________

( But for now, you might be best to use Hans final macro , …. It may be simpler for you to understand… ).

_.___


It’s a long hard painful road, but sometimes we get there in the end…. well , until the next time Loop….
Last edited by Doc.AElstein on 06 Aug 2020, 11:24, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Sure Sir
Thnx for the info Doc Sir

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

Re: Code modfication for notepad to excel

Post by Doc.AElstein »

I am having difficulty logging in with this account just now.
You can find me at DocAElstein also