change, extract, and save text from csv files on server

[trauch]

I need a pretty straight forward all-be-it complex script and would appreciate any help.

 

I have multiple csv files that will be saved on our server daily. They will be the only files located in a directory and will be updated daily. I need to manipulate, extract data, and save as a new files also to be stored in the directory (overwritten each day). Regarding the manipulation; the original files are tab delimited .csv files and need to be converted to ; delimited files. Also the files contain date fields but they are not in a recognizable format. As you can see in the sample below, the dates are listed as Feb 21 2008 and I need them to be replaced with this format [02-21-2008]. I know how to do this manually (text editor) by replacing [ 2008] with [-2008] and changing the month portion from text to numbers ( ie [Feb ] to [02-] for each month). The resulting month field would be formated as follows [02-21-2008]

 

That's all the textual modification needed.

 

Now all I need to do is extract all text from the word [Day] through but NOT inclusive of the word [Region] (approximately 30 lines).

 

Here is an example file. Please note that the amount of lines in the text file will vary but each file will contain the key indicators above. Also, I would need to write the extracted data to a txt or csv file that will be overwritten with new data each day.

 

----------------------------------

Item Percentage

1.00 0%

 

 

Day Item Percentage

Nov 12 2008 1.09 2%

Nov 13 2008 1.10 2%

Nov 14 2008 1.01 2%

Nov 15 2008 0.88 2%

Nov 16 2008 0.85 2%

Nov 17 2008 1.04 2%

Nov 18 2008 1.09 2%

Nov 19 2008 1.08 2%

Nov 20 2008 1.10 2%

Nov 21 2008 1.03 2%

Nov 22 2008 0.88 2%

Nov 23 2008 0.88 2%

Nov 24 2008 1.08 2%

Nov 25 2008 1.04 2%

Nov 26 2008 0.99 2%

Nov 27 2008 0.86 2%

Nov 28 2008 0.87 2%

Nov 29 2008 0.86 2%

Nov 30 2008 0.90 2%

Dec 1 2008 1.09 2%

Dec 2 2008 1.05 2%

Dec 3 2008 1.10 2%

Dec 4 2008 1.09 2%

Dec 5 2008 1.04 2%

Dec 6 2008 0.89 2%

Dec 7 2008 0.92 2%

Dec 8 2008 1.12 2%

Dec 9 2008 1.07 2%

Dec 10 2008 0 >10%

Dec 11 2008 0 >10%

 

 

Region Item Percentage

Area1 1.000 0%

Area2 0.710 3%

Area3 0.540 3%

Area4 0.485 3%

Area5 0.455 3%

Area6 0.185 3%

Area7 0.130 3%

Area8 0.115 3%

Area9 0.115 3%

Area10 0.110 3%

 

 

City Item Percentage

City1 1.000 0%

City2 0.755 3%

City3 0.560 3%

City4 0.555 3%

City5 0.495 3%

City6 0.450 3%

City7 0.430 5%

City8 0.400 3%

City9 0.395 3%

City10 0.385 5%

 

 

Language Item Percentage

English 1.000 0%

Dutch 0.285 3%

Polish 0.200 3%

Portuguese 0.190 3%

German 0.180 3%

Spanish 0.160 3%

Italian 0.160 3%

Chinese 0.135 3%

Turkish 0.125 3%

French 0.115 3%

 

----------------------------------

 

For reference, here is how I would like the data to look like after extraction and saving.

 

Day;Item;Percentage

Nov 12 2008;1.09;2%

Nov 13 2008;1.10;2%

Nov 14 2008;1.01;2%

Nov 15 2008;0.88;2%

Nov 16 2008;0.85;2%

Nov 17 2008;1.04;2%

Nov 18 2008;1.09;2%

Nov 19 2008;1.08;2%

Nov 20 2008;1.10;2%

Nov 21 2008;1.03;2%

Nov 22 2008;0.88;2%

Nov 23 2008;0.88;2%

Nov 24 2008;1.08;2%

Nov 25 2008;1.04;2%

Nov 26 2008;0.99;2%

Nov 27 2008;0.86;2%

Nov 28 2008;0.87;2%

Nov 29 2008;0.86;2%

Nov 30 2008;0.90;2%

Dec 1 2008;1.09;2%

Dec 2 2008;1.05;2%

Dec 3 2008;1.10;2%

Dec 4 2008;1.09;2%

Dec 5 2008;1.04;2%

Dec 6 2008;0.89;2%

Dec 7 2008;0.92;2%

Dec 8 2008;1.12;2%

Dec 9 2008;1.07;2%

Dec 10 2008;0;>10%

Dec 11 2008;0;>10%

[Mchl]

Use fgetcsv in while to read a file line by line, until you get to 'Region'.

 

Use strtotime and date to convert dates

 

Use fputcsv to save your data as ; delimited csv

[trauch]

Thank you, Mchl.

 

That seems like it will do the trick.

For a short cut, since I'm really new at this and under a time crunch, can you get me started with samples of what I would need to write in the php code? I've never really written code just merely looked at existing code and modified to suit my needs.

 

Thanks for any help you can provide.

[Mchl]

Basically you need a lot of motivation.

 

Just start, and see what can you come up with. If you have problems, ask here.

 

If you're looking for good IDE, I say NetBeans 6.5

 

If you want to install Apache, MySQL and PHP on Windows, to test your scripts locally, I suggest WampServer 2.0