From CSV to MySQL

Ignition
1

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

2

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.

1 Like
3

Thanks Travis, this worked great

4

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.

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.

5

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")