Code modfication for notepad to excel
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code modfication for notepad to excel
What error are you getting, and which line causes the error?
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
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
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
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
Have a Great Day
If any correction are there in the code then plz let me know
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Code for text file to Excel
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..
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
/ | A | B | C | D | E | F | G | H | I | J |
1 | 10 | 18052020 | 9.23 | 15378 | ||||||
2 | 20 | 1018GS2026 | GS | IN0020010081 | 0 | 0 | 10 | 0 | 0 | 10 |
3 | 20 | 1025GS2021 | GS | IN0020010040 | 0 | 0 | 10 | 0 | 0 | 10 |
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
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Code for Text File to Excel
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
You can find me at DocAElstein also
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
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
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
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
Sorry for the same Doc Sir
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
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
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)
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Code modfication for notepad to excel
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….
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….
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) & "))")
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
You can find me at DocAElstein also
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Code modfication for notepad to excel
Sure Sir
Thnx for the info Doc Sir
Thnx for the info Doc Sir
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Code modfication for notepad to excel
I just did a slightly improved version of my last macro here:
https://eileenslounge.com/viewtopic.php ... 67#p274367
https://eileenslounge.com/viewtopic.php ... 68#p274368
https://eileenslounge.com/viewtopic.php ... 67#p274367
https://eileenslounge.com/viewtopic.php ... 68#p274368
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also