Import the first 100 lines of text file into excel

[BittenApple]

Hello team,
How to import the 100 lines of Text file into Excel?
Regards
Bittersweet

[HansV]

I'd import the entire file, then delete row 101 and below.

[SpeakEasy]

Or if feeling adventurous, you could try Power Query, which allows you to do much the same thing

[DocAElstein]

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

ABCDEF.JPG

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

[BittenApple]

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

[SpeakEasy]

>VBA is not a solution

Why not?

[BittenApple]

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.

[rory]

Why Excel?

[SpeakEasy]

>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 ...

[DocAElstein]

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

[SpeakEasy]

Sure, why not ...

(Get-Content bigtext.csv -Head 100) -replace ",","`t" | set-clipboard

[PJ_in_FL]

After trying to decipher PowerShell scripts, I'm beginning to believe APL was actually very intuitive...

APL_Pgm.png

[HansV]

APL! Ah, the memories...

[DocAElstein]

After trying to decipher PowerShell scripts, I'm beginning to ....

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….._
_.______________________________________________________

_.....

Sure, why not ...(Get-Content bigtext.csv -Head 100) -replace ",","`t" | set-clipboard

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

[SpeakEasy]

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:

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

[DocAElstein]

... 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 . ....

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.
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

[SpeakEasy]

>-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)

[DocAElstein]

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

[snb]

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