(solved) write CSV from Array efficiently

[Skysnake]

Hi there

I have searched and found many posts on READING CSV.  What I need to do is WRITE CSV.

Typically I have a Listview, I use @guinness's excellent  _GUICtrlListView_CreateArray to read that LV to Array. Currently I use _FileWriteFromArray to write the output.  It is fast and it is easy.

The benefits of using these Array functions are their inherit flexibility.  The code is portable.  Plug it into LV report and you can write to file instantly.  No formatting issues.

To be sure, my problem is not with the Listview or Array.  My problem is with the file write to CSV: it should be fast, accurate and efficient.

Part of the problem is Microsoft Excel's insistence that my CSVs are not what they appear to be...

 

I am inspired by the simplicity of SQLITE3.EXE's dot command options:

.headers on
.mode csv
.once output.csv

 

Problem is that I want to export the text packed in characters, such as ' " ' with fields delimited by another character, such as ' , '.

Current method using _FileWriteFromArray outputs data like this

12-04-2016|           0.00|         131.00|131|Description
12-04-2016|           0.00|         132.00|132|Description 132 rece
12-04-2016|         998.00|           0.00|998|Description Receipt
12-04-2016|           0.00|         900.00|900|Description

What I want to achieve is this:

"12-04-2016","           0.00","         131.00","131","Description"
"12-04-2016","           0.00","         132.00","132","Description 132 rece"
"12-04-2016","         998.00","           0.00","998","Description Receipt"
"12-04-2016","           0.00","         900.00","900","Description"

I have done this. The long way. I have written the output one $aResult[$i][1] item at a time, spaced with the $text & $field markers.  I have placed this in an array with additional columns just to fit in the formatting. I have also played with the idea of inserting the array into a sqlite db, then run sqlite3's dot commands on that.

Is there a different method?  Is there an efficient method to specify FileWriteFromArraytoCSV?  Does anyone have ideas?  I am sure I will know how to code this, I just need advice on the most efficient way of going about it. If I missed any threads, please enlighten  me.

Thanks for reading

Edited April 22, 2016 by Skysnake

[czardas]

Have you tried my UDF? https://www.autoitscript.com/forum/topic/155748-csvsplit/
_ArrayToCSV() does not wrap all fields in double quotes: only those that require it.

[AutoBert]

i think this

_ReplaceStringInFile('Test.csv','|','","')
_ReplaceStringInFile('Test.csv',@CRLF,'"'&@CRLF&'"')

does most of the job. Just inserting a '"' at begin and deliting last line and whole job is done.

[Skysnake]

@czardas you deserve 5 stars.  Excellent. 

Thank you kindly

Edited September 13, 2017 by Skysnake

[czardas]

It's old code, but it's also robust and has not yet let me down. I'm happy if it helps.

[Skysnake]

I should perhaps post that the default code works faultlessly on Win7 with OpenOffice, and on Win10 with MS Office Excel 2013.  

Even Excel opened the file without complaints