Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

**DocAElstein** · 08-25-2023, 11:57 AM

mmghg

**DocAElstein** · 08-25-2023, 01:51 PM

jhkjhh

**DocAElstein** · 08-26-2023, 12:15 AM

vmvv

**DocAElstein** · 08-26-2023, 09:35 PM

hkjhg

**DocAElstein** · 09-02-2023, 11:38 AM

Some Extra notes for this forum post
https://eileenslounge.com/viewtopic....310565#p310565
https://eileenslounge.com/viewtopic....295906#p295906 Macro Correction(.txt to xlsb)

**DocAElstein** · 09-02-2023, 11:48 AM

Some Extra notes for this forum post
https://eileenslounge.com/viewtopic....310565#p310565
https://eileenslounge.com/viewtopic....310720#p310720

Here is an interesting use of the "scripting.filesystemobject"
Question: extract only a .csv name file from url string, for example:
ANPR_archivio_comuni.csv
comuni.csv
cap.csv
comuni_codici-catastali.csv
stemmi.csv
, from
https://www.anagrafenazionale.intern...vio_comuni.csv
https://raw.githubusercontent.com/op...ati/comuni.csv
https://raw.githubusercontent.com/op...n/dati/cap.csv
https://raw.githubusercontent.com/op...-catastali.csv
https://raw.githubusercontent.com/op...ati/stemmi.csv
Answer:
The answer here almost works, there is the deliberate mistake of a missing "…. Remember, snb, " In VBA almost every double quote should be followed by a 'closing' quote" … do that and then it works
join(filter(split(replace(createobject("scripting.filesystemobject").opentextfile("G:\OF\nomefilecsv.txt").readall,vbcrlf,"/"),"/"),".csv"),vblf)
Lets look at that
It is a "one liner". Maybe we can use the ideas there to get a whole text file.
Here it is, broken down / opened up, for future reference.

Code:

'   https://eileenslounge.com/viewtopic.php?p=310565#p310565
'Sub M_snb()
'   msgbox join(filter(split(replace(createobject("scripting.filesystemobject").opentextfile("G:\OF\nomefilecsv.txt).readall,vbcrlf,"/"),"/"),".csv"),vblf)
'End Sub
Sub M_snb()
Dim vTemp As Variant
 Let vTemp = CreateObject("scripting.filesystemobject").opentextfile("" & ThisWorkbook.Path & "\nomefilecsv.txt").readall
'https://www.anagrafenazionale.interno.it/wp-content/uploads/ANPR_archivio_comuni.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/cap.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni_codici-catastali.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv
 
 Let vTemp = Replace(vTemp, vbCr & vbLf, "/")
 '  https://www.anagrafenazionale.interno.it/wp-content/uploads/ANPR_archivio_comuni.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/cap.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni_codici-catastali.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv

 Let vTemp = Split(vTemp, "/") '                                                  
 '  https://i.postimg.cc/gjnjtx2S/v-Temp...Temp-slash.jpg
 
 Let vTemp = Filter(Sourcearray:=vTemp, Match:=".csv", Include:=True, Compare:=vbBinaryCompare)   ' 
 '  https://i.postimg.cc/0jpzgbS9/v-Temp...-Match-csv.jpg
 
 
 Let vTemp = Join(vTemp, vbCr & vbLf)
'ANPR_archivio_comuni.csv
'comuni.csv
'cap.csv
'comuni_codici -catastali.csv
'stemmi.csv

Idea for a "one liner" to
_ bring a text file into an Excel worksheet,
or
_ bring a text file into a VBA variable
The idea, summarised in words is, to do, all in one line …
_The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentextfile("TextFile") .readall thing
_ the line separator ( most likely the invisible character pair, vbCr & vbLf ) will be replaced by the column separator , the thing which most commonly is the comma ,
_ the Split of the modified string by the , separator , to give a long single array 1 dimensional array,
_ finally the 2Darray = Index ( 1Darray, Rws , Clms() ) ideas , for example here , https://www.excelfox.com/forum/showt...3287#post23287 , will be used to give us the final array or range of values

First try with simple 3 row, 2 column array
a, b
c, d
e, f

I made that manually. Here is a quick check on what it is made up of:
Using this,

Code:

Sub QuickTxtFileCheck()
Rem 0 Path info
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "3Row2ColumnTextFile.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

' My usually used way
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
Debug.Print TotalFile
'    a , b
'    c , d
'    e , f
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
'    "a" & "," & "b" & vbCr & vbLf & "c" & "," & "d" & vbCr & vbLf & "e" & "," & "f"

' using the CreateObject("scripting.filesystemobject").opentextfile("TextFile") thing
 Let TotalFile = CreateObject("scripting.filesystemobject").opentextfile(PathAndFileName).readall
Debug.Print TotalFile
'    a , b
'    c , d
'    e , f
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
'    "a" & "," & "b" & vbCr & vbLf & "c" & "," & "d" & vbCr & vbLf & "e" & "," & "f"
End Sub

, we get results of this form for its character contents,

06 Sep 2023 Lenf is 13	a,b c,d e,f	06 Sep 2023 Lenf is 13	a,b c,d e,f
1 a	97	1 a	97
2 ,	44	2 ,	44
3 b	98	3 b	98
4	13	4	13
5	10	5	10
6 c	99	6 c	99
7 ,	44	7 ,	44
8 d	100	8 d	100
9	13	9	13
10	10	10	10
11 e	101	11 e	101
12 ,	44	12 ,	44
13 f	102	13 f	102

, so no surprise, all looks as expected.

Some coding attempts in the next post

**DocAElstein** · 09-02-2023, 11:48 AM

FileSystemObject OpenTextFile ReadAll One liner

_……. Continued from last post
This basic coding does the job, if we know the dimensions of the text file, for example that the rows are 3 and the columns 2

Code:

Sub FileSystemObjectOpenTextFileReadAll()      '  https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23290&viewfull=1#post23290
Dim vTemp As Variant
 Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall  '  _The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentextfile("TextFile").readall thing
 Let vTemp = Replace(vTemp, vbCr & vbLf, ",", 1, -1, vbBinaryCompare)               '  _ the line separator ( most likely the invisible character pair, vbCr & vbLf ) will be replaced by the column separator , the thing which most commonly is the comma  ,
 Let vTemp = Split(vTemp, ",", -1, vbBinaryCompare)                                 '  _ the split of the modified string by the  ,  separator , to give a long single array 1 dimensional array,
 Let vTemp = Application.Index(vTemp, 1, Evaluate("=COLUMN(A:B)+((Row(1:3)-1)*2)")) '  _ finally the 2Darray = Index ( 1Darray, Rws , Clms() ) ideas , for example here , https://www.excelfox.com/forum/showt...3287#post23287 , will be used to give us the final array or range of values
' Or
 Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:B)+((Row(1:3)-1)*2)"))
' Or
 Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
'  '    
 Let Range("A20").Resize(3, 2) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
End Sub

To make it more flexible, we would need to get the dimensions of the text file from the text file, in the last example that would be to get the column count 2 and row count 3 in the final formula
………..Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))

This next code gets you there,

Code:

Sub FileSystemObjectOpenTextFileReadAll_()      '  https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23290&viewfull=1#post23290
Dim vTemp As Variant
 Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
Dim RwsCnt As Long, ClmsCnt As Long
 Let RwsCnt = 3: ClmsCnt = 2
 Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, ClmsCnt).Address, "$")(1) & ")+((Row(1:" & RwsCnt & ")-1)*" & ClmsCnt & ")"))
 
 ' to get the row count, we look at the (difference in length between the main string and the main string less the vbCr & vbLf pairs) / 2   and then add 1 to that since we have 1 less  vbCr & vbLf pairs  than there are rows
 Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall()  '  _The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentextfile("TextFile").readall thing
 Let vTemp = (Len(vTemp) - Len(Replace(vTemp, vbCr & vbLf, "", 1, -1, vbBinaryCompare))) / 2 + 1
' or
 Let RwsCnt = (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1

' to get the column count get the (difference in length in a line and the line without any seperator) + 1  since there willl be one more columns than there are seperators  ,
 Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()  '
 Let vTemp = (Len(vTemp) - Len(Replace(vTemp, ",", "", 1, -1, vbBinaryCompare))) + 1
' or
 Let ClmsCnt = (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1

' So
 Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
 Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
' or
 Let Range("A20").Resize(RwsCnt, ClmsCnt) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
 Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
 Let Range("A20").Resize((Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
 Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
'    
End Sub