Page 1 of 1
Code logic problem - transform lines to columns
Posted: 08 Jul 2020 10:01
by mirrormirror
Hey all, I have multiple text files from which I would like to extract information and import into a sqlite database. Here is how the format currently looks:
Code: Select all
CM004
Change Ticket
CM006
Solidifi & Change Management
Block 'Removable' Media
Forcepoint Social Media Block
DLP002
Forcepoint, Scanning
Web Content Filtering Standard
If I could reformat to something like this then I could do the import:
Code: Select all
CM004 Change Ticket
CM006 Solidifi & Change Management
CM006 Block 'Removable' Media
CM006 Forcepoint Social Media Block
DLP002 Forcepoint, Scanning
DLP002 Web Content Filtering Standard
The files are of varying lengths and content and as mentioned before, my end goal is to put into a sqlite database so I can run some queries.
Any ideas?
Re: Code logic problem - transform lines to columns
Posted: 08 Jul 2020 11:30
by aGerman
This may work for you.
Code: Select all
@echo off &setlocal DisableDelayedExpansion
set "infile=foo.txt"
set "outfile=bar.txt"
set "tab= "
set "firsttoken="
setlocal EnableDelayedExpansion
<"!infile!" >"!outfile!" (
for /f %%i in ('type "!infile!"^|find /c /v ""') do for /l %%j in (1 1 %%i) do (
if not defined firsttoken (
set /p "firsttoken="
) else (
set "line=" &set /p "line="
if not defined line (
echo(
set "firsttoken="
) else (
echo !firsttoken!!tab!!line!
)
)
)
)
Of course you have to update the file names.
Steffen
Re: Code logic problem - transform lines to columns
Posted: 09 Jul 2020 05:25
by Aacini
Code: Select all
@echo off
setlocal EnableDelayedExpansion
set "TAB= "
for %%f in (*.txt) do (
set "head="
(for /F "tokens=1* delims=:" %%a in ('findstr /N "^" "%%f"') do (
if not defined head (
set "head=%%b"
) else if "%%b" neq "" (
echo !head!!TAB!%%b
) else (
echo/
set "head="
)
)) > "%%~Nf.out"
)
Antonio
Re: Code logic problem - transform lines to columns
Posted: 09 Jul 2020 21:47
by mirrormirror
I wanted to say thank you so much to both of you for the help. I tried both of these solutions but did not get the solution from aGerman to work fully. It may be how I incorporated the code into my existing batch file. I plan to play around with it a bit more to see if I can find the problem. May I ask why you DisableDelayedExpansion for the variable setting at the beginning?
I did get the code from Aacini to work - thank you. I just need to tweak it to remove some blank lines in the output file.
Again, thanks to both of you. This really does help me out a great deal with the project I am working on.
Re: Code logic problem - transform lines to columns
Posted: 10 Jul 2020 05:52
by aGerman
mirrormirror wrote: ↑09 Jul 2020 21:47
May I ask why you DisableDelayedExpansion for the variable setting at the beginning?
That's just to be safe in case your real file names contain exclamation points.
Can't tell why the code doesn't work for you. I wasn't facing any problems for the snippet you provided. There are numerous possible reasons though. Beginning with the text encoding, empty lines not being empty, lines longer than 1021 characters, ... It's hard to guess without seeing your recent code.
Steffen