Python Forum
MDB to CSV Specific columns
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MDB to CSV Specific columns
#1
Hello everyone I'm trying to extrapolate specific columns from a table, to export them to a csv file but when I enter them in the csv file they all come out on the same column instead I would like to divide them how can I do?

import pyodbc

import csv

import pandas as pd


con = pyodbc.connect("DSN=ROTTURADIPALLE")



sql= " SELECT codbar, imposto FROM tabmag"

PATH ="C:/Users/nio74/Desktop/ProjectPython/test\dati.csv"


dati=pd.read_sql(sql,con)

dati.to_csv((PATH))
[Image: csv.png]
Reply
#2
I gave you a shell script that will export entire database into CSV files in one fell swoop,
https://python-forum.io/Thread-Connect-o...atbase-mdb

If this is a one off, that would save a ton of time.

https://python-forum.io/Thread-Connect-o...atbase-mdb
Reply
#3
I want to export only the columns I need and put them in a column on the csv file in order to speed things up
Reply
#4
I apologize to everyone I have never worked with CSVs but I thought that opening them with excell the data should be lined up on every single column, instead it is normal that they are all placed on a column and separated by a comma.
Reply
#5
(Dec-21-2020, 05:36 AM)nio74maz Wrote: I apologize to everyone I have never worked with CSVs but I thought that opening them with excell the data should be lined up on every single column, instead it is normal that they are all placed on a column and separated by a comma.

Two possibilities - check the generated csv file. Are there quotes around each row?
second one - it depends also how you open csv file in Excel. If just opening the csv file does not work - import it from Data->From text file. As far as I remember the automatic behavior depends on list separator value specified in Regional settings. I guess your list separator setting is semi-colon.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
CSV files are (normally) comma delimited text files.
they can be read simply
the following script can read csv as dictionary, or plain data:
import csv
import os


def read_csv(filename, has_header=True, dict_out=True):
    header = []
    with open(filename) as fp:
        if dict_out:
            cdrdr = csv.DictReader(fp, delimiter=',')
        else:
            crdr = csv.reader(fp, delimiter=',')
        if dict_out:
            for n, row in enumerate(cdrdr):
                yield n, row
        else:
            for n, row in enumerate(crdr):
                yield n, row

def tryit():

    # Guarentee cwd is script directory
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    print(f"\noutput as As dict:\n")
    for n, row in read_csv('IllinoisZipcodeData.csv'):
        print(f"Rownum: {n}, end = ''")
        for key, value in row.items():
            print(f"    {key}: {value}")
        print()

    print(f"\nOutput as plain data:\n")
    for n, row in read_csv('IllinoisZipcodeData.csv', has_header=True, dict_out=False):
        print(f"Rownum: {n}, data: {row}")


if __name__ == '__main__':
    tryit()
Example with attached file:
Output:
output as As dict: Rownum: 0, end = '' CountyName: McHenry CountySeat: Woodstock FipsCd: 111 LocalCountyId: 63 ZipCode: 60001 Rownum: 1, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60002 Rownum: 2, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60004 Rownum: 3, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60005 Rownum: 4, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60006 Rownum: 5, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60007 Rownum: 6, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60008 Rownum: 7, end = '' CountyName: Cook CountySeat: Chicago FipsCd: 31 LocalCountyId: 16 ZipCode: 60009 Rownum: 8, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 9, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 10, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 11, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 12, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 13, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 14, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 15, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 16, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 17, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 18, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 19, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 20, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 21, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 22, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 23, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 24, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60010 Rownum: 25, end = '' CountyName: Lake CountySeat: Waukegan FipsCd: 97 LocalCountyId: 50 ZipCode: 60011 Output as plain data: Rownum: 0, data: ['CountyName', 'CountySeat', 'FipsCd', 'LocalCountyId', 'ZipCode'] Rownum: 1, data: ['McHenry', 'Woodstock', '111', '63', '60001'] Rownum: 2, data: ['Lake', 'Waukegan', '97', '50', '60002'] Rownum: 3, data: ['Cook', 'Chicago', '31', '16', '60004'] Rownum: 4, data: ['Cook', 'Chicago', '31', '16', '60005'] Rownum: 5, data: ['Cook', 'Chicago', '31', '16', '60006'] Rownum: 6, data: ['Cook', 'Chicago', '31', '16', '60007'] Rownum: 7, data: ['Cook', 'Chicago', '31', '16', '60008'] Rownum: 8, data: ['Cook', 'Chicago', '31', '16', '60009'] Rownum: 9, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 10, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 11, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 12, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 13, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 14, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 15, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 16, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 17, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 18, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 19, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 20, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 21, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 22, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 23, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 24, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 25, data: ['Lake', 'Waukegan', '97', '50', '60010'] Rownum: 26, data: ['Lake', 'Waukegan', '97', '50', '60011']

Attached Files

.csv   IllinoisZipcodeData.csv (Size: 725 bytes / Downloads: 135)
Reply
#7
thanks you are really good
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020