From Text File to Excel

[IronGirl]

Hello folks,

I'm new to Autoit and I just wanna know if there is some script that helps me to populate my excel template from a text file (.txt).

I have a text file with lots of columns and lines and I would like to read these data from each column and save in Excel's columns.

Is there some script here that I can do this? If there is, please, indicate to me, if not, can someone help me with this script?

I appreciate your help,

Tks!

[jchd]

What is the format of your text file? If that's a .csv or .tsv then import in Excel is a standard feature.

There are plenty of examples for processing .CSV data and there is a pretty good Excel UDF.

[IronGirl]

Dear jchd,

My text file format is .txt... with tabular separation.

Is there a script for this type of file?

I have this kind of file, with tabular separation, and I have to past to an Excel Sheet.

 

0,    5162,    -2410,9    127,    23,52    4,9431    26,455    5,1758    23,664
5,2917    5162,    -2410,9    121,71    23,52    4,7372    26,321    4,7445    23,735
10,583    5162,    -2410,9    116,42    23,52    4,5312    26,188    4,3131    23,812
15,875    5162,    -2410,9    111,12    23,52    4,3252    26,055    3,8818    23,895
21,167    5162,    -2410,9    105,83    23,52    4,1193    25,924    3,4504    23,984
26,458    5162,    -2410,9    100,54    23,52    3,9133    25,793    3,0191    24,079

Tks!

[water]

You need something like this:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpenText($oExcel, "C:\Local\Tab.txt", 1, Default, Default, Default, @TAB)

 

[jchd]

Almost what @water said, but the example provided uses 4 whitespaces and not tabs (maybe due to some conversion during copy-paste) and more worrisomely, the formatting of numbers (a space as thousands separator, comma as decimal point, no decimal part everywhere) may require a few changes before supplying the data to Excel.

[water]

I have modified the provided data and changed 4 spaces to a tab character. No further modification was needed for a german system which uses comma as decimal point. Excel handles data without decimal part just fine in my environment.

[jchd]

Wow, and it does understand 12345, w/o a decimal part and 12 3456,78 with the thousands separator?
OK; Excel 15 - jchd 0

But I wonder how that would work on  a system where the locale is different. Let the OP try and tell us.

[water]

I didn't test with 12 3456,78 as the OPs data does not include a value with space as thousands separator.
Let's see if the OP is happy with the provided solution.

If the system has a different locale you simply provide the decimal and thousands separator to use as parameter to _Excel_BookOpenText.

[jchd]

Sorry I misread the data; never mind, too tired.

[water]

I just tested the thousands separator. Space lets Excel interpret the value as text. When I use a dot the value is correctly interpreted as number.

[IronGirl]

Dears,

Thanks for helping me, it works perfectly.

I didn't answer before because I had health issues in the last day. I'm good now.

Let me enjoy your knowledge, is it possible to create an initial template to Excel?

Let me explain in a better way, I would like to know where I can find some details about how to create a previously Excel sheet template to import these data with the Script that you give to me? Is it possible?

I appreciate the help of the spend time with me, explaining these kinds of subject that for you is boring.

Tks!!!

[water]

_Excel_BookOpenText loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.
Excel does not allow to specify a template to use.
But you could open your template, save as a new workbook and then copy the data from the parsed workbook.

[Zedna]

Here is simple convertor from TXT (with TAB) to CSV (with ; )

$text = FileRead('file.txt')
$text = StringReplace($text, @TAB, ';', 0, 1)
FileDelete('file.csv')
FileWrite('file.csv', $text)

 

CSV is "natural" format for Excel, so CSV can be just opened (instead of importing TXT).

Edited July 10, 2020 by Zedna

[IronGirl]

I appreciate the help from Water, JCHD and Zedna.

Thank You!

Edited July 15, 2020 by IronGirl