sql server - Python 3.8 pandas *.csv import into MSSQL

Question

It should not that be difficult to import some data from a .csv file and I have done it already. But then I got a file with some data and it stores 50k of lines and in a really confusing way. I'm unable to import that one and I would like to reach out for help.

Problem one: everything is stored in row.name but it should be stored in row.name, row.id, row.year and so on.

Problem two: I'm not allowed to modify the .csv file.

Here are the first line of the .csv file:

name,id,nametype,recclass,mass (g),fall,year,reclat,reclong,GeoLocation
"Aachen,1,Valid,L5,21,Fell,1880,50.775,6.08333,""(50.775000, 6.083330)"""
"Aarhus,2,Valid,H6,720,Fell,1951,56.18333,10.23333,""(56.183330, 10.233330)"""
"Abee,6,Valid,EH4,107000,Fell,1952,54.21667,-113,""(54.216670, -113.000000)"""
"Acapulco,10,Valid,Acapulcoite,1914,Fell,1976,16.88333,-99.9,""(16.883330, -99.900000)"""
"Achiras,370,Valid,L6,780,Fell,1902,-33.16667,-64.95,""(-33.166670, -64.950000)"""
"Adhi Kot,379,Valid,EH4,4239,Fell,1919,32.1,71.8,""(32.100000, 71.800000)"""

Here is my Python script to import above .csv file

import pandas as pd
import pyodbc
import DatabaseConnectionDetails as dcd # seperate file to import DB settings

#Get the file loaded in the memory
data = pd.read_csv (r'C:pathToTHeFileLocationMeteoriteDataShort.csv', delimiter=',')
col_names =['name', 'id', 'nametype', 'recclass', 'mass (g)', 'fall', 'year', 'reclat', 'reclong', 'GeoLocation']
df = pd.DataFrame(data, columns=col_names)

#establish connection to MsSqldb
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=' + dcd.DB_SERVER + ';'
                      'Database=' + dcd.DB_DB + ';'
                      'Trusted_Connection=' + dcd.TRUSTED_CONNECTION + ';')
cursor = conn.cursor()

#Insert into Table
for row in df.itertuples():
    cursor.execute('INSERT INTO Meteorite.dbo.MeteoriteDataPoints (MeteoriteName, MeteoriteId, NameType, RecClass, MassInG, FallStatus, FellYear, RecLatitude, RecLongitude, Geolocation) VALUES (?,?,?,?,?,?,?,?,?,?)',
                row.name
                ,row.id
                ,row.nametype
                ,row.recclass
                ,row._5
                ,row.fall
                ,row.year
                ,row.reclat
                ,row.reclong
                ,row.GeoLocation
                )
conn.commit()
conn.close()

It would be really nice if someone can teach me or show me my mistakes.

Answer 1

等待大神答复