Code logic problem - transform lines to columns

[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?

[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

[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

[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.

[aGerman]

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