Import data from multiple big .txt (around 1.5 Mb) into multiple .xlsx

[TheShredder]

Hi guys,
I'm currently a newbie to VBA macro scripting. While working I need to find a way to import data from a text file to an excel worksheet, and a way to automatize it for a list of .txt into a list of .xlsx . Found a sweet code here http://www.cpearson.com/excel/ImportBigFiles.aspx but it cannot detect that into the .txt data are delimited: so I'm not able to separate them with the default setting for import.
<code>
Delimited
Delimiters : TAB
Column Data format : General
Where do you want to put the data ? Existing worksheet: =$A$1
</code>

Here's the example of what I get manually and what with the code above.

Manually.
https://imgur.com/a/4C5AD

With the script.
https://imgur.com/a/Tefzs

So, in order to resume, I'm looking mainly to a way to get the text properly formatted and without random characters added (+ or _ ).
After that, I'll try something to automatize the process to a list of .txt into a proper folder.

Every tip or suggestion are well accepted.
Thanks for the attention.

 

[ranman256]

so what is your file? Delimited, not delimited, fixed?

 

[TheShredder]

so what is your file? Delimited, not delimited, fixed?

My files are delimited by tab. I've solved using python and library xlwt and xlrd. I know it doesn't support .xlsx (infact new files are generated with .xls extension, but for me doesn't matter at all). Here's the code.

<code>
!# /usr/bin/env/python

mypath = raw_input ("Enter the directory ..")

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('\t'))
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', '.xlsx'))

</code>

 

[ranman256]

Have you tried import via text:
menu: Data tab, From Text, select the file, then set TAB DELIMITED

 

[TheShredder]

Have you tried import via text:
menu: Data tab, From Text, select the file, then set TAB DELIMITED

I'm talking about multiple big .txt files, more than 100.
Manually it's nearly impossible to do.
The code above does the work well.

 

[ranman256]

100 records is nothing.