Exporting SQL query from Python to txt file

Question

I'm trying to export data that I queried from a database to a txt file. I am able to do so with the .to_csv method however it exports with spaces. I've tried to set the (sep) in the query to no space but it is forcing me to use at least one space or item as a seperator. Is there any way to export data to a txt file and not have any spaces in between export?

dataframe

Code I've been using to export to .txt

dataframe.to_csv('Sales_Drivers_ITCSignup.txt',index=False,header=True)

Want it to export like this:

Any reason to not want a delimiter? Any of those fields being one character off (eg even 1 shorter or longer) is going to make it a tricky thing to read back again... or do you genuinely desire a fixed-width file? — Jon Clements, Mar 31 '21 at 18:58
We send this list to the IT team to Load up for transformation. Not sure specifically as to why either but this is the format they requested we send over. — adura826, Mar 31 '21 at 19:00

score 1 · Answer 1 · answered Mar 31 '21 at 18:43

1

Try

np.savetext(filename, df.values, fmt)

Feel free to ask question in case of any problem.

answered Mar 31 '21 at 18:43

Usama Tariq

169
5

I tried using this 'np.savetxt("Sales_Drivers_ITCSignup_2.txt", dataframe.values, fmt='')' but got the error that fmt has wrong number of % formats. How do I get around this? – adura826 Mar 31 '21 at 18:48
Format 'fmt' is where you will define what datatype your value hold just like '%d' or '%s' you can define separate fmt for separate columns or you can define universal for all. you should '%s' for yours. – Usama Tariq Apr 01 '21 at 17:43

score 0 · Accepted Answer · answered Apr 05 '21 at 18:49

Took a bit of tinkering but this was the code I was able to come up with. The thought process was to create import the text file, edit it as a list, then re-export it overwriting the previous list. Thanks for all the suggestions!

RemoveCommas = []
RemoveSpaces = []
AddSymbol = []
Removeextra = []

#Import List and execute replacements
SalesDriversTransactions = []
with open('Sales_Drivers_Transactions.txt', 'r')as reader:
    for line in reader.readlines():
        SalesDriversTransactions.append(line)

for comma in SalesDriversTransactions:
    WhatWeNeed = comma.replace(",","")
    RemoveCommas.append(WhatWeNeed)
    
for comma in RemoveCommas:
    WhatWeNeed = comma.replace(" ","")
    RemoveSpaces.append(WhatWeNeed)
    
for comma in RemoveSpaces:
    WhatWeNeed = comma.replace("Ã¾","þ")
    AddSymbol.append(WhatWeNeed)

for comma in AddSymbol:
    WhatWeNeed = comma.replace("\n","")
    Removeextra.append(WhatWeNeed)

with open('Sales_Drivers_Transactions.txt', 'w')as f:
    for i in Removeextra:
        f.write(i)
        f.write('\n')

Exporting SQL query from Python to txt file

2 Answers2