def convert_excel_to_txt(filename):
    fileout = home + (filename.split("/")[-1]).split('.')[0]+".txt"
    print fileout
    #print "Reading file ",filename

    records = pe.get_array(file_name=filename)
    f = open(fileout,'w')
    #print "Starting to process data. Hold your breath"

    for count,rec in enumerate(records[1:]):
        rec[0] = "DATALIFE"
        rec[1] = "RPAY"
        rec[5] = "04182010000104"
        rec[4] = time.strftime("%d/%m/%Y")
        line = ""
        for value in rec:
            if value and type(value) is unicode:	
                value = unicodedata.normalize('NFKD', value).encode('ascii','ignore')
            if rec[6] % 2 == 0:
                rec[6] = int(rec[6])
	        # Cross check payment types with mahesh
            if rec[2] == "NEFT" or rec[2] == "IFT":
                line = line + str(value)+"~"
            else:
                showerror("Error","Your Payment Type is Wrong in column %d. Please correct it and run the script again."%(count+2))
			#print "Exiting Script"
                delete_content(f)
                f.close()
                root.quit()
                #sys.exit()
        f.write(line[:-1])
        f.write("\n")
    f.close()
    showinfo("Final Status","File converted. Please see this path %s"%(fileout))
    root.quit()

 def test_get_array_from_sql(self):
     array = pe.get_array(session=Session(), table=Signature)
     assert array == [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]

def edit():
    # shutil.copyfile(app.config['EXTRA_STUDENTS_SOURCE_PATH'], app.config['EXTRA_STUDENTS_WORKING_PATH'])
    records = get_array(file_name=app.config['EXTRA_STUDENTS_WORKING_PATH'])
    if request.method == 'POST':
        changed = False
        removes = []
        for key in request.form.keys():
            m = re.match('remove_(\d+)', key)
            if m:
                removes.append(int(m.group(1)))
                changed = True

        records = [r for r in records if not (r[0] in removes)]
        if request.form['student_number'] and request.form['first_name'] and \
            request.form['last_name'] and request.form['email']:
            records.append([
                request.form['student_number'],
                request.form['first_name'],
                request.form['last_name'],
                '104',
                request.form['gender'],
                request.form['email'],
                '9919.1'
            ])
            changed = True
        if changed:
            save_data(app.config['EXTRA_STUDENTS_WORKING_PATH'], records, lineterminator='\n')

    return render_template('students.html', page_title='Edit Students', records=records)

 def test_get_array_from_file(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     result = pe.get_array(file_name=testfile)
     assert result == data
     os.unlink(testfile)

 def test_get_array_from_array(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     result = pe.get_array(array=data)
     assert result == data

 def test_save_as_and_append_colnames(self):
     data = [[1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     testfile2 = "testfile.xls"
     sheet.save_as(testfile)
     pe.save_as(file_name=testfile, out_file=testfile2, colnames=["X", "Y", "Z"])
     array = pe.get_array(file_name=testfile2)
     assert array == [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]

 def test_download(self):
     response = self.app.get('/download')
     ret = pe.get_array(file_type="csv", file_content=response.data)
     assert ret == [
         ["REVIEW_DATE","AUTHOR","ISBN","DISCOUNTED_PRICE"],
         ["1985/01/21","Douglas Adams",'0345391802','5.95'],
         ["1990/01/12","Douglas Hofstadter",'0465026567','9.95'],
         ["1998/07/15","Timothy \"The Parser\" Campbell",'0968411304','18.99'],
         ["1999/12/03","Richard Friedman",'0060630353','5.95'],
         ["2004/10/04","Randel Helms",'0879755725','4.5']
     ]

 def test_get_sheet_from_recrods(self):
     records = [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
     result = pe.get_array(records=records)
     expected = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     assert expected == result

def read_events(fpath, response_type):
    """Read data from the file an Excel work book.

    Parameters
    ----------
    fpath : str or `pathlib.Path`
        Filename of the input file.
    response_type : str
        Type of response. Valid options are: 'psa' for psuedo-spectral
        acceleration, or 'fa' for Fourier amplitude.

    Returns
    -------
    ext : str
        Extension of input file
    reference : :class:`numpy.ndarray`
        Reference of the response. This is either period (sec) for
        response_type 'psa' or frequency (Hz) for response_type 'fa'
    events : List[dict]
        List of events read from the file. See ``Note`` in
        :func:`.calc_compatible_spectra` for more information on structure of
        the dictionaries.

    """
    assert response_type in ['psa', 'fa']
    fpath = pathlib.Path(fpath)

    data = pyexcel.get_array(file_name=str(fpath))
    ext = fpath.suffix

    parameters = {
        key: data[i][1:]
        for i, (key, label) in enumerate(PARAMETER_NAMES)
    }

    event_row = len(parameters) + 1
    event_count = len(data[0]) - 1

    reference = np.array([row[0] for row in data[event_row:]])

    events = []
    for i in range(event_count):
        resps = np.array([row[i + 1] for row in data[event_row:]])
        # Extract the appropriate attributes
        e = {k: v[i] for k, v in parameters.items()}
        e[response_type] = resps

        if 'region' in e:
            e['region'] = get_region(e['region'])

        events.append(e)

    return ext, reference, events

 def test_get_array_from_dict(self):
     adict = {
         "X": [1, 4],
         "Y": [2, 5],
         "Z": [3, 6]
     }
     result = pe.get_array(adict=adict)
     expected = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     assert expected == result

 def test_get_array_from_memory(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     content = pe.save_as(dest_file_type="xls", array=data)
     array = pe.get_array(file_content=content.getvalue(), file_type="xls")
     assert array == [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]

    def get_array(self, **keywords):
        """
        Get a list of lists from the file

        :param sheet_name: For an excel book, there could be multiple
                           sheets. If it is left unspecified, the
                           sheet at index 0 is loaded. For 'csv',
                           'tsv' file, *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A list of lists
        """
        params = self.get_params(**keywords)
        return pe.get_array(**params)

def validate_data_file(file):

    tmp_name_file = 'tmp' + get_extension(file)

    tmp_file = open('{}/{}'.format(settings.MEDIA_ROOT, tmp_name_file), 'wb')
    tmp_file.write(file.read())
    tmp_file.close()

    file_data = pyexcel.get_array(file_name="{}/{}".format(settings.MEDIA_ROOT, tmp_name_file))

    list_error = []

    list_error += _validate_prev_data_in_file(file_data[: settings.EXCEL_START_STRING])
    list_error += _validate_excel_data(file_data[settings.EXCEL_START_STRING:])

    if len(list_error):
        text = ['В файе присутствуют ошибки:'] + list_error
        raise ValidationError(text)

 def test_single_sheet_file(self):
     array = [["id", "name"], [1, "News"], [2, "Sports"]]
     for upload_file_type in ["ods", "xls"]:
         self.init()
         print("Uploading %s" % upload_file_type)
         file_name = "test.%s" % upload_file_type
         io = pe.save_as(array=array, dest_file_type=upload_file_type)
         if not PY2:
             if isinstance(io, BytesIO):
                 content = io.getvalue()
             else:
                 content = io.getvalue().encode("utf-8")
         else:
             content = io.getvalue()
         response = self.app.post("/upload/categories", upload_files=[("file", file_name, content)])
         ret = pe.get_array(file_type="xls", file_content=response.body)
         assert array == ret
         self.done()

 def test_single_sheet_file(self):
     array = [
         ["id", "name"],
         [1, "News"],
         [2, "Sports"]
     ]
     for upload_file_type in ['xls', 'ods']:
         with app.app_context():
             db.drop_all()
             db.create_all()
         print("Uploading %s" % upload_file_type)
         file_name = "test.%s" % upload_file_type
         io = pe.save_as(array=array, dest_file_type=upload_file_type)
         response = self.app.post('/upload/categories',
                                  buffered=True,
                                  data={"file": (io, file_name)},
                                  content_type="multipart/form-data")
         ret = pe.get_array(file_type="xls", file_content=response.data)
         assert array == ret

def upload(request):
    if request.method == "POST":
        form = UploadFileForm(request.POST, request.FILES)
        if form.is_valid():
            on_date = form.cleaned_data['load_date']
            file_handle = request.FILES['file']
            tmp_name_file = 'tmp' + get_extension(file_handle)
            path = "{}/{}".format(settings.MEDIA_ROOT, tmp_name_file)
            data = pyexcel.get_array(file_name=path)
            job = loader.delay(data[settings.EXCEL_START_STRING:], on_date)
            os.remove(path)
            response = render_to_response(
                'loader/index.html',
                {
                    'message': 'Данные будут загружены',
                    'message_type': 'success',
                    'form': form,
                },
                context_instance=RequestContext(request))
            response.set_cookie('kids_loader_job', job.get_id())
            response.set_cookie('kids_job_task', '2')
            return response
        else:
            return render_to_response(
                'loader/index.html',
                {
                    'message': 'Ошибка загрузки',
                    'message_type': 'danger',
                    'form': form,
                },
                context_instance=RequestContext(request))

    else:
        form = UploadFileForm()

    return render_to_response(
        'loader/index.html',
        {'form': form},
        context_instance=RequestContext(request))

def read_deepsoil_results(name):
    data = pyexcel.get_array(file_name=str(fpath_data / (name + '.xlsx')))
    names = ','.join(string.ascii_uppercase[:len(data[0])])
    records = np.rec.fromrecords(data, names=names)

    def extract_cols(records, cols, first, last, names):
        return {
            name: records[col][first:last].astype(float)
            for col, name in zip(cols, names)
        }

    d = dict()
    # Read the time series
    d['time_series'] = extract_cols(
        records, 'ABCDE', 1, 11800,
        ['time', 'accel', 'strain', 'stress', 'arias_int'])
    # Read the response spectrum
    d['resp_spec'] = extract_cols(records, 'GH', 1, 114, ['period', 'psa'])
    # Read the Fourier amplitude
    d['fourier_spec'] = extract_cols(records, 'JKL', 1, 16384,
                                     ['freq', 'ampl', 'ratio'])

    return d

import pyexcel as pe
import pyexcel.ext.xls
import unicodedata
import sys
import time

def delete_content(pfile):
	pfile.seek(0)
	pfile.truncate()

filename = sys.argv[1]
fileout = filename.split('.')[0]+".txt"

print "Reading file ",filename

records = pe.get_array(file_name=filename)
f = open(fileout,'w')
print "Starting to process data. Hold your breath"

for count,rec in enumerate(records[1:]):
	rec[0] = "DATALIFE"
	rec[1] = "RPAY"
	rec[5] = "04182010000104"
	rec[4] = time.strftime("%d/%m/%Y")
	line = ""
	for value in rec:
		if value and type(value) is unicode:
			value = unicodedata.normalize('NFKD', value).encode('ascii','ignore')
		if rec[6] % 2 == 0:
			rec[6] = int(rec[6])
		# Cross check payment types with mahesh

 def test_get_array(self):
     expected = pe.get_array(x="something")
     assert expected == None

 def test_get_array(self):
     pe.get_array(x="something")