convert .txt to .xlsx

[speroni17]

Hey guys,

I am looking for advice on how to write a simple script to convert a txt file to xlsx. The data output in the text file looks like the following:

06/11/2015 15:29:00,091.6,100.4,100.0,100.5
06/11/2015 15:30:00,091.6,100.4,100.0,100.5
06/11/2015 15:31:00,091.6,100.4,099.9,100.4
06/11/2015 15:32:00,091.7,100.2,099.9,100.4
06/11/2015 15:33:00,091.7,100.2,099.9,100.4
06/11/2015 15:34:00,091.7,100.1,099.9,100.2

I would like to write something that takes each data entry (6 in total), and separates them into separate columns in an excel file. Any help with this is much appreciated. Thanks

 

[iFargle]

Why not just rename the .txt to .csv? those are comma separated values

 

[speroni17]

Thanks for the response!
That definitely is a step forward. However, I would also like to separate the date and time into separate columns.

 

[iFargle]

Ohhh all you'd have to do is replace the " " with a "," and you're all good.

The reason I'm not wanting to convert a .txt to a .xlsx is because a .xlsx contains a lot of metadata and a conversion isn't quite that simple. a .csv will more than likely suffice, unless you want to get super technical with charts and whatnot

But yeah, a script to replace a " " with "," would work. how are you parsing this file? line by line as they come in or in bulk? (ie the log builds up for a few weeks, you take it out, and parse it)

 

[speroni17]

I most likely will be parsing it in bulk, setting a schedule to download the log data on a weekly basis, convert to .csv, and save in a directory. However, if it is possible to parse it in real time, and have an updated log, that would be pretty freakin sweet. I will probably want to display chart data that shows any deviations from a set range in values. i.e if those 4 end values go out of range from 96 to 104 I would like to have that displayed in a graph.

Thanks!

 

[speroni17]

If you prefer to communicate through email, let me know. I tried to send you my address through a pm, but the message was blocked for containing content similar to spam

 

[iFargle]

Hm, parsing in real time is definitely possible. you might not need to convert, you could just import the data into excel and have it update every 30 seconds or even quicker if you want. From there you can parse the data how you like (First, parse with CSV, then perform an excel SPLIT() function on the date / time field) -- This will pull data from the file on an interval you specify as seen here:

 

[carnageX]

Ohhh all you'd have to do is replace the " " with a "," and you're all good.

The reason I'm not wanting to convert a .txt to a .xlsx is because a .xlsx contains a lot of metadata and a conversion isn't quite that simple. a .csv will more than likely suffice, unless you want to get super technical with charts and whatnot

It's easy if you use .NET .

But yeah, a script to replace a " " with "," would work. how are you parsing this file? line by line as they come in or in bulk? (ie the log builds up for a few weeks, you take it out, and parse it)

Yep, I'd replace the spaces with commas, and then have a batch loop that imports into either a CSV or XLSX (if you use the right library for .NET).

You could also probably do this easily with PowerShell, if you didn't want to use one of the .NET languages like C# or VB.

Yes, I'm a .NET developer, hence me suggesting .NET stuff .

 

[iFargle]

It's easy if you use .NET .


Yep, I'd replace the spaces with commas, and then have a batch loop that imports into either a CSV or XLSX (if you use the right library for .NET).

You could also probably do this easily with PowerShell, if you didn't want to use one of the .NET languages like C# or VB.

Yes, I'm a .NET developer, hence me suggesting .NET stuff .

.NET evangelist!

 

[carnageX]

.NET evangelist!

Console.WriteLine("Damn right ");