Txt to excel python

txt2xls 0.2.2

Convert raw text data files into a single excel file.

Навигация

Ссылки проекта

Статистика

Метаданные

Лицензия: MIT

Метки raw, text, data, convert, excel, xls

Сопровождающие

Описание проекта

txt2xls convert raw text data files into a single excel file. It use maidenhair for reading raw text files so any kind of raw text file can be used if there is a maidenhair plugins.

Installation

Quick Usage

Assume there are several raw text data files like:

# Sample1.txt 0 10 1 20 2 30 3 40 4 50 5 60 # Sample2.txt 0 15 1 25 2 35 3 45 4 55 5 65 # Sample3.txt 0 12 1 22 2 32 3 42 4 52 5 62

It will produce output.xls file. The excel file have Sample1 , Sample2 , and Sample3 sheets.

Usage

usage: txt2xls [-h] [-v] [-p PARSER] [-l LOADER] [-u USING] [--unite] [--unite-basecolumn UNITE_BASECOLUMN] [--unite-function UNITE_FUNCTION] [--classify] [--classify-function CLASSIFY_FUNCTION] [--relative] [--relative-origin RELATIVE_ORIGIN] [--relative-basecolumn RELATIVE_BASECOLUMN] [--baseline] [--baseline-basecolumn BASELINE_BASECOLUMN] [--baseline-function BASELINE_FUNCTION] [--peakset-method ] [--peakset-basecolumn PEAKSET_BASECOLUMN] [--peakset-where-function PEAKSET_WHERE_FUNCTION] [--raise-exception] [-o OUTFILE] infiles [infiles . ] positional arguments: infiles Path list of data files or directories which have data files. optional arguments: -h, --help show this help message and exit -v, --version show program's version number and exit --raise-exception If it is specified, raise exceptions. -o OUTFILE, --outfile OUTFILE An output filename without extensions. The required filename extension will be automatically determined from an output format. Reading options: -p PARSER, --parser PARSER A maidenhair parser name which will be used to parse the raw text data. -l LOADER, --loader LOADER A maidenhair loader name which will be used to load the raw text data. -u USING, --using USING A colon (:) separated column indexes. It is used for limiting the reading columns. Unite options: --unite Join the columns of classified dataset with respecting --unite-basecolumn.The dataset is classified with --unite-function. --unite-basecolumn UNITE_BASECOLUMN An index of columns which will be used as a base column for regulating data point region. --unite-function UNITE_FUNCTION A python script file path or a content of python lambda expression which will be used for classifing dataset. If it is not spcified, a filename character before period (.) will be used to classify. Classify options: --classify Classify dataset with --classify-function. It will influence the results of --relative and --baseline. --classify-function CLASSIFY_FUNCTION A python script file path or a content of python lambda expression which will be used for classifing dataset. If it is not specified, a filename character before the last underscore (_) will be used to classify. Relative options: --relative If it is True, the raw data will be converted to relative data from the specified origin, based on the specified column. See `--relative-origin` and `--relative-basecolumn` also. --relative-origin RELATIVE_ORIGIN A dataset number which will be used as an orign of the relative data. It is used with `--relative` option. --relative-basecolumn RELATIVE_BASECOLUMN A column number which will be used as a base column to make the data relative. It is used with `--relative` option. Baseline options: --baseline If it is specified, the specified data file is used as a baseline of the dataset. See `--baseline-basecolumn` and `--baseline-function` also. --baseline-basecolumn BASELINE_BASECOLUMN A column index which will be proceeded for baseline regulation. It is used with `--baseline` option. --baseline-function BASELINE_FUNCTION A python script file path or a content of python lambda expression which will be used to determine the baseline value from the data. `columns` and `column` variables are available in the lambda expression. Peakset options: --peakset-method A method to find peak data point. --peakset-basecolumn PEAKSET_BASECOLUMN A column index which will be used for finding peak data point. --peakset-where-function PEAKSET_WHERE_FUNCTION A python script file path or a content of python lambda expression which will be used to limit the range of data points for finding. peak data point. `data` is available in the lambda expression.

Preference

You can create configure file as ~/.config/txt2xls/txt2xls.cfg (Linux), ~/.txt2xls.cfg (Mac), or %APPDATA%\txt2xls\txt2xls.cfg (Windows).

The default preference is equal to the configure file as below:

[default] raise_exception = False [reader] parser = 'parsers.PlainParser' loader = 'loaders.PlainLoader' using = None [[classify]] enabled = False function = 'builtin:classify_function' [[unite]] enabled = False function = 'builtin:unite_function' basecolumn = 0 [[relative]] enabled = False origin = 0 basecolumn = 1 [[baseline]] enabled = False function = 'builtin:baseline_function' basecolumn = 1 [writer] default_filename = 'output.xls' [[peakset]] method = 'argmax' basecolumn = -1 where_function = 'builtin:where_function'

I don’t use Microsoft Windows so the location of the configure file in Windows might be wrong. Let me know if there are any mistakes.

Источник

How to Convert Data from Txt Files to Excel Files Using Python

How to convert data from txt files to Excel files using python

The pandas library is wonderful for reading csv files (which is the file content in the image you linked). You can read in a csv or a txt file using the pandas library and output this to excel in 3 simple lines.

import pandas as pd

df = pd.read_csv('input.csv') # if your file is comma separated

or if your file is tab delimited ‘\t’ :

df = pd.read_csv('input.csv', sep='\t')

To save to excel file add the following:

df.to_excel('output.xlsx', 'Sheet1')

import pandas as pd
df = pd.read_csv('input.csv') # can replace with df = pd.read_table('input.txt') for '\t'
df.to_excel('output.xlsx', 'Sheet1')

This will explicitly keep the index, so if your input file was:

Your output excel would look like this:

You can see your data has been shifted one column and your index axis has been kept. If you do not want this index column (because you have not assigned your df an index so it has the arbitrary one provided by pandas):

df.to_excel('output.xlsx', 'Sheet1', index=False)

Your output will look like:

Here you can see the index has been dropped from the excel file.

how to convert text file to Excel file in python

The answer come from the stackoverflow link

import xlwt
import xlrd

book = xlwt.Workbook()
ws = book.add_sheet('First Sheet') # Add a sheet

f = open('testval.txt', 'r+')

data = f.readlines() # read all lines at once
for i in range(len(data)): 
row = data[i].split() # This will return a line of string data, you may need to convert to other formats depending on your use case
 
for j in range(len(row)): 
ws.write(i, j, row[j]) # Write to cell i, j

book.save('testval' + '.xls')
f.close()

In the case that you want all white space as column you can use openpyxl package to handle more than 256 columns :

from openpyxl import Workbook
wb = Workbook()
ws = wb.active


f = open('testval.txt', 'r+')

data = f.readlines()
spaces = ""
for i in range(len(data)): 
row = data[i].split(" ") 
ws.append(row)
wb.save("testval2.xlsx")

how to convert txt file to excel in python

Variable a is a string. You need to feed data into a pandas dataframe df and then df.to_excel(‘james.xlsx’) . See here for more details.

Python — How to convert txt to excel file using pandas

You have overthought the problem:

df = pd.read_csv(excel, sep=',')
df.to_excel('output.xlsx', index=False)

Convert text files to excel files using python

To automate that, you can use that python script described here:

Automate conversion txt to xls

Here is an updated version of the python script that will convert all the text files having the format that you described in a given directory to XLS files and save them in the same directory:

# mypath should be the complete path for the directory containing the input text files
mypath = raw_input("Please enter the directory path for the input files: ")

from os import listdir
from os.path import isfile, join
textfiles = [ join(mypath,f) for f in listdir(mypath) if isfile(join(mypath,f)) and '.txt' in f]

def is_number(s): 
try: 
float(s) 
return True 
except ValueError: 
return False 

import xlwt
import xlrd

style = xlwt.XFStyle()
style.num_format_str = '#,###0.00' 

for textfile in textfiles: 
f = open(textfile, 'r+') 
row_list = [] 
for row in f: 
row_list.append(row.split('|')) 
column_list = zip(*row_list) 
workbook = xlwt.Workbook() 
worksheet = workbook.add_sheet('Sheet1') 
i = 0 
for column in column_list: 
for item in range(len(column)): 
value = column[item].strip() 
if is_number(value): 
worksheet.write(item, i, float(value), style=style) 
else: 
worksheet.write(item, i, value) 
i+=1 
workbook.save(textfile.replace('.txt', '.xls'))

The script above will get a list of all the text files in the given directory specified in mypath variable and then convert each text file to an XLS file named generated_xls0.xls then the next file will be named generated_xls1.xls etc.

strip the string before writing it to the XLS file

modified the script to handle the formatting of numbers

how to convert text file to Excel file , Without deleting the spaces between data

If you have fixed-length fields, you need to split each line using index intervals.

book = xlwt.Workbook()
ws = book.add_sheet('First Sheet') # Add a sheet

with io.open("testval.txt", mode="r", encoding="utf-8") as f: 
for row_idx, row in enumerate(f): 
row = row.rstrip() 
ws.write(row_idx, 0, row[0:8]) 
ws.write(row_idx, 1, row[9:19]) 
ws.write(row_idx, 2, row[20:21]) 
ws.write(row_idx, 3, row[22:24]) 
# and so on. 

book.save("sample.xlsx")

You get something like that:

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

keniel123/text-to-excel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

This script takes as arguments a text file as input and the file name of an excel file as output. The script uses a comma as a delimiter and passes throught the textfile adding information into the excel sheet .

Example Command to run from the terminal

python excelscript.py -I data.txt -O balances.xlsx

Libraries needed to run script

You can download these libraries by using the pip package manager

pip install xlxlsxwriter , optparse

Источник