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?

rtn
  • 127,556
  • 20
  • 111
  • 121
Everett
  • 145
  • 2
  • 12
  • 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
  • 483
  • 1
  • 8
  • 21
  • 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
  • 3,456
  • 3
  • 14
  • 26