How to parse SQL files in pandas?
I am in an odd situation where I cannot connect to the server using python. I can however connect to the server in other ways using SQL Server Management, so from that end I can execute any query. The problem however is parsing in pandas, data retrieved from SQL Manager. As far as I am aware, data from SQL Manager can be retrieved as csv, txt or rpt. Parsing any of these formats is a pain in the neck and it's not always the same for all tables. My question is then, what is the fastest way to parse any of the file formats that SQL Manager can output in pandas? Is there a standard format that SQL Manager can output and which is parsed the same way in pandas for all tables? Has anyone faced this problem, or is there another workaround?
sql-server python-3.x pandas parsing rpt asked Nov 12 '18 at 21:59
callmeGuy
13519
add a comment |
I am in an odd situation where I cannot connect to the server using python. I can however connect to the server in other ways using SQL Server Management, so from that end I can execute any query. The problem however is parsing in pandas, data retrieved from SQL Manager. As far as I am aware, data from SQL Manager can be retrieved as csv, txt or rpt. Parsing any of these formats is a pain in the neck and it's not always the same for all tables. My question is then, what is the fastest way to parse any of the file formats that SQL Manager can output in pandas? Is there a standard format that SQL Manager can output and which is parsed the same way in pandas for all tables? Has anyone faced this problem, or is there another workaround?
sql-server python-3.x pandas parsing rpt asked Nov 12 '18 at 21:59
callmeGuy
13519
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them withpandas.read_csv(), what happens? (And how is that different than executing a SQL query viapandas.read_sql()...)
– Evan
Nov 12 '18 at 22:22
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28
add a comment |
I am in an odd situation where I cannot connect to the server using python. I can however connect to the server in other ways using SQL Server Management, so from that end I can execute any query. The problem however is parsing in pandas, data retrieved from SQL Manager. As far as I am aware, data from SQL Manager can be retrieved as csv, txt or rpt. Parsing any of these formats is a pain in the neck and it's not always the same for all tables. My question is then, what is the fastest way to parse any of the file formats that SQL Manager can output in pandas? Is there a standard format that SQL Manager can output and which is parsed the same way in pandas for all tables? Has anyone faced this problem, or is there another workaround?
sql-server python-3.x pandas parsing rpt asked Nov 12 '18 at 21:59
callmeGuy
13519
I am in an odd situation where I cannot connect to the server using python. I can however connect to the server in other ways using SQL Server Management, so from that end I can execute any query. The problem however is parsing in pandas, data retrieved from SQL Manager. As far as I am aware, data from SQL Manager can be retrieved as csv, txt or rpt. Parsing any of these formats is a pain in the neck and it's not always the same for all tables. My question is then, what is the fastest way to parse any of the file formats that SQL Manager can output in pandas? Is there a standard format that SQL Manager can output and which is parsed the same way in pandas for all tables? Has anyone faced this problem, or is there another workaround?
sql-server python-3.x pandas parsing rpt sql-server python-3.x pandas parsing rpt asked Nov 12 '18 at 21:59
callmeGuy
13519
asked Nov 12 '18 at 21:59
callmeGuy
13519
asked Nov 12 '18 at 21:59
callmeGuy
13519
asked Nov 12 '18 at 21:59
callmeGuy
13519
asked Nov 12 '18 at 21:59
callmeGuy
13519
13519
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them withpandas.read_csv(), what happens? (And how is that different than executing a SQL query viapandas.read_sql()...)
– Evan
Nov 12 '18 at 22:22
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28
add a comment |
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them withpandas.read_csv(), what happens? (And how is that different than executing a SQL query viapandas.read_sql()...)
– Evan
Nov 12 '18 at 22:22
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them with
pandas.read_csv(), what happens? (And how is that different than executing a SQL query via pandas.read_sql()...)– Evan
Nov 12 '18 at 22:22
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them with
pandas.read_csv(), what happens? (And how is that different than executing a SQL query via pandas.read_sql()...)– Evan
Nov 12 '18 at 22:22
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53270714%2fhow-to-parse-sql-files-in-pandas%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53270714%2fhow-to-parse-sql-files-in-pandas%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'd love to know why SSMS can connect, but Python can't... anyway, if you export CSV files and try to read them with
pandas.read_csv(), what happens? (And how is that different than executing a SQL query viapandas.read_sql()...)– Evan
Nov 12 '18 at 22:22
@Evan the reason python can't connect is because it's in a different environment. pandas.read_csv() definitely works and that's the way I've been doing it, however it's pretty annoying because depending on what data I am returning, I always have to parse it differently. pandas.read_sql(), I don't think I can use it as it takes a connection
– callmeGuy
Nov 12 '18 at 22:44
Doesn't seem like anyone else is biting; I guess that using CSVs is the best approach, if you can't get pandas/SQLAlchemy to connect.
– Evan
Nov 13 '18 at 1:28