# From CSV to MySQL

**URL:** <https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844>\
**Category:** Ignition\
**Created:** [January 11, 2012, 4:13pm UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844 "2012-01-11T16:13:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dfyanez](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@dfyanez](https://forum.inductiveautomation.com/u/dfyanez)\
**Post date:** [January 11, 2012, 4:13pm UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844/1 "2012-01-11T16:13:03Z")

</div>

Hey guys,  
Im really stuck trying to pull data from a csv files and insert it into a database, I’ve seen a example and i tested succesfully for 1 record on the file, but what im trying to do is get 3 different values

the code im using is:

adir = ‘C:\import\’  
rFile = open(adir + ‘file.txt’, ‘r’).readlines()

for line in rFile:  
print line

```
system.db.runUpdateQuery("INSERT INTO csv (job) values (%s)" %(line))

```

the file that Im trying to read is the following

job=J1234  
product=4321  
quantity=500

Sorry Im a newbie on python

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [January 11, 2012, 4:33pm UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844/2 "2012-01-11T16:33:39Z")

</div>

You can do the following:[code]adir = ‘C:\import\’  
rFile = open(adir + ‘file.txt’, ‘r’).readlines()  
job = rFile[0].replace(“job=”, “”)  
product = rFile[1].replace(“product=”, “”)  
quantity = rFile[2].replace(“quantity=”, “”)

print job, product, quantity[/code]Use those variables in your SQL query.

---

<div class="post-metadata">

**Author:** ![dfyanez](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@dfyanez](https://forum.inductiveautomation.com/u/dfyanez)\
**Post date:** [January 11, 2012, 5:24pm UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844/3 "2012-01-11T17:24:40Z")

</div>

Thanks Travis, this worked great

---

<div class="post-metadata">

**Author:** ![dfyanez](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@dfyanez](https://forum.inductiveautomation.com/u/dfyanez)\
**Post date:** [January 16, 2012, 11:05pm UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844/4 "2012-01-16T23:05:45Z")

</div>

Hey, going on the same kind of operation but now writing,  
what im trying to do now is to write a query from my DB to an txt file.  
Im totally lost on this.

i have tried the following but no luck so far.

```auto
resultset = system.db.runQuery("SELECT sum(produced), wo, circuit FROM prodkomax1 WHERE wo = '33'")
   if len(rs) > 0: 
      data = rs[0][0]
      filename = system.file.saveFile("c:\\control\file.txt")
      if filename != None:
         system.file.writeFile(filename, data)
```

also… is there any way to delete files from here? I mean, after make a read from a file delete it.

Thanks for your support.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [January 18, 2012, 2:05am UTC](https://forum.inductiveautomation.com/t/from-csv-to-mysql/3844/5 "2012-01-18T02:05:12Z")

</div>

I think you just want:`rs = system.db.runQuery("SELECT sum(produced), wo, circuit FROM prodkomax1 WHERE wo = '33'")
if len(rs) > 0: 
    data = rs[0][0]
    system.file.writeFile("c:\\control\file.txt", data, 1)`The 1 on writeFile appends it. Also to delete files you can do this:`import os
os.delete("C:\\path\\to\\file")`