Sorting .txt data to .csv

[Chillers]

Hi all this has been mind boggling for me and have had to go in search for much needed help.

I want to sort txt data automatically to be used with a .csv file so i can use it to create a spreadsheet in openoffice (Calc).

I would do it manually, but I will be using vast amounts of data. I need help with either pointing me in the direction of a useful application or even a simple snippet of coding for a script.

Here is an example of just 1 line of the data:

15 Lion Fan City 3/1/2007 8:14:48 PM   14,893.700 696.71 2,162.27 251.01

Preferably i want some sort of help with find a program or a snippet of code that will recognise stops and spaces between characters and numbers and input a hash mark.

So in summary that 1 line would end up like this:

15# Lion Fan City# 3/1/2007# 8:14:48#   14,893.700# 696.71# 2,162.27# 251.01#

Henceforth i would be able to open all of the converted and saved .csv file, using openoffice (Calc) directly into a sorted spreadsheet.

Any help on this would be very much appreciated.

[Chriz]

I have made an program in java that will do this for you. It has been uploaded here. To run it, save it to its own directory, create a COPY of the txt file you need as an input and call it infile.txt create another txt file called outfile.txt. Put both these files in the same directory as the program you downloaded. Then double click the downloaded file. Nothing will look to be happening but after a while, the outfile will update with all the information.

You will need java installed to run this program
The lines will need to be exactly in the same layout as the one you posted
All the numbers at the end of the line will need to have decimal points in them. If they don't have decimal points in them post here and I will change the program

If you don't want to download the program in case it has a virus etc then you could send me the file you want changed and I will send the completed version back.

[Snurfen]

Hey chriz, nice bit of code - do you mind if I pinch it and have a play - I occsaionaly need to perform bizzare sorts from odd output strings. Ever moved from a "firstname, lastname" domain to a "lastname, firstname" domain and tried to copy the security groups? Nightmare! hacking this baby would've saved me hours.

[Chriz]

nope. I don't mind a bit. I don't know that it would be that useful in the situation you have named as this just looks for certain patterns and writes the data in the order it was given to it. To alter the data order you would have to store each String in a variable or something before writing it.

[Snurfen]

Cheers, I'll have a play and let you know when I make a monumental hash of it

[Chillers]

Brilliant, Thank-you!

There is one small problem. It does not copy the names (Lion Fan City) to the outfile.txt

I also forgot to add a ')' in the data line and one last number at the end as highlighted in red:

Data lines are exactly like this:

1) X.G.A. 2/24/2007 5:05:34 PM   27,857.403 1,580.36 3,999.99 354.92 50   
2) Magi Nation 2/25/2007 7:33:58 PM   27,671.491 1,019.95 4,779.99 341.98 7

If you could edit your script to adapt these settings it would be great! The script works great apart from these 3 problems, two of which is my own fault.

It there any way to customise this script?

[Chriz]

Ok the changes you have specified have been made. The program is uploaded here http://www.megaupload.com/?d=O3RXNJ14

[Chillers]

Works great! No problems. Thankyou very much!