1

I am attempting to use Visual Studio Code(VSC) to import a csv file into SQL Server.

I can access SQL Server in VSC using the MSSQL extension. I am able to select, add columns, create tables ect... I can use python to load and manipulate the csv file.

However, I don't know how to connect the Python and the SQL scripts, or alternatively, how to use an sql script to query a csv file on my local computer.

One option is to just use Python, but I've had some trouble successfully setting up that connection.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Stykes
  • 306
  • 6
  • 15
  • If it were me, I would never be able to trust how the VSCode extension was doing the conversion. I would want to have the control of a Python script. Which connector are you trying to use? – Tim Roberts Apr 07 '21 at 19:47
  • @TimRoberts , good to know. Regardless of whether you would trust it. Is it possible? And I'm trying to use pyodbc as the connector, but I'm open to suggestions. It's not something I'm experienced at. – Stykes Apr 07 '21 at 20:30
  • 1
    This feels far too broad, if I am honest. The question is, effectly, "How do I import CSV data into SQL Server using Python?", which a [search](https://www.google.com/search?q=How+do+I+import+CSV+data+into+SQL+Server+using+Python%3F+site%3Astackoverflow.com) of gives a wealth of results. Does this answer your question? [Writing a csv file into SQL Server database using python](https://stackoverflow.com/questions/21257899/writing-a-csv-file-into-sql-server-database-using-python). What have you got so far? Why isn't it working? – Thom A Apr 19 '21 at 08:38
  • *"I've had some trouble successfully setting up that connection."* Did you have a read of the documentation on how to achieve this? [Python SQL Driver](https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-ver15) – Thom A Apr 19 '21 at 08:39
  • @Larnu you are probably right that this question is too broad. I did try to be as specific as I could. How I _feel_ is "Hey, I open up VS code: I have an SQL script that can read and write to an SQL Server. I have a python script that can read a csv file on my desktop. There should be a way to get the two work together right?" But I can't seem to get the SQL to read a csv and I'm much less skilled with python, so connecting it to the SQL server has proved challenging for me, though I did read the documentation. – Stykes Apr 21 '21 at 15:47

2 Answers2

1

I don't know about a VS Code specific tool but if you can run SQL scripts then you can look at OPENROWSET. Example F is probably a close match for what you are looking for but there are lots of options with this command to get exactly what you want.

INSERT INTO MyTable SELECT a.* FROM
OPENROWSET (BULK N'D:\data.csv', FORMATFILE =
    'D:\format_no_collation.txt', CODEPAGE = '65001') AS a;
SMM
  • 2,225
  • 1
  • 19
  • 30
0

first try to install mysql connector using this- pip install mysql-connector-python

after that use import mysql.connector to import the connector

Then connect your database using this-

myconn = mysql.connector.Connect(user='root',password="1234567890",database="world",auth_plugin='mysql_native_password')

where password is your mysql password and database is your name of database you want to connect

suppose you want to fetch all rows from your selected table-

myconn = mysql.connector.Connect(user='root',password="1234567890",database="world")
cur = myconn.cursor()
cur.execute("Select * from emptab")
allrows = cur.fetchall()
print(allows)

this would generate result like that-

(1001, 'RamKumar', 10000)
(1002, 'Ganesh Kumar', 1000)
(1003, 'Rohan', 3450)
(1004, 'Harish Kumar', 56000)
(1005, 'Mohit', 12000)
(1006, 'Harish Nagar', 56000)

to convert above data to CSV form first convert it into an pandas data frame-

df = pd.DataFrame(allrows)
df.columns = ['empno','name','salary']

this would convert data into pandas df and with columns above

at last use

df_final.to_csv(r'final.csv', index = False)

like command to save result to csv form

ABHIJAY PALIWAL
  • 11
  • 3