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


jimbo45

Well-known member
Pro User
VIP
Local time
3:38 PM
Posts
4,288
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
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 11th Gen. 2.40GHz
    Memory
    12GB
    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 10th Gen. 2.90GHz
    Memory
    16GB
    Graphics card(s)
    Onboard, no VGA, using a DisplayPort-to-VGA adapter
    Monitor(s) Displays
    24" Dell
    Hard Drives
    512GB SSD NVMe, 2TB WDC HDD
    Browser
    Firefox, Edge
    Antivirus
    Windows Defender/Microsoft Security

My Computers

System One System Two

  • OS
    Windows 11 Pro 64-bits 23H2 22631.2861
    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
    SSD Team Group T-Force Cardea Zero Z440 1TB Gen4 M.2 NVMe (5000/4400MB/s), SSD Team Group CX2 512GB SATA III (530/470MB/s) and 2x 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
    1000/200
    Browser
    Firefox 120.0.1
    Antivirus
    Windows Defender
    Other Info
    Optical Drive ASUS DRW-24D5MT
  • Operating System
    Windows 11 Pro 64-bits 22H2 22631.2861
    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
    1000 Mbps / 200 MBps
    Browser
    Firefox 120.0.1
    Antivirus
    Microsoft Windows Defender
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 Photoshop/Game/tinker build
    CPU
    Intel i9 13900KS P/E cores 5.9/4.4 GHz, cache 5.0 GHz
    Motherboard
    Asus ROG Maximus Z790 Dark Hero
    Memory
    64GB (2x32) G.skill Trident Z5 RGB 6400 MT/s
    Graphics Card(s)
    Asus ROG Strix 4070 Ti OC
    Sound Card
    Onboard Audio, Vanatoo Transparent One; Klipsch R-12SWi Sub; Creative Pebble Pro Minimilist
    Monitor(s) Displays
    Eizo CG2730 ColorEdge, ViewSonic VP2768
    Screen Resolution
    2560 x 1440p x 2
    Hard Drives
    WDC SN850 1TB nvme, SK-Hynix 2 TB P41 nvme, Raid 0: 1TB 850 EVO + 1TB 860 EVO SSD. Sabrent USB-C DS-SC5B 5-bay docking station: 6TB WDC Black, 6TB Ironwolf Pro; 2x 2TB WDC Black
    PSU
    850W Seasonic Vertex PX-850 ATX 3.0/PCI-E 5.0
    Case
    Fractal Design North XL Mesh, Black Walnut
    Cooling
    EKWB 360 Nucleus Dark AIO w/Phanteks T30-120 fans, 1 Noctua NF-A14 Chromax case fan, 1 T30-120 fan cooling memory
    Keyboard
    Keychron Q3 Max TKL with custom GMK Redsuns Red Samuri keycaps, TX Stabs
    Mouse
    Logitech G305 wireless gaming
    Internet Speed
    380 Mb/s down, 12 Mb/s up
    Browser
    Firefox
    Antivirus
    Defender, Macrium Reflect 8 ;-)
    Other Info
    Runs hot. LOL. SP: P116/E93/M93
    Phangkey Amaterasu V2 Desk Mat
  • Computer type
    Laptop
    Manufacturer/Model
    Apple 13" Macbook Pro 2020 (m1)
    CPU
    Apple M1
    Screen Resolution
    2560x1600
    Browser
    Firefox
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
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
I've been using this for file format conversions.. it's fast and free

 

My Computer

System One

  • OS
    Windows 11 Home(Release Preview) - 24H2 - 26100.1150
    Computer type
    PC/Desktop
    Manufacturer/Model
    Banana Junior 5600- G Series
    CPU
    AMD Ryzen 5 5600G
    Motherboard
    Asus ROG Strix B550-F
    Memory
    G.SKILL Ripjaws V Series 64GB 4x16
    Graphics Card(s)
    NVIDIA GeForce GTX TITAN X
    Monitor(s) Displays
    28" ASUS VP28U
    Screen Resolution
    4K
    Hard Drives
    Primary SAMSUNG 970 EVO Plus
    PSU
    EVGA BQ 700w 80+ Bronze
    Case
    Zalman i3 NEO
    Cooling
    ARCTIC Freezer 7 X
    Keyboard
    Corsair
    Mouse
    Amazon Generic with Cord
    Internet Speed
    Download: 295.11 mbps Upload: 65.35 mbps T-Mobile Internet
    Browser
    Firefox and Edge
    Antivirus
    MS - Defender
    Other Info
    Speakers: Klipsch ProMedia 2.1
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 7/10/11, Fedora 40
    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 Support Threads

Back
Top Bottom