Code logic problem - transform lines to columns

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
mirrormirror
Posts: 129
Joined: 08 Feb 2016 20:25

Code logic problem - transform lines to columns

#1 Post by mirrormirror » 08 Jul 2020 10:01

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?

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: Code logic problem - transform lines to columns

#2 Post by aGerman » 08 Jul 2020 11:30

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

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Code logic problem - transform lines to columns

#3 Post by Aacini » 09 Jul 2020 05:25

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

mirrormirror
Posts: 129
Joined: 08 Feb 2016 20:25

Re: Code logic problem - transform lines to columns

#4 Post by mirrormirror » 09 Jul 2020 21:47

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.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: Code logic problem - transform lines to columns

#5 Post by aGerman » 10 Jul 2020 05:52

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

Post Reply