Solved Batch script fill in empty space

April 24, 2015 at 05:27:26
Specs: Windows 7
I have a text file with a list (fixed width), and need to fill in missing text where missing in the first two columns.

The list is like this:
J100920 39 MY100520 3.116E-03 -3.146E-02 1.437E-01 -6.513E-01 -9.267E-05 -2.291E-04
MY100920 -1.666E-04 4.455E-02 -1.437E-01 6.513E-01 9.271E-05 2.291E-04
J101020 31 MY100920 8.390E-03 -5.537E-02 1.731E-01 -1.031E+00 -5.143E-05 1.016E-02
MZ101020 6.032E-03 -1.763E-02 9.679E-02 1.008E-01 2.281E-02 -1.319E-05
MY101020 -2.399E-03 1.293E+00 -5.721E-02 8.392E-01 8.890E-06 9.955E-03
ME101020 8.392E-03 -1.118E+00 -1.695E+00 7.248E-02 2.810E-02 -1.831E-02
MC101020 6.317E-01 -2.000E-03 1.293E+00 1.847E-02 3.287E-02 -8.929E-03
MX101020 -5.376E-01 3.359E-03 1.890E-01 1.388E-04 -8.374E-02 7.136E-03

And this is how I want it to look like:
J100920 39 MY100520 3.116E-03 -3.146E-02 1.437E-01 -6.513E-01 -9.267E-05 -2.291E-04
J100920 39 MY100920 -1.666E-04 4.455E-02 -1.437E-01 6.513E-01 9.271E-05 2.291E-04
J101020 31 MY100920 8.390E-03 -5.537E-02 1.731E-01 -1.031E+00 -5.143E-05 1.016E-02
J101020 31 MZ101020 6.032E-03 -1.763E-02 9.679E-02 1.008E-01 2.281E-02 -1.319E-05
J101020 31 MY101020 -2.399E-03 1.293E+00 -5.721E-02 8.392E-01 8.890E-06 9.955E-03
J101020 31 ME101020 8.392E-03 -1.118E+00 -1.695E+00 7.248E-02 2.810E-02 -1.831E-02
J101020 31 MC101020 6.317E-01 -2.000E-03 1.293E+00 1.847E-02 3.287E-02 -8.929E-03
J101020 31 MX101020 -5.376E-01 3.359E-03 1.890E-01 1.388E-04 -8.374E-02 7.136E-03


See More: Batch script fill in empty space

Report •


#1
April 24, 2015 at 07:33:46
✔ Best Answer
The following script solves the issue if the first line is correctly formatted. Just curious, are data numbers in scientific notation?

@echo off & setlocal EnableDelayedExpansion
for /F "delims=" %%i in ('type "input.txt"') do (
  set row=%%i
  set cnt=0
  for %%l in (%%i) do set /A cnt+=1
  if !cnt! equ 7 (
    set row=!header! !row!
  ) else (
    for /F "tokens=1,2" %%j in ("%%i") do set header=%%j %%k
  )
  echo.!row!
) >> "output.txt"


Report •

#2
April 24, 2015 at 15:11:44
Thanks, this does the job. The columns are not perfectly aligned, but since I afterwards convert the list into CSV, this solved my problem. Now, the only thing left is to sort the table, starting with column #1, then 2 and 3. And, yes, the numbers are in scientific format, with fixed digits from a computer program listing).

message edited by IvarH1969


Report •

#3
April 25, 2015 at 07:33:56
Here a slightly modified version of the previous script that generates a sorted .csv output.

@echo off & setlocal EnableDelayedExpansion
for /F "delims=" %%i in ('type "input.txt"') do (
  set row=%%i
  set cnt=0
  for %%j in (%%i) do set /A cnt+=1
  if !cnt! equ 7 (
    set row=!header! !row!
  ) else (
    for /F "tokens=1,2" %%j in ("%%i") do set header=%%j %%k
  )
  set row=!row: =,!
  echo.!row!
) >> "output.tmp"
sort "output.tmp" /O "output.csv"
del "output.tmp"


Report •

Related Solutions

#4
April 26, 2015 at 23:30:56
Thanks again for help. The suggested sort and csv do not work properly, since a comma (,) is inserted for every space sign (several between each column). This is probably due to my initial list above seems to miss some formatting.

I used your first suggestion, then coverted to csv and finally did a sort like this:

for /f "tokens=1-9 delims= " %%A in ('type output.txt') do (
echo %%A;%%B;%%C;%%D;%%E;%%F;%%G;%%H;%%I >> "output1.csv"
)
sort "output1.csv" > "output.csv"

Hence, problem solved!

Here is link to my initial file (for clarity on formatting); http://1drv.ms/1OXjxrv

message edited by IvarH1969


Report •


Ask Question