How to put data into mysql database from .txt file using batch script?

Question

I have a database on my localhost, and I need to put data into MySQL database using batch script. Is this possible? Those values are in .txt files But this doesn't work.

Here is my whole script:

@echo on
setlocal ENABLEDELAYEDEXPANSION
set vidx=0
for /f "tokens=*" %%A in (C:\Users\Diana\Desktop\names.txt) do (
    SET /A vidx=!vidx! + 1
    snmpwalk -v 2c -c root %%A .1.3.6.1.2.1.25.3.3.1.2 > C:\Users\Diana\Desktop\cpu.txt
    snmpwalk -v 2c -c root %%A .1.3.6.1.2.1.25.5.1.1.2 > C:\Users\Diana\Desktop\ramvid.txt
    snmpwalk -v 2c -c root %%A .1.3.6.1.2.1.25.2.2 > C:\Users\Diana\Desktop\ram.txt
)

for /f "tokens=4" %%B IN (C:\Users\Diana\Desktop\ram.txt) DO echo %%B >> C:\Users\Diana\Desktop\ramfiltruotas.txt
for /f "tokens=4" %%B IN (C:\Users\Diana\Desktop\cpu.txt) DO echo %%B >> C:\Users\Diana\Desktop\cpufiltruotas.txt

for /f "tokens=4" %%a in (C:\Users\Diana\Desktop\ramvid.txt) do set /a used+=%%a 
echo !used! > C:\Users\Diana\Desktop\naujas.txt

for /f "tokens=4" %%c in (C:\Users\Diana\Desktop\ram.txt) do set /a total=%%c
set /a ramai=(%used%*100)/%total%
echo %ramai% > C:\Users\Diana\Desktop\naujas2.txt

for /f "tokens=4" %%d in (C:\Users\Diana\Desktop\cpu.txt) do set /a e+=%%d
set /a cpu=(%e%/4)
echo %cpu% > C:\Users\Diana\Desktop\naujas3.txt

mysql --host=localhost --user=root --password= --database=database <
    INSERT INTO server (cpu, ram)
    WHERE ('server_name' LIKE '192.168.95.139'
    VALUES (%cpu%,%ramai%)

What exactly are you trying to do here? I mean the functional requirement of your script? — Mark, May 30 '13 at 03:46
I have a .txt file, in that file i have one number - CPU load. The value %a% is that numbet because i have calculated it before in the same script, i just didn't posted the beginning of the script. I need to put that number into my database that i could see it in my webpage. — , May 30 '13 at 03:48
So you are trying to compute the CPU usage as you run the batch script and put it in your database am I right? — Mark, May 30 '13 at 03:50
Eugen, UPDATE doesn't work either, something is wrong with the syntax. — , May 30 '13 at 03:53
Show us the actual txt file, and the schema of the server table — Kenji Noguchi, May 30 '13 at 03:53
What system? Or OS are we talking about here? If windows, I have an interesting post [here](http://stackoverflow.com/questions/15878988/executing-sql-script-on-a-batch-file-with-accent) — Mark, May 30 '13 at 03:53
Kenji, in the txt file there is only one number 50; the table server has server_name, id, ram, cpu and more other parameters. — , May 30 '13 at 04:02
Christian, but what should i writo into dbase.sql to take those parameters I need to insert from a .txt file ? — , May 30 '13 at 04:03
In Unix shell it's typically `echo "UPDATE server SET cpu='$cpu', ram='$ram' where server_name='192.168.95.139'" | mysql --host=localhost --user=root --password= --database=database` I can't remember Windows cmd syntax, but it should be very similar. — Kenji Noguchi, May 30 '13 at 04:11

score 0 · Answer 1 · answered May 30 '13 at 04:15

I just woke up and didn't have coffee yet, but... the way I see it the where clause in your insert statement makes no sense. Try:

INSERT INTO server (cpu, ram , server_name )
VALUES (%cpu%,%ramai%,'192.168.95.139')

instead. This assumes that %cpu% and %ramai% will be replaced with the correct values and that cpu and ram are numeric types in the database. If these are string types, add single quotes, eg ... VALUES('%cpu%','%ramai%','192.168.23.139')

if you want to update existing rows instead of inserting new ones, it would be something like

UPDATE server 
SET cpu=%cpu%, ram=%ramai%
WHERE server_name = '192.168.95.139'

C:\Users\Diana>echo 0 1>C:\Users\Diana\Desktop\naujas3.txt The syntax of the command is incorrect. C:\Users\Diana>mysql --host=localhost --user=root --password= --database=database < C:\Users\Diana> — , May 30 '13 at 04:21

How to put data into mysql database from .txt file using batch script?

1 Answers1