Parse Text File

Amm85:

Here is a sample text file "data.txt" that contains data with 3 columns - product id, product qty and product price. Note, my example includes empty rows and empty columns.

345<tab>7<tab>29.75

123<tab><tab>30.90

Here is the script that will generate the sql statement to insert this data into table "product". The sql statement is created in variable $sql.

var str sql ; set $sql = "INSERT INTO product VALUES "

# Read file in.

var str data ; cat "data.txt" > $data

# We will add a comma before each row except the first row.

var  bool firstRow ; set $firstRow = true

# Get one line at a time.

while ($data <> "")

do

    var str row ; lex -e "1" $data > $row

    # Convert all tabs to commas.

    while ( { sen "^\t^" $row } > 0 )

        sal "^\t^" "," $row > null

    # Add this row to sql.

    if ( $firstRow )

        # We will NOT ADD a comma before this row.

        set $sql = $sql + "(" + $row + ")"

    else

        # We WILL ADD a comma before this row.

        set $sql = $sql + ", (" + $row + ")"

    endif

    set $firstRow = false

done

echo $sql

The above is in biterscripting ( http://www.biterscripting.com ) .

Richard