0

I have to copy data from a .dat file and paste it into excelfile format. Currently, i am reading line by line dat file and appending it to existing xlsx file.(I can write into new xlsx file but then i have to append headers and formatting of that file).

book = openpyxl.load_workbook(SIQFile)
sheet = book.active
with open(AFile,"r") as F1:
    #line=F1.readlines()
    line1=F1.readline()
    cnt = 1
    while line1:
        #print("Line {}: {}".format(cnt, line1.strip()))
        list1=line1.split("\t")
        if cnt !=1:
            sheet.append(list1)
        line1 = F1.readline()
        cnt += 1
        print cnt
book.save(rootFolder+'.xlsx')

But it is taking to much time as it is reading line by line and then writing it to xlsx file. I can read all lines at a time using 

line=F1.readlines()

But i can i write all lines at a time in xlsx file. In .dat file data seperated by tab

rtn
  • 127,556
  • 20
  • 111
  • 121
Sudhir Tiwari
  • 65
  • 1
  • 14
  • 1
    For [python2.7](https://docs.python.org/2/library/csv.html), or [python 3.6](https://docs.python.org/3/library/csv.html), you have some csv native libraries that might be faster than anything. – IMCoins Dec 20 '17 at 11:37
  • I would investigate copying the string from `readlines()` to the clipboard and pasting it into Excel. – Andy G Dec 20 '17 at 11:40
  • 1
    Have you tried using Pandas? [Here](https://stackoverflow.com/questions/27413843/difficulty-importing-dat-file) is an SO answer on reading a DAT file with Pandas. And [here](https://stackoverflow.com/questions/29459461/pandas-dataframe-to-excel-sheet) is an SO answer on writing a Pandas DataFrame to Excel. – gaw89 Dec 20 '17 at 11:49
  • @AndyG How can i paste all lines when i use sheet.append(line) it will add all data in one row. – Sudhir Tiwari Dec 20 '17 at 11:57
  • @gaw89 loading data in pandas also take time and i am not sure will pandas able to write in excel or not. – Sudhir Tiwari Dec 20 '17 at 11:58
  • @IMCoins can you suggest any librabry apart from openpyxl, xlwtwriter – Sudhir Tiwari Dec 20 '17 at 11:59
  • I would attempt the paste into a single cell; the normal behaviour of Excel is then to layout all the data in the following columns and rows. There are PasteSpecial options to explore as well. – Andy G Dec 20 '17 at 12:03
  • @SudhirTiwari please click the links I put into `python2.7` and `python3.6`. – IMCoins Dec 20 '17 at 12:09
  • xlsxwriter in python has always worked out fine for me. – waqasgard Dec 20 '17 at 12:09
  • @SudhirTiwari, looking at your code above, you may actually even be able to `pd.read_csv('file.dat', sep='\t')` with Pandas. And Pandas can definitely write to Excel, I use it all the time. – gaw89 Dec 20 '17 at 12:10
  • @AndyG that's exactly i do manually but how can i do using python? – Sudhir Tiwari Dec 20 '17 at 13:02
  • 2
    Your limiting factor is writing to an existing file. openpyxl is the only library suitable for this. Make sure you have lxml installed. All suggestions to use Pandas well-intended but misguided. – Charlie Clark Dec 20 '17 at 15:41

0 Answers0