Importing Data into SQL Server with Visual Studio Code

Question

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.

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
– Tim Roberts, Commented Apr 7, 2021 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
– Stykes, Commented Apr 7, 2021 at 20:30
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 of gives a wealth of results. Does this answer your question? Writing a csv file into SQL Server database using python. What have you got so far? Why isn't it working? — Thom A
– Thom A ♦, Commented Apr 19, 2021 at 8: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 — Thom A
– Thom A ♦, Commented Apr 19, 2021 at 8: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
– Stykes, Commented Apr 21, 2021 at 15:47

SMM · Accepted Answer · 2021-04-19 12:49:49Z

1

+50

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;

answered Apr 19, 2021 at 12:49

SMM

2,2351 gold badge21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stykes Over a year ago

Thanks! While not a direct answer to the question, this did help me learn more.

ABHIJAY PALIWAL · Accepted Answer · 2021-04-25 15:32:42Z

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

edited Apr 25, 2021 at 15:32

answered Apr 25, 2021 at 15:17

ABHIJAY PALIWAL

214 bronze badges

1 Comment

Stykes Over a year ago

I am trying this, but I don't think this will work because SQL Server is not MySQL.

Collectives™ on Stack Overflow

Importing Data into SQL Server with Visual Studio Code

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related