Automated file upload using asp

[ucfcarver]

Thank you for your help i have not tested it yet but will in the next little while

i got an error that i can not get rid of.

#!/usr/bin/env bash
while read line
do
tsql -H <server> -p <port> -U <user> -P <password>
Insert into Route_1
Values($line)
go
done</home/carver/rider1.txt


the error is
line10: syntax error near unexpected token '$line"
line10:' Values($Line)'

[debtboy]

Is this a bash error or Tsql?
If it's a Tsql error, look at the 10th line of your csv,
did it put the first 9 lines in?

If it's a bash error put double quotes around line like so..
VALUE("$Line")
Do you need a semi-colon?
Do a little debugging, (make some changes, see what happens)

You could also, comment out the tsql stuff and modify the script to echo $line
to the terminal, the reason is, there are few different types of csv files, some with
quotes, some without quotes, some mixed, etc...
Look at the data your getting and verify it's in the correct format
to put right into VALUES(), may need quotes, may have too many quotes
look at the data and try to determine if you would get an error by manually
putting that data in.
When you finished looking at data
remove the "#" marks from the Tsql stuff so it executes.

Let me know what happens.

#!/usr/bin/env bash
while read -r line
do
#tsql -H <server> -p <port> -U <user> -P <password>
#Insert into Route_1
#Values($line)
echo $line
go
done</home/carver/rider1.txt

[ucfcarver]

here is what i have learned.
1) having the tsql command in the while loop causes problems

i get e never ending output in the form 1> 2> 3> and it keeps counting up

2) moving the tsql command outside the loop makes that work right i get a connection to the database however the rest of the script does not seemto do anything

3) i have tried breaking up the line input into the different field and i still get a syntax error on the line with the value()

4)using the script no data has been added to the databse

#!/usr/bin/env bash



tsql -H <server> -p <port> -U <user> -P <password>

sleep 5

while read line
do


  Route="$(echo $line | cut -d, -f1)"
  Stop="$(echo $line | cut -d, -f2)"
  Change="$(echo $line | cut -d, -f3)"
  Passangers="$(echo $line | cut -d, -f4)"
  Time="$(echo $line | cut -d, -f5)"

echo $Route
echo $Stop


done < /home/carver/rider1.txt

i added the sleep thinking if there was a pause to allow the server connection the rest of the script would run but it didn't help

5) adding " " or ' ' did not change the syntax error and the data looks fine and entering the exact same data in by hand works

[debtboy]

Ha! Ha! Ha!
Be patient,
If it works manually on the command line then
it will work in a script.

Prior to posting my initial solution, I created a small csv file
and looped through the output displaying lines and also
cutting things up to display individual values.
So I KNOW the looping script works.
NOTE: I did not use quotes inside my csv file.
The only variable left in question was Tsql which works
manually.

Why not give all the information you can so my script
will look very much like the end result.

Additional note:
Be careful of your syntax, the loop you posted will not work

while read line
do


  Route="$(echo $line | cut -d, -f1)"
  Stop="$(echo $line | cut -d, -f2)"
  Change="$(echo $line | cut -d, -f3)"
  Passangers="$(echo $line | cut -d, -f4)"
  Time="$(echo $line | cut -d, -f5)"

echo $Route
echo $Stop


done < /home/carver/rider1.txt

Here is my sample csv file located in the same directory
as my shell script, I'll name it rider1.txt

one1,one2,one3,one4
two1,two2,two3,two4
three1,three2,three3,three4
four1,four2,four3,four4

Here is the same loop you posted, but slight syntax
adjustments were needed, I named it csv_script.sh

#!/bin/env bash

while read line
do

  Route="$(echo $line | cut -d, -f1)"
  Stop="$(echo $line | cut -d, -f2)"
  Change="$(echo $line | cut -d, -f3)"
  Passangers="$(echo $line | cut -d, -f4)"
  Time="$(echo $line | cut -d, -f5)"

echo $Route
echo $Stop

done < ./rider1.txt

I make it executable by:
chmod 777 ./csv_script.sh

Here is my shell output:

[debtboy@nixbox ~]$ chmod 777 ./csv_script.sh
[debtboy@nixbox ~]$ ./csv_script.sh
one1
one2
two1
two2
three1
three2
four1
four2
[debtboy@nixbox ~]$

Maybe your csv file structure is an issue and as
for the Tsql, there are many ways to resolve that.

I'll try to get another post in the next half hour.

I'll also be on tonight, don't get discouraged
it will work and you'll learn some scripting!!

[debtboy]

In this case, I made a slight change to the script file
putting everything in variables.
Same sample csv file rider1.txt

Here is a modified script:

#!/bin/env bash

Tsql_auth='tsql -H <server> -p <port> -U <user> -P <password>'
Tsql_insert='INSERT INTO Route_1 VALUES('
Tsql_end=') go'

while read line
do
Tsql_values="$line"  
Tsql_query="$Tsql_auth $Tsql_insert $Tsql_values $Tsql_end"

echo $Tsql_query
done < ./rider1.txt

Here is the shell output:

[debtboy@nixbox ~]$ ./csv_script.sh
tsql -H <server> -p <port> -U <user> -P <password> INSERT INTO Route_1 VALUES( one1,one2,one3,one4 ) go
tsql -H <server> -p <port> -U <user> -P <password> INSERT INTO Route_1 VALUES( two1,two2,two3,two4 ) go
tsql -H <server> -p <port> -U <user> -P <password> INSERT INTO Route_1 VALUES( three1,three2,three3,three4 ) go
tsql -H <server> -p <port> -U <user> -P <password> INSERT INTO Route_1 VALUES( four1,four2,four3,four4 ) go
[debtboy@nixbox ~]$

Starting to look better?
Experiment on the command line manually and find what works,
then we will duplicate that.

Additional thought...
If you have particular types of quotes in your csv file then we might have to
adjust for that. No problem, but we need to see some sample data.

[ucfcarver]

ok i copied your script and that is looking better.

this is what the text file looks like

1,1,1,1,'Fri Aug 28 14:18:21 2009'
1,2,3,4,'Fri Aug 28 14:18:23 2009'
1,3,4,8,'Fri Aug 28 14:18:25 2009'
1,4,6,14,'Fri Aug 28 14:18:27 2009'
1,5,7,21,'Fri Aug 28 14:18:29 2009'
1,6,11,32,'Fri Aug 28 14:18:31 2009'

i worked with the command line manually entering the values and if the ' ' are not there is chokes on the fri and the command can not handle it when i use " " instead.

Doing the insert manually you enter the tsql command hit enter
Then you type the insert, values and go lines hit enter and it inserts one row.

[debtboy]

I did a little research on Freetds and tsql.
It appears that tsql was designed only to test the connection.

We may have to use an ODBC after all,
still no big deal.

If possible, post the pertinent info from
/etc/freedts.conf

Is unixODBC installed on that system?

Look in synaptic or apt-get and let me know

[ucfcarver]

it looks like i have three packages installed with that name.

unixODBC
unixODBC-dev
unicODBC-bin

[debtboy]

AWESOME!!

Give me a little time to put something together.

[debtboy]

ucfcarver,
your problem is truly unique,
I can't seem to find a single example of anyone using
this freetds tabbed protocol successfully from the shell,
Python or Perl. Tsql was designed just for testing
and doesn't seem to work from within a script.

There are a few examples of it being used w/ php, but
that requires the web server apache, php, etc...
installed and configured on your Ubuntu machine.

There may be a solution, but I'm coming up empty right now.
Has anyone else been able to help??