Import the first 100 lines of text file into excel
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Import the first 100 lines of text file into excel
Hello team,
How to import the 100 lines of Text file into Excel?
Regards
Bittersweet
How to import the 100 lines of Text file into Excel?
Regards
Bittersweet
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import the first 100 lines of text file into excel
I'd import the entire file, then delete row 101 and below.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
Or if feeling adventurous, you could try Power Query, which allows you to do much the same thing
-
- 5StarLounger
- Posts: 625
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Import the first 100 lines of text file into excel
Hello
If you are using some form of the typical .. VBA Open For Get the whole string, manipulate it , then paste it in .. type way, then you often use the Split function at some point to split by the line separator to get an array of the rows.
If you only wanted 100 rows then you could simply use the third argument of the Split.
I can’t see any great advantage of doing that over something simpler, but its just shows a use of the third argument of the Split function, which you don’t see very often.
_.____
For a simplified demo, I will get just 3 lines out of a 5 line text file, looking like this
A,B
C,D
E,F
Jee,Haitch
Eye,Jay
So like I want from that in an Excel worksheet like
Either of these macros will do that ,
( The macros assume the seperators used in the text file are for between values
,
and for the lines
vbCr & vbLf )
The shortened version:
A fuller version to show what’s going on:
Alan
If you are using some form of the typical .. VBA Open For Get the whole string, manipulate it , then paste it in .. type way, then you often use the Split function at some point to split by the line separator to get an array of the rows.
If you only wanted 100 rows then you could simply use the third argument of the Split.
I can’t see any great advantage of doing that over something simpler, but its just shows a use of the third argument of the Split function, which you don’t see very often.
_.____
For a simplified demo, I will get just 3 lines out of a 5 line text file, looking like this
A,B
C,D
E,F
Jee,Haitch
Eye,Jay
So like I want from that in an Excel worksheet like
Either of these macros will do that ,
( The macros assume the seperators used in the text file are for between values
,
and for the lines
vbCr & vbLf )
The shortened version:
Code: Select all
Sub snbIt()
Dim FileNum As Long: Let FileNum = FreeFile(1)
Dim TotalFile As String
Open ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt" For Binary As #FileNum
Let TotalFile = Space(LOF(FileNum)) '
Get #FileNum, , TotalFile
Close #FileNum
Let TotalFile = Replace(Replace(Left(Join(Split(TotalFile, vbCr & vbLf, 3), "|"), (InStr(Join(Split(TotalFile, vbCr & vbLf, 3), "|"), vbCr & vbLf) - 1)), ",", vbTab), "|", vbCr & vbLf)
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText TotalFile
.PutInClipboard
End With
ActiveSheet.Paste
End Sub
A fuller version to show what’s going on:
Code: Select all
Sub ThreefromFive() ' https://eileenslounge.com/viewtopic.php?f=27&t=38243
' 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 & "ABCDEFJEEHaitchEyeJay.txt" ' CHANGE TO SUIT
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 'Debug.Print TotalFile
Close #FileNum
' Rem 2 typical split action to get rows, (but only split 3 times)
Dim arrSpt() As String
Let arrSpt() = Split(TotalFile, vbCr & vbLf, 3, vbBinaryCompare)
' Put the string back together with an arbritrary character in place of the vbCr & vbLF wot we want.
Let TotalFile = Join(arrSpt(), "|")
' get rid of the last lines
Let TotalFile = Left(TotalFile, (InStr(1, TotalFile, vbCr & vbLf, vbBinaryCompare) - 1))
' Rem 3 Paste text into Excel ( using windows clipboard )
' In the windows clipboard a row is got by vbCr & vbLf and a cell seperator is got by vbTab
Let TotalFile = Replace(TotalFile, ",", vbTab, 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, "|", vbCr & vbLf, 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
Alan
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 01 Jun 2022, 06:36, edited 2 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Import the first 100 lines of text file into excel
Hello team,
I am lookin for a quick way. I have 150 tables to load to server; VBA is not a solution. Also I had this question before, it seems to me. I couldn’t find it in the forum.
Thanks for all the responses.
Bittersweet
I am lookin for a quick way. I have 150 tables to load to server; VBA is not a solution. Also I had this question before, it seems to me. I couldn’t find it in the forum.
Thanks for all the responses.
Bittersweet
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
>VBA is not a solution
Why not?
Why not?
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Import the first 100 lines of text file into excel
Hello,
We can easily import many rows of data in a blink of an eye. Why should we go for Visual Basic that generate errors for each import?
My job is import data to Linux. I just want to see the format of the data before importing. In situations the amount data is too much that the text file can’t be opened or loaded. I thought if i want to see small number of rows then there should be some way.
Thanks for your effort for putting this code together.
Bittenapple
P.s. I think power query should have a method to show small volume of records.
We can easily import many rows of data in a blink of an eye. Why should we go for Visual Basic that generate errors for each import?
My job is import data to Linux. I just want to see the format of the data before importing. In situations the amount data is too much that the text file can’t be opened or loaded. I thought if i want to see small number of rows then there should be some way.
Thanks for your effort for putting this code together.
Bittenapple
P.s. I think power query should have a method to show small volume of records.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
>P.s. I think power query should have a method to show small volume of records.
It does. That's why I suggested it as a possible solution earlier
>Why should we go for Visual Basic that generate errors for each import?
Sounds like poor VBA to me
>I just want to see the format of the data
Fine, so why not use Powershell?
Get-Content bigtext.csv -Head 100
If it really needs to be in Excel, then pipe the output of the above command to a new file, and import that file in Excel ...
It does. That's why I suggested it as a possible solution earlier
>Why should we go for Visual Basic that generate errors for each import?
Sounds like poor VBA to me
>I just want to see the format of the data
Fine, so why not use Powershell?
Get-Content bigtext.csv -Head 100
If it really needs to be in Excel, then pipe the output of the above command to a new file, and import that file in Excel ...
-
- 5StarLounger
- Posts: 625
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Import the first 100 lines of text file into excel
If it were possible in PowerShell to extend that last line given by SpeakEasy to replace the separator in the resulting text string with a Tab character, and further put that modified string into the windows clipboard, then you would only need to manually paste, to get the info you want. (That would work I guess to put the data into Excel, Word, a text file, or what ever other thing you had open just before you manually do a paste. )
But I don’t know if its possible to extend that last line given by SpeakEasy to replace the separator in the resulting text string with a Tab character, and further put that modified string into the windows clipboard?
This is the pseudo script line I am meaning
Get-Content bigtext.csv -Head 100 | replace the separator with a Tab character | put into the windows clipboard
But I don’t know if its possible to extend that last line given by SpeakEasy to replace the separator in the resulting text string with a Tab character, and further put that modified string into the windows clipboard?
This is the pseudo script line I am meaning
Get-Content bigtext.csv -Head 100 | replace the separator with a Tab character | put into the windows clipboard
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
Sure, why not ...
(Get-Content bigtext.csv -Head 100) -replace ",","`t" | set-clipboard
(Get-Content bigtext.csv -Head 100) -replace ",","`t" | set-clipboard
-
- 5StarLounger
- Posts: 1110
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Import the first 100 lines of text file into excel
After trying to decipher PowerShell scripts, I'm beginning to believe APL was actually very intuitive...
You do not have the required permissions to view the files attached to this post.
PJ in (usually sunny) FL
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 625
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Import the first 100 lines of text file into excel
I learnt some basic PowerShell this last winter, and found it not too bad, better than I expected due to the comments of people smarter than me who found it difficult. That is not usual for me, - in most things computer , ( or anything else for that matter ), I know I am much slower at getting the point than the average intelligence. I think somehow perhaps the vague concept of the dot in VBA and the Pipe in PowerShell seemed to suit my vague misunderstanding of the, equally ( to me ), vague concept of the whole .Net Object Orientated Programming stuff which I believe they both somehow “are” or “use” in a similar way.
Most of my programming knowledge is just knowing a small part of VBA quite well, so I guess I was able to Merge a bit of PowerShell script into my limited computer brain storage capacity reasonably easily..
I find that the development tools in PowerShell are similar but not quite as good as in VBA, for example I can’t find easily a script syntax as I can via the VBA macro recorder….._
_.______________________________________________________
_.....
Wow, super, that’s cool, thx!
I tried that a few times and got it to work. Great, useful….
I was thinking I might be able to use that somehow to add a variation of the clipboard solutions for bringing a text file into Excel as we recently discussed here,
http://www.eileenslounge.com/viewtopic. ... 80#p295780
, if I could get that to work in VBA ..
Unfortunately on the first attempt I can’t seem to get it to work. It’s strange because I can get the initial idea from SpeakEasy to work, - For example, variations of this sort of application of that,
Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3 | Out-File -FilePath 'C:\Users\acer\Desktop\test.txt'
, will work for me either
manually in the PowerShell window
or
in the first test macro below, Sub Test1()
However if I try to do something similar with some variation of this form,
(Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace ",","`t" | set-clipboard
, then I can get that to work manually in the PowerShell window, but attempts like in the second test macro below, Sub Test2() , don’t seem to work. That macro, Sub Test2() , does not error for me, and seems to run normally, but it does not seem to put anything in the clipboard.
Code: Select all
Sub Test1() ' ".... pipe the output of the above command to a new file.... http://www.eileenslounge.com/viewtopic.php?p=296079#p296079
Dim sPSCmd As String
'Let sPSCmd = "Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3 | Out-File -FilePath 'C:\Users\acer\Desktop\test.txt'"
Let sPSCmd = "Get-Content '" & ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt' -Head 3 | Out-File -FilePath '" & ThisWorkbook.Path & "\test.txt'"
Let sPSCmd = "powershell -command " & sPSCmd
CreateObject("WScript.Shell").Exec (sPSCmd) ' .Run sPSCmd, 0, True ' https://www.devhut.net/vba-run-powershell-command/
End Sub
' "...... extend that last line given by SpeakEasy to replace the separator in the resulting text string with a Tab character, and further put that modified string into the windows clipboard?
' ".....Sure, why not ... http://www.eileenslounge.com/viewtopic.php?p=296092#p296092
Sub Test2() '
Dim sPSCmd As String
' This sort of script line works form me manually in the blue PowerShell window :
' (Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace ",","`t" | set-clipboard
'Let sPSCmd = "(Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | set-clipboard"
Let sPSCmd = "(Get-Content '" & ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | set-clipboard"
Let sPSCmd = "powershell -command " & sPSCmd
CreateObject("WScript.Shell").Exec (sPSCmd) ' .Run sPSCmd, 0, True
End Sub
I will maybe experiment a bit more on that when I have more time. Please let me know if anyone has any ideas why that second macro might not be working in the meantime. (Or if anyone has the time to try those two macros out, then let me know if they work for you. - You will just need to save the text file and the file with the macros in it, in the same place, then run either or both of the macros.)
Thx
Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
There a couple of challenges coming up here. The first is that set-clipboard needs to be single-threaded, which the Powershell host provides - but that very single-threaded nature means that it cannot actually paste anything to the clipboard when it is being run from a Powershell session that is itself launched from a Shell host ( CreateObject("WScript.Shell") ). Now we can switch Powershell to multithreaded, but then set-clipboard will not run, and error out.
Fortunately there is an older clipboard command that Powershell can use. So your function might look like this:
Fortunately there is an older clipboard command that Powershell can use. So your function might look like this:
Code: Select all
' "...... extend that last line given by SpeakEasy to replace the separator in the resulting text string with a Tab character, and further put that modified string into the windows clipboard?
' ".....Sure, why not ... http://www.eileenslounge.com/viewtopic.php?p=296092#p296092
Sub Test2() '
Dim sPSCmd As String
' This sort of script line works form me manually in the blue PowerShell window :
'Let sPSCmd = "(Get-Content 'C:\Users\acer\Desktop\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | set-clipboard"
Let sPSCmd = "(Get-Content '" & ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | clip"
Let sPSCmd = "powershell -mta -command " & sPSCmd
'Shell sPSCmd, vbMinimizedNoFocus ' an alternative
CreateObject("WScript.Shell").Run sPSCmd, 0, True '.Exec (sPSCmd) ' .Run sPSCmd, 0, True
End Sub
-
- 5StarLounger
- Posts: 625
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Import the first 100 lines of text file into excel
Thanks SpeakEasy, that’s interesting stuff. I am not too clued up on the single / multithread stuff, but I have heard it mentioned in a project I am following.SpeakEasy wrote: ↑11 Jun 2022, 15:39... set-clipboard needs to be single-threaded, - the Powershell host provides - .....but ..single-threaded nature means that it cannot actually paste anything to the clipboard when it is being run from a Powershell session that is itself launched from a Shell host ( CreateObject("WScript.Shell") ). Now we can switch Powershell to multithreaded, but then set-clipboard will not run, and error out.
Fortunately there is an older clipboard command that Powershell can use . ....
That Clip thing is an interesting enlightenment. ( I see that is working for me (manually) in some earlier PowerShell versions, where the set-clipboard isn’t. That makes it attractive to me as I like to make my codes or scripts as backward compatible as possible, - just a personal preference thing )
( Also in earlier versions of PowerShell, -head is not working for me, but -replace is, so there is still potential here for a good solution for using this as another solution in that previpous clipboard text import to Excel idea , but.......)
... but, Unfortunately I have not had any success yet with any variations of your last code suggestions to put anything in the clipboard. I get the same results as previously. No error, but nothing is appearing in the clipboard.
These are not working for me:
Code: Select all
' http://www.eileenslounge.com/viewtopic.php?p=296145&sid=dbf2d2d6404cc73e3fc3122d67712aaa#p296145
Sub Test2b_i() '
Dim sPSCmd As String
Let sPSCmd = "(Get-Content '" & ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | clip"
Let sPSCmd = "powershell mta -command " & sPSCmd
CreateObject("WScript.Shell").Run sPSCmd, 0, True ' .Exec (sPSCmd) ' .Run sPSCmd, 0, True
End Sub
Sub Test2b_ii() '
Dim sPSCmd As String
Let sPSCmd = "(Get-Content '" & ThisWorkbook.Path & "\ABCDEFJEEHaitchEyeJay.txt' -Head 3) -replace "","",""`t"" | clip"
Let sPSCmd = "powershell mta -command " & sPSCmd
Shell sPSCmd, vbMinimizedNoFocus
End Sub
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 579
- Joined: 27 Jun 2021, 10:46
Re: Import the first 100 lines of text file into excel
>-head is not working
For Powershell 2.0 try -first instead. The downside is that -first reads in the whole file (at least it used to), unlike -head.
>not had any success yet with any variations of your last code
Can 't really help any further. It is that old story: works fine here. As you can see in the following screen shots (was going to be a video, but this site doesn't seem to support them); the only real difference is I am using my own csv instead of yours, and I slightly simplified the command line to avoid all those multiple " (we can replace " with ', but then we cannot use ` t to represent tab)
For Powershell 2.0 try -first instead. The downside is that -first reads in the whole file (at least it used to), unlike -head.
>not had any success yet with any variations of your last code
Can 't really help any further. It is that old story: works fine here. As you can see in the following screen shots (was going to be a video, but this site doesn't seem to support them); the only real difference is I am using my own csv instead of yours, and I slightly simplified the command line to avoid all those multiple " (we can replace " with ', but then we cannot use ` t to represent tab)
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 625
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Import the first 100 lines of text file into excel
OK, thanks, good to know, both the extra info and that it is working by you..
I'll experiment a bit further and post back if I have anything new
I'll experiment a bit further and post back if I have anything new
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Import the first 100 lines of text file into excel
Code: Select all
Sub M_snb()
With CreateObject("scripting.filesystemobject")
c00 = .opentextfile("G:\OF\adres.csv").readall
sn = Split(c00, vbCrLf)
.createtextfile("G:\OF\adres_009.csv").write Split(c00, sn(100))(0)
End With
End Sub