Automatically copy data from a text file into excel

[simon1516]

Hello everybody,

I would like to have data from a text file (.txt) automatically written to an Excel spreadsheet.

The text file looks something like this:

bla bla bla
bla bla bla
bla bla bla
random_number:'xyz'
bla bla bla
bla bla bla
bla bla bla
random_number:'abc'

Now the result should be an excel file in which column xyz and column b are abc ... there should be written a total of about 100 of these numbers in 100 consecutive columns.

My search brought me only results that brought similar results. Because I'm a beginner, the question:

Could someone write me a corresponding batch file?

Thank you in advance,

greeting Simon

[SIMMS7400]

Can you provide us an example of your final data set in excel?

Why don't you just convert the txt to xlsx instead of worry about copying content from source to target? Unfortunately, batch can't interact with excel very well (or at all really to do what you want it to) so kick off VB script with batch - you can get fancy and pass in parameters too.

Then, once you have converted to a workbook, then do the transformation there.

To get you started, try the below vb script. Once you provide a final data set we can assist with the additional VB logic.

Code: Select all

'The text file to be converted
strInput = "c:\temp.txt"
 
'The Excel file to be created
strOutput = "c:\temp.xlsx"
 
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
 
Set objWorkbook = objExcel.Workbooks.Open(strInput)
 
objExcel.ActiveWorkbook.SaveAs strOutput, 1
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

Paste into text editor and save as .vbs. You can just execute the VB script for now and then get fancy later.

[simon1516]

Hello SIMMS7400,

thank you very much for your help. I try to give you some Information

As soon as I try to open the generated excel file, I get the following error message:
the file "temp.xlsx" can be opened by excel nciht because the file format or the file extension is invalid. Check if the file is corrupted and if the file extension matches the file format.

An example of what the finished excel file should look like is attached.

the keyword "random_number" in the .txt file is always the same (it should be searched for). the content behind this keyword should then be listed in the excel file as mentioned above (one value per column).

Thank you in advance,

Simon

[ShadowThief]

Change the 1 at the end of the SaveAs line to a 51.
https://msdn.microsoft.com/en-us/VBA/Ex ... tion-excel

[simon1516]

Hello everybody,

thank you very much for your help.

Now, however, the entire content of the text file is copied to excel.

I would like, however, that only certain values ​​are copied, which occur as follows in the text file:

Random number: 'A2 8D 75 31'

in this example column 1 of excel file should be "A2 8D 75 31".

the excel file has then "n" columns at the end. "n" stands for the number of hits found.

could someone write me a piece of code?

Thank you in advance.

Simon

[SIMMS7400]

Can you provide a sample of the text file with actual data not 'blah blah blah'

[simon1516]

Hello SIMMS7400,

Unfortunately, I could not copy a text document in the appendix. so i copied the content from an example .txt. You will often find the entry "random_number" there. these values ​​I would like to end up next to each other, in columns in an excel table

In the following content the excel table would show 7 columns with the content 16 33 18 32

Thank you for your help!

.txt file Content:

Code: Select all

Hinweis : Ziel- Datei 'L:\ft' existiert
eits (wird nicht ueberschrieben)!
Hinweis : Ziel- Datei 'L:\ft' existiert
eits (wird nicht ueberschrieben)!
Hinweis : Ziel- Datei 'L:\ft' existiert
berschrieben)!
-------------------------------------------------------------------------------
Benutzer -     ersetzen...
-------------------------------------------------------------------------------
einlesen...
-------------------------------------------------------------------------------
Instanz 
-------------------------------------------------------------------------------

==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'
==============================================================================

==============================================================================
Vorgang   : 6464694
Durchlauf : br14
Liste     : wayne
Karte     : keine
obhouhs  : skript
Starts    : 1
Eingabe> <CONTROL><C> oder <CONTROL><BREAK>

Response APDU = '7  B7 - 97 53 A2 34 9C 20 D9 21
                 9F 2C D5 5C B5 2E 34 EB - 0D 90 B74C A7 A0
                 EA 48 E6 B6 FE E2 96 D7 - 71 9 F5D AC 44 52 12
                 DE 66 DE  6B - F1 D5 68 D37E 75 5B
                 CC 7A A2 2F 58 BA 5B 4D - FF A3 5 89 8F FD 60 CA
                 86 93 F6 A8 BB A2 - 66 90 A F9 EC 78 B8
                 2C 0B 536E 02 3E 61 - 90 21 3A3 15 0B FF
                 5F 49 C5 EF 8B 8C FA B9 - BC 8F 5D 1E 20 7B 66 70
                 94 D4 2C 15 0 -0'

              random_number:  '16 33 18 32'

[simon1516]

Hello guys,

do i have to give more informations?

it would be great, if someoe could help me

king regards,

simon

[SIMMS7400]

You wont get help by pestering us. I will try to send something over within the next day.

[Bit_Demon]

Might want to try powershell first. Its pretty good at this kind of task. Once you get the search strings you are looking for its easy to do a
replace "bla bla bla" with ",bla bla bla" in the output file you get running this script below. You basically build a CSV file that can be feed into a function
called CsvToExcel it will build a real Excel file from the comma delimited logs. I am not sure you realize what a job it is to convert parsed plain text into Excel. Findstring is great but powershell's Select-string is far better suited to doing this kind of job. I would google Regex101 and within an hour you will be on your way to targeting the data in the logs your after.
If your still reading this...
Make sure CsvToExcel.ps1 is in your - $path\Out_folder\ and you have excel installed.

Code: Select all

#
#$path = read-host "Enter the path to the log files."
# You can hard code the log path like below like this $path = "C:\logs\"
# or delete the # in the line above and add # at the start of the line below.
#$path = read-host "Enter the path to the log files."


$path = "C:\logs"
cd $path
$OutPath = "$path\Out_folder\"
#$Searchstring = read-host "Enter the string to be searched. "
$Searchstring =  "bla " , "bla " , "INSERT REGEX PATTERN" , "bla bla bla"

$Logs = Get-ChildItem -path $path -recurse -include *.txt
$logout = "$path\Out_folder\Logs_Containing_string_$log.txt"
$logout2 = "$path\Out_folder\Unique_String_descending_$log.txt"

# This will add your columns in your excel doc. Just edit column# in the line below. example $csvheader = "column1,column2,column3,"
$csvheader = "blah1,blah2,blah3,"


$counter = 1
    foreach($Log in $Logs){

Write-Progress -Activity "Checking: $Log" -Status "File $counter of $($Logs.count)" -PercentComplete ($counter*100/$Logs.count)  
$counter++
    

$StringExist = Select-string -Path $log.fullname -pattern $Searchstring
    if($StringExist)
      
        {
      
        write-host $Searchstring " found in " $Log.name -fore green
        $StringExist | Sort-Object -Unique | Select-Object | out-file  $logout -Append
        [string]$csvheader | Out-File $logout2  -append
        Get-Content $logout | Sort-Object -Unique | Select-Object | out-file $logout2 -Append
        $OutPath\CsvToExcel.ps1 -inputfile $logout2.csv -outputfile $logout3.xlsx
        
        }
        else
        {
            write-host "$Searchstring not found in " $Log.name -fore Yellow
        }
    }

OK I am ready to be flamed for my solution.
This forum has been great resource for years and I only trying to give back.
*locates fire extinguisher*
*puts on blind fold*
*dials 9 1 ...*

[simon1516]

Hello SIMMS7400,

I wanted to ask again friendly, if you help me even further

regards,

Simon

[aGerman]

Excel spreadsheets are not accessible using Batch. SIMMS7400 and Bit_Demon gave you examples how to start using other scripting languages. I can also only help out with VBS. It will only work on machines where Excel is installed and it will delete an already existing output file and write it new.

Code: Select all

Option Explicit

Const strInFile = "test.txt", strOutFile = "test.xlsx", _
      strPattern = "\brandom_number:\s+'([0-9A-F ]+)'"

Dim objFSO, objTxtFile, _
    objExcel, objWorkbook, objWorksheet, objRange, _
    objRegex, objMatch, _
    strFullOutName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxtFile = objFSO.OpenTextFile(strInFile)
strFullOutName = objFSO.GetAbsolutePathName(strOutFile)

Set objRegex = New Regexp
objRegex.Global = True
objRegex.IgnoreCase = False
objRegex.Pattern = strPattern

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.Range("A1")

For Each objMatch In objRegex.Execute(objTxtFile.ReadAll)
  objRange.Value = objMatch.SubMatches(0)
  Set objRange = objRange.Offset(0, 1)
Next

If objFSO.FileExists(strFullOutName) Then objFSO.DeleteFile(strFullOutName)
objWorksheet.SaveAs strFullOutName
objExcel.Application.Quit

Steffen