Create CSV from text file.

[lynxus]

Hi Guys,

 

Does anyone know to do this in php?

 

Give it a .txt file with details like below:

And output like further down?

 

Ive got 10000's of rows to go through and doing this manually will kill me.

 

Orginal data

interface Vlan207
description Some customer
interface Vlan214
description Some customer
service-policy input 4Mbps
service-policy output 4Mbps
interface Vlan218
interface Vlan219
interface Vlan221
description Some customer
interface Vlan223
description Some customer
interface Vlan236
description Some customer
service-policy input 4Mbps
service-policy output 4Mbps

 

exported code ( from the php script )

(Interface # , description , bandwidth

interface Vlan207,description Some customer,None
interface Vlan214, description Some customer, service-policy input 4Mbps service-policy output 4Mbps
interface Vlan218,No Description,No bandwidth
interface Vlan219,No Description,No bandwidth
interface Vlan221, description Some customer,None
interface Vlan223, description Some customer,None
interface Vlan236, description Some customer, service-policy input 4Mbps  service-policy output 4Mbps

 

Any ideas?

The problem is also, that sometimes after the interface vlan there isnt any more details.

 

So sometimes, it wont have a description and othertimes it may not have a bandwidth statement. OR both.

 

Any thoughts?

 

Thanks Muchly.

[cags]

Will need more details about the composition of description Some customer and the file in general to give a definitive answer, but this should be a good start...

 

$rows = preg_split('#(?=interface Vlan[0-9]+)#', $input);
$lines = array();

foreach($rows as $row) {
   $columns = explode('\r\n', $row);
    $lines[] = implode(',', $columns);
}

[lynxus]

Well, The file goes like this:

 

interface Vlan1

description Management LAN

interface Vlan41

description Hamiltons

interface Vlan65

description Somelan

service-policy input 2Mbps

service-policy output 2Mbps

interface Vlan66

description HSomelan

interface Vlan70

description Somelan

interface Vlan71

description Somelan

service-policy input 4Mbps

service-policy output 4Mbps

interface Vlan72

description Somelan

service-policy input 4Mbps

service-policy output 4Mbps

interface Vlan73

description Somelan

service-policy input 6Mbps

service-policy output 6Mbps

interface Vlan74

description Somelan

interface Vlan75

description Somelan

service-policy input 20Mbps

service-policy output 20Mbps

interface Vlan76

description Somelan

interface Vlan77

description Somelan

service-policy input 2Mbps

service-policy output 2Mbps

 

 

I suppose, What i want is basically to add a comma at the beginning or each line apart from the lines starting interface.

 

IE:

interface Vlan75

,description Somelan

,service-policy input 20Mbps

,service-policy output 20Mbps

interface Vlan76

,description Somelan

interface Vlan77

,description Somelan

,service-policy input 2Mbps

,service-policy output 2Mbps

 

This way i could import it into excel so it shoudl show like:

 

interface Vlan75 ,description Somelan ,service-policy input 20Mbps ,service-policy output 20Mbps

interface Vlan76 ,description Somelan

interface Vlan77 ,description Somelan ,service-policy input 2Mbps ,service-policy output 2Mbps

 

 

Thanks

Graham

[cags]

That being the case, if you take the code from my previous post and simply implode('\r\n', $lines) and then write it to a file, that should be it. Potential issues will be if the description line includes new line characters or if any line includes commas.

[lynxus]

Ah ok,

Im sorry however, Im not uber great with php?

 

How do i get input to read data.txt and how do i write it to a txt file?

 

-Graham

[ignace]

Here I tried this one and it worked for me:

 

$csvLines = '';
$csvLine = '';
$lines = file('string2csv.txt');
foreach ($lines as $line) {
    $line = trim($line, "\r\n\t");
    $words = str_word_count($line, 1 /* mode 1: returns an array containing all the words found inside the string */);
    $identifier = $words[0];
    switch ($identifier) {
       case 'interface':
           $csvLines .= $csvLine . PHP_EOL;
           $csvLine = $line; break;
       case 'description':
       case 'service-policy':
           $csvLine .= ',' . $line; break;
    }
}
$csvLines .= $csvLine;

echo $csvLines;

 

Returns:

interface Vlan207,  description Some customer
interface Vlan214,  description Some customer,  service-policy input 4Mbps,  service-policy output 4Mbps
interface Vlan218
interface Vlan219
interface Vlan221,  description Some customer
interface Vlan223,  description Some customer
interface Vlan236,  description Some customer,  service-policy input 4Mbps,  service-policy output 4Mbps

 

That being said I favor cags code.

[lynxus]

OH WOW!

Thanks guys!

 

+100000 Internets to all of you for helping

[Deoctor]

but one thing ignace

 

i have tried ur code.it works absolutely fine.

but the output is not exactly the same as required by the guy..

, description Management LAN interface Vlan41, description Hamiltons interface Vlan65, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps interface Vlan66, description HSomelan interface Vlan70, description Somelan interface Vlan71, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps interface Vlan72, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps interface Vlan73, description Somelan, service-policy input 6Mbps, service-policy output 6Mbps interface Vlan74, description Somelan interface Vlan75, description Somelan, service-policy input 20Mbps, service-policy output 20Mbps interface Vlan76, description Somelan interface Vlan77, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps

 

it is giving as the above..

the required is

interface Vlan207,description Some customer,None

interface Vlan214, description Some customer, service-policy input 4Mbps service-policy output 4Mbps

interface Vlan218,No Description,No bandwidth

interface Vlan219,No Description,No bandwidth

interface Vlan221, description Some customer,None

interface Vlan223, description Some customer,None

interface Vlan236, description Some customer, service-policy input 4Mbps  service-policy output 4Mbps

 

the value None should come if there is nothing at the bandwidth place..

and also each one should come in a single line

 

 

[ignace]

@ ym_chaitu I tried the input from the OP which is:

 

interface Vlan1
description Management LAN
interface Vlan41
description Hamiltons
interface Vlan65
description Somelan
service-policy input 2Mbps
service-policy output 2Mbps
interface Vlan66
description HSomelan
interface Vlan70
description Somelan
interface Vlan71
description Somelan
service-policy input 4Mbps
service-policy output 4Mbps
interface Vlan72
description Somelan
service-policy input 4Mbps
service-policy output 4Mbps
interface Vlan73
description Somelan
service-policy input 6Mbps
service-policy output 6Mbps
interface Vlan74
description Somelan
interface Vlan75
description Somelan
service-policy input 20Mbps
service-policy output 20Mbps
interface Vlan76
description Somelan
interface Vlan77
description Somelan
service-policy input 2Mbps
service-policy output 2Mbps

 

And my script returned:

 

interface Vlan1,description Management LAN
interface Vlan41,description Hamiltons
interface Vlan65,description Somelan,service-policy input 2Mbps,service-policy output 2Mbps
interface Vlan66,description HSomelan
interface Vlan70,description Somelan
interface Vlan71,description Somelan,service-policy input 4Mbps,service-policy output 4Mbps
interface Vlan72,description Somelan,service-policy input 4Mbps,service-policy output 4Mbps
interface Vlan73,description Somelan,service-policy input 6Mbps,service-policy output 6Mbps
interface Vlan74,description Somelan
interface Vlan75,description Somelan,service-policy input 20Mbps,service-policy output 20Mbps
interface Vlan76,description Somelan
interface Vlan77,description Somelan,service-policy input 2Mbps,service-policy output 2Mbps

 

Which is as far as I can tell what the OP asked for.

[lynxus]

Either way is fine

It works as is , So thats good

 

Thanks guys.

[Deoctor]

instead of echo i think u can give it to write in a text file and also the first word is not coming..

<?php
$csvLines = '';
$csvLine = '';
$lines = file('string2csv.txt');
foreach ($lines as $line) {
    $line = trim($line, "\r\n\t");
    $words = str_word_count($line, 1 /* mode 1: returns an array containing all the words found inside the string */);
    $identifier = $words[0];
    switch ($identifier) {
       case 'interface':
           $csvLines .= $csvLine . PHP_EOL;
           $csvLine = $line; break;
       case 'description':
       case 'service-policy':
           $csvLine .= ',' . $line; break;
    }
}
$csvLines .= $csvLine;
$fh = fopen("csv.txt", 'w');
fwrite($fh, $csvLines);

//echo $csvLines;
?>

 

can u just check it out

[ignace]

@ym_chaitu You do have one point:

 

the value None should come if there is nothing at the bandwidth place..

 

So I re-wrote my code to:

 

function addInterface($interface, $description = 'No Description',
        $input = 'No Input Service-Policy', $output = 'No Output Service-Policy') {
    return "$interface, $description, $input, $output";
}

function appendCsvLine($line, &$lines) {
    $lines = $lines . $line . PHP_EOL;
}

function appendNoneEmpty(&$args, &$lines) {
    if (sizeof($args)) {
        $line = call_user_func_array('addInterface', $args);
        appendCsvLine($line, $lines);
        $args = array();
    }
}

function getIdentifier($line) {
    return current(str_word_count($line, 1));
}

$csvLines = '';
$args = array();
$lines = file('string2csv.txt');
foreach ($lines as $line) {
    $line = trim($line, " \r\n\t");
    $identifier = getIdentifier($line);
    switch ($identifier) {
       case 'interface':
           appendNoneEmpty($args, $csvLines);
           $args[] = $line; break;
       case 'description':
       case 'service-policy':
           $args[] = $line; break;
    }
}
appendNoneEmpty($args, $csvLines);

echo $csvLines;

 

And it now returns:

 

interface Vlan1, description Management LAN, No Input Service-Policy, No Output Service-Policy
interface Vlan41, description Hamiltons, No Input Service-Policy, No Output Service-Policy
interface Vlan65, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps
interface Vlan66, description HSomelan, No Input Service-Policy, No Output Service-Policy
interface Vlan70, description Somelan, No Input Service-Policy, No Output Service-Policy
interface Vlan71, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps
interface Vlan72, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps
interface Vlan73, description Somelan, service-policy input 6Mbps, service-policy output 6Mbps
interface Vlan74, description Somelan, No Input Service-Policy, No Output Service-Policy
interface Vlan75, description Somelan, service-policy input 20Mbps, service-policy output 20Mbps
interface Vlan76, description Somelan, No Input Service-Policy, No Output Service-Policy
interface Vlan77, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps

[Deoctor]

Bravo that really wonderfull...

 

but only one small issue

check this output..

description Management LAN, No Description, No Input Service-Policy, No Output Service-Policy

interface Vlan41, description Hamiltons, No Input Service-Policy, No Output Service-Policy

interface Vlan65, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps

interface Vlan66, description HSomelan, No Input Service-Policy, No Output Service-Policy

interface Vlan70, description Somelan, No Input Service-Policy, No Output Service-Policy

interface Vlan71, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps

interface Vlan72, description Somelan, service-policy input 4Mbps, service-policy output 4Mbps

interface Vlan73, description Somelan, service-policy input 6Mbps, service-policy output 6Mbps

interface Vlan74, description Somelan, No Input Service-Policy, No Output Service-Policy

interface Vlan75, description Somelan, service-policy input 20Mbps, service-policy output 20Mbps

interface Vlan76, description Somelan, No Input Service-Policy, No Output Service-Policy

interface Vlan77, description Somelan, service-policy input 2Mbps, service-policy output 2Mbps

 

the first line as it didnt took the

interface Vlan1

 

it gave me this type of output.

 

remaining everything is quite great..

[ignace]

I had that same problem and added $args[] = $line to the case 'interface' weird your know getting that what are your specs?

[Deoctor]

i think the issue is that u are hardcoding the name interface..

but the input i gave is Interface.

so i think it is causing the I to neglect it out..

 

any way great script... . after i changed it it is working fine

[Cardale]

Now that this is solved.  Why would anyone want to use CSV?

[lynxus]

I was using it so i could insert the data into excel easily.

 

( Like i said , theres 100000 odd rows i need to import + possibly more, So i needed a fats way to have them paste into their own columns for data sorting. )

[ignace]

@ym_chaitu

 

i think the issue is that u are hardcoding the name interface..

but the input i gave is Interface.

 

function addInterface($interface, $description = 'No Description',
        $input = 'No Input Service-Policy', $output = 'No Output Service-Policy') {
    return "$interface, $description, $input, $output";
}

function appendCsvLine($line, &$lines) {
    $lines = $lines . $line . PHP_EOL;
}

function appendNoneEmpty(&$args, &$lines) {
    if (sizeof($args)) {
        $line = call_user_func_array('addInterface', $args);
        appendCsvLine($line, $lines);
        $args = array();
    }
}

function getIdentifier($line) {
    return current(str_word_count($line, 1));
}

$csvLines = '';
$args = array();
$lines = file('string2csv.txt');
foreach ($lines as $line) {
    $line = trim($line, " \r\n\t");
    $identifier = getIdentifier($line);
    switch (strtolower($identifier)) {
       case 'interface':
           appendNoneEmpty($args, $csvLines);
           $args[] = $line; break;
       case 'description':
       case 'service-policy':
           $args[] = $line; break;
    }
}
appendNoneEmpty($args, $csvLines);

echo $csvLines;

 

Solved it! Now it doesn't matter if you give Interface, interface, iNTERFACE or iNtERfACe

[Deoctor]

nice one dude...

 

but i got one problem 

hope u can solve it out for me..

 

check this topic

http://www.phpfreaks.com/forums/index.php/topic,280896.0.html

 

here what i wanted is as rajiv told like pass the enire xml as a string as doing that i got the result what i wanted but the thing is that i need to read the xml file and check for the attribute and change it..

hope u can do it out..