0

I have a database in MS Access. I am trying to export one column from one table to a CSV file, with Python using pypyodbc. From the CSV file obtained, there are no commas in numbers greater than 1. Any idea to solve?

Screen from MS Access: MS Access database

Screen from the obtained CSV: CSV

Code:

import pypyodbc
import csv
import os
from pathlib import Path
import re

data_folder1 = Path("/Users/sfulc/Desktop/FileProva/")
data_folder2 = Path("/Users/sfulc/Desktop/FileOutput/")

for filename in os.listdir("/Users/sfulc/Desktop/FileProva/"):
    file1 = r"Dbq=" + os.path.abspath(data_folder1 / filename) + r";"
    file2 = re.sub("mdb", "csv", os.path.abspath(data_folder2 / filename))
    pypyodbc.lowercase = False
    conn = pypyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + file1)
    cur = conn.cursor()
    cur.execute("SELECT LoadValue FROM OriginalData");
    with open(file2, 'w', newline='') as f:
        writer = csv.writer(f)
        for row in cur.fetchall():
            writer.writerow(row)
    cur.close()
    conn.close()
Gustav
  • 53,498
  • 7
  • 29
  • 55
LucaSful
  • 1
  • This normally is a `Locale` problem, it depend on the Language/Locale settings. What language are you using? – piertoni Apr 13 '22 at 14:37
  • It's the _csv.writer_ playing games with you, not _Access_. – Gustav Apr 13 '22 at 15:15
  • Print the row to see whats there. Maybe convert the item to string first. – Colin Curtain Apr 13 '22 at 17:05
  • @piertoni Italian – LucaSful Apr 14 '22 at 06:11
  • Good suggestions from @Gustav and @Colin-Curtain 1. to understand if is a csv issue try instead of `writerow` to print the content and check what is inside. 2. Play with csv, try something like https://stackoverflow.com/questions/18861420/python-csv-writer-control-precision – piertoni Apr 14 '22 at 06:54
  • @ColinCurtain If I print I obtain this: (0.1589555,) (0.4153565,) (0.7951628,) (1.184598,) (1.489722,) (1.732026,) ...... – LucaSful Apr 14 '22 at 09:36
  • If I try to convert the item to string the result is this: (,0,.,0,4,5,7,9,5,5,2,",",) (,0,.,1,5,8,9,5,5,5,",",) (,0,.,4,1,5,3,5,6,5,",",) (,0,.,7,9,5,1,6,2,8,",",) (,1,.,1,8,4,5,9,8,",",) – LucaSful Apr 14 '22 at 09:38
  • If I open the CSV file with a text editor it is displayed correctly, so the problem is with excel. Do you have any idea how I can fix it? – LucaSful Apr 14 '22 at 10:08
  • convert the item to string like this: i= 0.1234 then next line, s = str(i) this gives '0.1234' – Colin Curtain Apr 14 '22 at 23:29
  • Importing csv into excel. try the instructions here to import the numbers as text: https://planergy.com/blog/open-csv-in-excel/ – Colin Curtain Apr 14 '22 at 23:32

0 Answers0