0

I have a bunch of DAT files that I need to convert to XLS files using Python. Should I use the CSV library to do this or is there a better way?

ralphtheninja
  • 107,622
  • 20
  • 101
  • 118
Everett
  • 105
  • 1
  • 10
  • Are all of the DAT files similar in format and if so can you show us a sample? – Bill Bell Oct 12 '17 at 17:47
  • I'm not exactly sure, but I do think they are all similar. If I can get at least 80% of the files converted with a simple script I'd be happy. – Everett Oct 12 '17 at 18:01

2 Answers2

1

I'd use pandas. 

import pandas as pd
df = pd.read_table('DATA.DAT')
df.to_excel('DATA.xlsx')

and of course you can setup a loop to get through all you files. Something along these lines maybe

import glob
import os
os.chdir("C:\\FILEPATH\\")
for file in glob.glob("*.DAT"):
     #What file is being converted
     print file 
     df = pd.read_table(file)
     file1 = file.replace('DAT','xlsx')
     df.to_excel(file1)
Tim Gottgetreu
  • 451
  • 1
  • 7
  • 19
  • I keep getting an error saying I'm missing the openpyxl module. I tried importing it, but still get the same error. – Everett Oct 12 '17 at 18:24
  • What version of pandas are you running? I think .to_excel is in v20? https://stackoverflow.com/questions/37954195/upgrade-version-of-pandas if you're running a version under 20. – Tim Gottgetreu Oct 12 '17 at 18:27
  • not sure how to check version, I tried `pandas -version` but no luck. I ran `pip3 install --upgrade pandas` and it updated numpy to version 1.13.3; however, I still get the same 'no module found' error – Everett Oct 12 '17 at 18:44
  • 1
    ah, silly me, I didn't have it installed. It works now! Much appreciated Tim! – Everett Oct 12 '17 at 18:49
  • Wonderful! Glade to hear it :) And if you like it, can you accept the answer? Thanks! – Tim Gottgetreu Oct 12 '17 at 18:49
  • I'm trying to use the loop function, but I'm having issues. It keeps giving me the "No engine for filetype" error. I believe it has something to do with the `df = pd.read_table('file')`. Do I replace `file` with the name of the first DAT file in my directory? I've been trying this, but it gives me an error. I read something about having to use a temporary file, should I try using that? – Everett Oct 12 '17 at 20:39
  • Sorry, my fault the `df=pd.read_table('file')` should be `df=pd.read_table(file)` since file should already be a string. I've up-dated the answer. – Tim Gottgetreu Oct 12 '17 at 20:53
0
writer = pd.ExcelWriter('pandas_example.dat',
                        engine='xlsxwriter',
                        options={'strings_to_urls': False})

or you can use :

pd.to_excel('example.xlsx')
Neha
  • 2,291
  • 2
  • 9
  • 25