[Tutor] Excel to SQL DB using Python

[Joel Goldstick]

On Fri, Feb 25, 2022 at 8:16 AM Hannah Jones
<hannah.jones at ebcbrakesuk.com> wrote:
>
> Hello,
>
> I need help with some code if possible please.
>
> I am trying to convert an excel file to a list and then iterate over the list and add to an SQL database.
>
> I am having trouble adding the data to the database.
>
> I have already established the connection - all good.
>
> My code is:
>
> Locations = []
> filename = input("Enter Filename Containing your Locations:  ")
>
> with open(filename, "r") as csv_file:
>     csv_reader = csv.reader(csv_file, delimiter=',')
>     #skips the header from the excel file
>     next(csv_reader)
>     for lines in csv_reader:
>         Locations.append(lines)
> # SQL Statements
> sqlDELETE = "DELETE FROM dbo.GeocodingLocations"
> cursor.execute(sqlDELETE)
> sqlINSERTBase = "INSERT INTO dbo.GeocodingLocations (SageCode, Name, AddressLine1, AddressLine2, AddressLine3, AddressLine4, City, CountryName) VALUES "
> sqlVALUES = ""
>
> # Iteration to add to Studio DB
> for x in Locations:
>     SageCode = x[0] ; Name = x[1] ; AddressLine1 = x[2] ; AddressLine2 = x[3] ; AddressLine3 = x[4] ; AddressLine4 = x[5] ; City = x[6] ; CountryName = x[7]
>     sqlVALUES = sqlVALUES + "( '" + SageCode + "', '" + Name + "', '" + AddressLine1 + "', '" + AddressLine2 + "', '" + AddressLine3 + "', '" + AddressLine4 + "', '" + City + "', '" + CountryName +  "' ), '"
>     sqlINSERT = sqlINSERTBase + sqlVALUES
>     sqlINSERT = sqlINSERT
>     cursor.execute(sqlINSERT)
>
>
> cursor.commit()
> con.close()
>
>
> however I keep receiving this error; pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'S'. (102) (SQLExecDirectW)")
>
> I know my error is in this line;
>
>
>
> sqlVALUES = sqlVALUES + "( '" + SageCode + "', '" + Name + "', '" + AddressLine1 + "', '" + AddressLine2 + "', '" + AddressLine3 + "', '" + AddressLine4 + "', '" + City + "', '" + CountryName +  "' ), '"
>
>
> but I am not sure where exactly, any help is highly appreciated.
 you could print sqlVALUES.  Then take the statement produced and
interactively see what happens with your sqlserver.

> https://mail.python.org/mailman/listinfo/tutor



-- 
Joel Goldstick