Parsing text fields into excel columns
<p>I've attempted to parse out data that's over 20,000 records. Each record has 4 fields that's prefixed with 2 alphanumeric values. Below is an example with 2 records. I currently have a bloated solution that uses Java based on the link here: <a href="http://stackoverflow.com/questions/25491424/parsing-html-data-using-java-dom-parse">Parsing HTML Data using Java (DOM parse)</a>. But I'm not looking to use that solution as it is overkill for just seperating the records. Is there a solution that uses VBS, Python or any other language that can separate out the fields based on the logic I've used already? Or another logical approach?</p>

<pre><code>     100000000          SMP008483
  |---   Category                Western
  |---   Model                   Ford
  |---   Asset Delivered Date    ?
  |---   Scheduled               ?

     100000001          SMP008484
  |---   Category                Eastern
  |---   Model                   Chevrolet
  |---   Asset Delivered Date    ?
  |---   Scheduled               ?
</code></pre>

<p>Expected output is here:</p>

<pre><code>ID1            ID2          Category    Model       Asset Delivered Date    Scheduled                           
100000000      SMP008483    Western     Ford                ?                   ?
100000001      SMP008484    Eastern     Chevrolet           ?                   ?
</code></pre>

<p>This one is kinda crappy but it works. here you go:</p>

<pre><code>#!/bin/bash
i=0
while IFS= read -r line;do
echo $line | egrep -q '^[0-9]+'
if test $? -eq 0; then
  id1=$(echo $line | cut -d' ' -f1)
  id2=$(echo $line | cut -d' ' -f2)
  ((i++))
fi
echo $line | egrep -q 'Category'
if test $? -eq 0; then
  cat=$(echo $line | sed -e 's/^.*Category//')
  ((i++))
fi
echo $line | egrep -q 'Model'
if test $? -eq 0; then
  model=$(echo $line | sed -e 's/^.*Model//')
  ((i++))
fi
echo $line | egrep -q 'Asset Delivered Date'
if test $? -eq 0; then
  date=$(echo $line | sed -e 's/^.*Asset Delivered Date//')
  ((i++))
fi
echo $line | egrep -q 'Scheduled'
if test $? -eq 0; then
  sch=$(echo $line | sed -e 's/^.*Scheduled//')
  ((i++))
fi
if test $i -eq 5; then
  echo -e "${id1}\t${id2}\t${cat}\t${model}\t${date}\t${sch}"
  i=0
fi
done &lt;&lt;&lt; "$(cat ${1})"
</code></pre>

<p>*Do not forget of adding all fields, because it will break if you do.</p>

python excel parsing text vbscript