Gurus help needed Windows batch script to convert text file into csv file


jimbo45

Well-known member
Power User
VIP
Local time
8:22 PM
Posts
2,020
Location
Hafnarfjörður IS
OS
Windows XP,7,10,11 Linux Arch Linux
Hi folks
Any Windows Gurus out there
I'm trying to replicate this Linux process to run in batch to convert a text file into CSV on Windows and then into EXCEL - once I've got the CSV file then I'm happy to run EXCEL normally

sed 's/ \+/,/g' ifile.txt > ofile.csv

my input file is a list of directories and files via ls -Rl >ifile.txt
Screenshot_20220407_133323.png

so I've got something like this per directory

Screenshot_20220407_133505.png

I'm sure it's a doddle in Windows but I'm totally not used to Windows batch things !!!

cheers
jimbo
 

My Computer

System One

  • OS
    Windows XP,7,10,11 Linux Arch Linux
    Computer type
    PC/Desktop
    CPU
    2 X Intel i7

Berton

Well-known member
Power User
VIP
Local time
2:22 PM
Posts
1,021
Location
Buffalo, Wyoming
OS
Win11 Pro RTM
Maybe no help but I just did an Export of my Logins in my browser and saved as a .csv file. One thing I saw opening it in Notepad shows every item that belongs in a cell in a spreadsheet has double-quotes at their beginning and their end with the comma separating them between the double-quotes as in,
"https://secure.techrepublic.com","xxxxxx@xxxxxxxxxxxxxxxxx.com","xxxxxxxxxxx",,"https://secure.techrepublic.com","{21a18294-e47a-4f40-80bd-,
the xxx's indicate user name and password for the site. The data between the double-quotes go into a cell by itself and the comma jumps to the next cell to enter following data. Two commas or more in a row indicate a blank cell or cells

As for a .bat/batch file, I use them mostly to run commands, create a .bat file once and don't have to keep re-typing a list of commands to be run when doing it frequently.
 

My Computers

System One System Two

  • OS
    Win11 Pro RTM
    Computer type
    Laptop
    Manufacturer/Model
    Dell Vostro 3400
    CPU
    Intel Core i5
    Memory
    8GB
    Hard Drives
    256GB SSD NVMe
  • Operating System
    Windows 11 Pro RTM x64
    Computer type
    PC/Desktop
    Manufacturer/Model
    Dell Vostro 5890
    CPU
    Intel Core i5
    Memory
    16GB
    Graphics card(s)
    Onboard, no VGA, using a DisplayPort-to-VGA adapter
    Monitor(s) Displays
    21"
    Hard Drives
    512GB SSD NVMe, 2TB WDC HDD
    Browser
    Firefox, Edge
    Antivirus
    Windows Defender/Microsoft Security

Pirilampo2

Well-known member
Member
Local time
9:22 PM
Posts
153
Location
Abrantes, Santarém, Portugal
OS
windows 11 Pro 64-bits 21H2 22000.708

My Computers

System One System Two

  • OS
    windows 11 Pro 64-bits 21H2 22000.708
    Computer type
    PC/Desktop
    Manufacturer/Model
    Custom self built
    CPU
    AMD Ryzen 3 2200G with Radeon Vega Graphics 3.50 GHz
    Motherboard
    MSI B350 PC MATE
    Memory
    16,00 GB TeamGroup DDR4-2667
    Graphics Card(s)
    Nvidia GeForce GT 730 & Radeon™ Vega 8 Graphics
    Sound Card
    nVIDIA GK208 HDMI/DP High Definition Audio Controller
    Monitor(s) Displays
    AOC 27"
    Screen Resolution
    3840x2160
    Hard Drives
    Seagate 1TB BarraCuda 64MB 7200rpm SATA III 3.5 - ST1000DM010
    PSU
    LC-Power 650W V2.3
    Case
    ATX Nox Hummer ZS
    Cooling
    No cooling
    Keyboard
    Logitech
    Mouse
    Logitech
    Internet Speed
    500/100
    Browser
    Firefox 100.0.2
    Antivirus
    Windows Defender
    Other Info
    Optical Drive ASUS DRW-24D5MT
  • Operating System
    Windows 11 Pro (x64) 21H2 Build 22000.708
    Computer type
    Laptop
    Manufacturer/Model
    Asus VivoBook Max X541NA
    CPU
    Intel Celeron N3350 @ 1.10GHz
    Motherboard
    ASUS X541NA
    Memory
    4 GB
    Graphics card(s)
    Intel Apollo Lake SoC - Graphics and Display Controller (12 EU) [B1] [ASUS]
    Sound Card
    Intel Apollo Lake SoC - High Definition Audio Controller
    Monitor(s) Displays
    NT156WHM-N42 15.6"
    Screen Resolution
    1366 x 768
    Hard Drives
    Seagate ST1000LM035-1RK172
    PSU
    Asus Battery
    Internet Speed
    500 Mbps / 100 MBps
    Browser
    Firefox 100.0.2

geneo

Well-known member
Power User
VIP
Local time
4:22 PM
Posts
1,391
OS
Windows 11 Pro x64
Hi folks
Any Windows Gurus out there
I'm trying to replicate this Linux process to run in batch to convert a text file into CSV on Windows and then into EXCEL - once I've got the CSV file then I'm happy to run EXCEL normally

sed 's/ \+/,/g' ifile.txt > ofile.csv

my input file is a list of directories and files via ls -Rl >ifile.txt
View attachment 26383

so I've got something like this per directory

View attachment 26384

I'm sure it's a doddle in Windows but I'm totally not used to Windows batch things !!!

cheers
jimbo

You can do that directly in excel, then save it as CSV if that is what you want. Home -> data -> From text/CSV

Or you could use windows susbsystem for linux to do the sed.

But the first is the simplest unless you have a batch of files to convert.
 
Last edited:

My Computers

System One System Two

  • OS
    Windows 11 Pro x64
    Computer type
    PC/Desktop
    Manufacturer/Model
    DIY
    CPU
    10900K, 5.2 GHz all-core
    Motherboard
    Asus Maximus Hero XIII Wifi
    Memory
    32GB G.skill TridentZ RGB 3200CL14 B-die @ 4100 MHz 16-16-34
    Graphics Card(s)
    Asus ROG Strix 2070 Super A8G
    Sound Card
    Onboard Audio, Vanatoo Transparent One; Klipsch R-12SWi Sub
    Monitor(s) Displays
    Eizo CG2730, ViewSonic VP2768
    Screen Resolution
    2560 x 1440p x 2
    Hard Drives
    WDC SN850 1TB (OS+), Samsung 980 1TB (games), Raid 0: 1TB 850 EVO + 1TB 860 EVO. Sabrent USB-C DS-SC5B docking station: 6TB WDC Black, 6TB Ironwolf Pro; 2TB WDC Black
    PSU
    750W Seasonic Prime Ultra Titanium Plus
    Case
    Fractal Design Meshify 2 dark tint glass
    Cooling
    EK-AIO 360 D-RGB w/Phanteks T30-120 fans, Noctua NF-A14 Chromax case fan
    Keyboard
    Glorious GMMK TKL - Brown mechanical, lubed modded
    Mouse
    Logitech G305 wireless gaming
    Internet Speed
    350 Mb/s down, 12 Mb/s up
    Browser
    Firefox
    Antivirus
    Defender, Macrium Reflect 8 ;-)
    Other Info
    Logitech C920e Webcam (crap don't buy)
  • Operating System
    Mac OS
    Computer type
    Laptop
    Manufacturer/Model
    Apple 13" Macbook Pro 2020 (m1)
    CPU
    M1
    Monitor(s) Displays
    2560x1600

play2mefish

Member
Local time
12:22 PM
Posts
18
OS
Windows 11 Pro
Or you could do this with Powershell

Get-ChildItem *.txt | Rename-Item -NewName { $_.Name -replace '.txt','.csv' }
 

My Computer

System One

  • OS
    Windows 11 Pro
    Computer type
    PC/Desktop
    Manufacturer/Model
    Home Built
    CPU
    AMD Ryzen 7 5800X
    Motherboard
    GIGABYTE B550 AORUS ELITE AX V2
    Memory
    32GB
    Graphics Card(s)
    Nvidea??
    Sound Card
    MB Sound only
    Hard Drives
    m2.nvme
    PSU
    Seasonic Focus
    Case
    Cougar
    Cooling
    Enermax Liquid CPU
    Keyboard
    Logitech K800
    Mouse
    Logitech MX Master 3
    Internet Speed
    2.5 GB
    Browser
    BRAVE
    Antivirus
    Windows Defender

jimbo45

Well-known member
Power User
VIP
Thread Starter
Local time
8:22 PM
Posts
2,020
Location
Hafnarfjörður IS
OS
Windows XP,7,10,11 Linux Arch Linux
Or you could do this with Powershell

Get-ChildItem *.txt | Rename-Item -NewName { $_.Name -replace '.txt','.csv' }
Thanks

Doing it in EXCEL is fine for small worksheets but powershell is a great way.

Subsystem for Linux (WSL) is OK too but that means installing it and some of the HYPER-V Virtual machine infrastructure which I don't really want on a lean and mean system which I mainly use for Office type apps photoshop and some work orientated applications.

cheers
jimbo
 

My Computer

System One

  • OS
    Windows XP,7,10,11 Linux Arch Linux
    Computer type
    PC/Desktop
    CPU
    2 X Intel i7

BunnyJ

Python Programmer
Guru
VIP
Local time
4:22 PM
Posts
5,598
Location
USA TN
OS
Windows 11 Home 25206.1000 - Dev Channel
I've been using this for file format conversions.. it's fast and free

 

My Computer

System One

  • OS
    Windows 11 Home 25206.1000 - Dev Channel
    Computer type
    PC/Desktop
    Manufacturer/Model
    Banna Junior 7000 - X Series
    CPU
    Ryzen 7 2700X
    Motherboard
    Asus Crosshair VII Hero, X470
    Memory
    Gskill 32GB, 16GBX2 PC3000
    Graphics Card(s)
    GeForce GTX TITAN X 12GB
    Sound Card
    Realtek (R) Audio
    Monitor(s) Displays
    ASUS VP28UQG 28" 4K, Viotek 32" 1080p
    Screen Resolution
    4K 3840 x 2160
    Hard Drives
    Primary Samsung 256 SSD
    PSU
    EVGA BQ 700w 80+ Bronze
    Case
    NZXT 510
    Cooling
    Stock AMD cooler
    Keyboard
    Corsair
    Mouse
    Amazon Generic with Cord
    Internet Speed
    Download: 350.50 mbps Upload: 12.02 mbps Ping: 15ms
    Browser
    Firefox, Chrome and Edge
    Antivirus
    MS - Defender

grumpus

Member
Local time
2:22 PM
Posts
30
OS
Windows 10, Fedora 34
The previously offered powershell command renames the file without altering the contents. Try this:

Code:
get-content ifile.txt | %{$_ -replace "  *", ","} > ofile.csv

Note that after the -replace, there are 2 spaces before the asterisk. This should be equivalent to your sed command, replacing every sequence of 1 or more spaces with a single comma on each line of ifile.txt, writing the output to ofile.csv.
 

My Computer

System One

  • OS
    Windows 10, Fedora 34
    Computer type
    Laptop
    Manufacturer/Model
    Lenovo W530
    CPU
    i7-3720QM @ 2.60Ghz
    Memory
    32GB
    Graphics Card(s)
    Intel HD Graphics 4000, NVIDIA Quadro K2000M
    Hard Drives
    Samsung SSD 860 EVO 1TB

Latest Tutorials

Top Bottom