CSV file to Excel, change + to =function

jjjj12212

Hi everyone,

when I open the CSV file to Excel, it converts all the phone numbers that have +1 to a function =1-800-123-123 and my value is -1045

I would put this as a bug but I actually changed a few things already.

Instead of having a CSV file, I wanted a TAB file (tab delimited).

what I have done was modify csv_export.php:
line 36

changed .csv to .tab

line 103

changed fputcsv($f, $rec)

fputcsv($f, $rec, "\t")

so now I have a tab delimited file, I would like to add on each data =LEFT($rec, 99)
so what will be the best method of showing the output of =LEFT($rec, 99) in the .tab file?

ajb

Sorry, we encountered an error while displaying this content. If you're a user, please try again later. If you're an administrator, take a look in your Flarum log files for more information.

jjjj12212

Hi Adam,

your very close, here is an example what we are trying to do:

current output of CSV file: "+1-800-888-8888", "jjjj12212", ....................

output that we need: "=LEFT("+1-800-888-8888", 99)", "=LEFT("jjjj12212", 99)", ........................

so when PHP does it's loop to insert the data in the CSV, also add the string "=left........" inside the CSV as well.

I don't know the framework enough to know which variable is the actual data.

thanks,

jjjj12212

ajb

Oh it's simple 🙂

Just go to modules/Utils/RecordBrowser/csv_export.php

Here we have one special case for currency. Currency fields are exported as two columns. One with value, one with currency symbol. But we can simply add this LEFT to every column.

I suggest to put this code in line 103 (right before fputcsv)



foreach ($rec as & $entry) $entry = "=LEFT(\"$entry\", 99)";

Regards,
Adam

jjjj12212

awesome!

I know I was close but I didn't know if it was the $rec var to use.

thanks again Adam.

jjjj12212