Using the following code to import csv:basically used this But the time column is imported in decimal values. I am using ODBC sqlite3 to connect this database to PowerBI locally. When i reformat the time column in PowerBI it gives me an error. I need to save the cvs column 'time' in time format while exporting .db
import csv, sqlite3
con = sqlite3.connect("Webasto.db") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE m (id,date,time,'action.name','findings.position_x','findings.position_y','findings.defect.name','part.name','station.name','station.area.name','station.area.identifier','station.area.production.name');") # use your column names here
with open('MASTER EXCEL.csv','r') as fin: # `with` statement available in 2.5+
# csv.DictReader uses first line in file for column headings by default
dr = csv.DictReader(fin) # comma is default delimiter
to_db = [(i['id'], i['date'],datetime['time'],i['action.name'],i['findings.position_x'],i['findings.position_y'],i['findings.defect.name'],i['station.area.identifier'],i['station.name'],i['station.area.name'],i['station.area.identifier'],i['station.area.production.name']) for i in dr]
cur.executemany("INSERT INTO m (id,date,time,'action.name','findings.position_x','findings.position_y','findings.defect.name','part.name','station.name','station.area.name','station.area.identifier','station.area.production.name') VALUES (?,?,?,?,?,?,?,?,?,?,?, ?);", to_db)
con.commit()
con.close()
