csv, Excel, note pad, UTF-8... I just want to convert a csv exported file to an array...

[MathieuLap]

I honnestly don't get it.

When I start my auto-it code which is supposed to convert a csv file to an array, it doesn’t work. But before starting my autoit code, when I open my csv file in Excel, ythat I don't touch anything, save it and close it, start autoit, it works!?

 

Here is more explanations:

I export some datas from an online CRM database. It returns it in a CSV file. When I open it into Excel or Note pad, I see the same : the datas are separated by a coma and all data is in quotes. for exemple : "first name","last name", "phone number", "etc".  following lines goes like : "john","doe","456-654-6544", etc...

One thing I've noticed:  I have special caracters like é, à, ï.... I saw a couple of times those caracters beeing replaced by strange caracters : like é was replaced by Ã© Not sure how did this happened in my numerous tries cause I try to replicate it and didn’t find the way.

I made sure that in the datas, there were no coma or quote

So with :

 FileReadToArray($sFilePath, $aRetArray, $FRTA_NOCOUNT, ',')
_ArrayDisplay($aRetArray, "Données clients", Default, 😎

Or

FileReadToArray($sFilePath, $aRetArray, $FRTA_NOCOUNT, ' ”,” ')
_ArrayDisplay($aRetArray, "Données clients", Default, 😎

 

it says "no array variable passed to function"

In a way or another, I wanted to be able to easily edit my data In Excel before starting my autoit code. but not all the time : just in the first place and I should run it numerous times, without touching the csv file.

 

I saw that when datas are separated by a semicolon (;), Excel opens a csv file and can edit it right away. No need to click "convert" in the Data menu.

So I decided to edit the csv file and changed all comas for a semi colon and make disapear all quotes

I cuted and pasted a code that I saw in another topic that goes like this : 

 

$szFile = $sFilePath

$szText = FileRead($szFile,FileGetSize($szFile))

$szText = StringReplace($szText, '"','')
$szText = StringReplace($szText, ',',';')

FileDelete($szFile)

FileWrite($szFile,$szText)

 

It works perfectly! So when I open it in Notepad, i see : 

first name; last name; phone number; etc

John; Doe; 546-654-4564; etc

And when I open it in Excel, I see every data in a single box, which is perfect. Easy to find what I’m looking for.

 

But when I start my auto it code with 

_FileReadToArray($sFilePath, $aRetArray, $FRTA_NOCOUNT, ';')
_ArrayDisplay($aRetArray, "Données clients", Default, 😎

it still says "no array variable passed to function"

But before starting my autoit code, when I open my csv file in Excel, that I don't touch anything, just save and close, start autoit, it works??? I see the display of my array!

What the??

I have a begging of an answer : With all the possibity of extention in Excel that I can save, there is .CSV (semicolon separated) and CSV UTF-8 (separated by coma). If i save by UTF-8 it doesnt'work, but I just have to save it back to .csv (semicolon separated) and it works back...

If you have no idea, it would not be the best but a code to open Excel, open the csv file, make it save by excel, close excel would also be appreciated!

 

 

[water]

You are talking about function _FileReadToArray not FileReadToArray, right? Note the underscore that makes the difference.
When you use function _FileReadToArray you have to define the array in advance.

#include <File.au3>
Global $aRetArray
_FileReadToArray($sFilePath, $aRetArray, $FRTA_NOCOUNT, ',')
_ArrayDisplay($aRetArray, "Données clients", Default)

Functions FileReadtoArray and _FileReadToArray do not have the same parameters. Please check the help file.

[junkew]

1. make a simple CSV file with notepad and see it that works

2. then start debugging your not working csv file and potentially that can be related to encoding of your file

Try fileopen with the encoding parameters and us the handle in your filereadtoarray/_filereadtoarray

https://www.autoitscript.com/autoit3/docs/functions/FileOpen.htm

 

[MathieuLap]

Ok thanks guys! you gave me a couple of ideas to test and i'm getting closer!

 

For reminder, I need to : 

1- create an array in autoit from an exported online CRM .csv file

2- Be able to easily edit it in EXCEL.

 

So I did a couple of tests and here is how it goes : 

If I test a simple notepad CSV :

"ça", "noël", "benoît"

"étrange", "être", "ô"

"voilà", "où", "pâte"

And save it under orginaltest.csv I see down of the window :  Windows (CRLF) and UTF-8 (before and after saving it)

if i Run this script : 

#include <date.au3>
#include <Constants.au3>
#include <String.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <File.au3>
#include <winapi.au3>
#include <AutoItConstants.au3>

local $szText
local $aRetArray

local $sFilePathOriginal, $sFilePathProcessed

$sFilePathOriginal = "C:\Users\Utilisateur\Downloads\originaltest.csv"
$sFilePathProcessed = "C:\Users\Utilisateur\Downloads\processedtest.csv"

fileopen($sFilePathOriginal)

$szText = FileRead($sFilePathOriginal)
$szText = StringReplace($szText, '"','')
$szText = StringReplace($szText, ',',';')

msgbox(0,"",$szText)

FileDelete($sFilePathProcessed)
FileWrite($sFilePathProcessed,$szText)

_FileReadToArray($sFilePathProcessed, $aRetArray, $FRTA_NOCOUNT, ';')
_ArrayDisplay($aRetArray, "Données clients", Default, 😎

 

It returns me a csv file separated with semicolon and again with Windows (CRLF) and UTF-8 AND it is converted to an array in autoit. (GREAT!!) 

BUT when I open it in Excel, here is what I see :

 

But even if I can manage to see my special caracters correctly when I open in Excel, I still have another problem. 

When I open my exported database from my CRM online, I see down on the window : UNIX (LF) UTF-8 with BOM

If I run the same program, it does create a new CSV separated by semicolon, no weird caracters ( é ),  and I see down the window : UNIX (LF) UTF-8       (with no BOM)

but it does'nt create an array in autoit and still have weird caracters in Excel : 

 

 

So How do I solve these problems?

 

Oh! And at the beginig, when I saw that I was able to open my csv file from Excel, just save and close and it worked, I tried by my self to find a way to program it. I found this on a forum :

Local $oExcel = _Excel_Open()
_Excel_BookOpen($oExcel, $sFilePath)
_Excel_BookSaveAs($oExcel, $sFilePath, "csvWin")

 It didn't worked out, but since I ran this program, when I open in Excel, I always see weird caracters like é instead of é. is it related??

 

Thanks a lot guys!

 

Mathieu 

 

 

[junkew]

1. Please use the <> code to insert code in your message
2. Please read in detail https://www.autoitscript.com/autoit3/docs/functions/FileOpen.htm

   fileopen("yourfilename", $FO_READ + $FO_UTF8)

3. And for your filewrite you most likely also have to open it first with fileopen to specify the $FO_UTF8

[Zedna]

Instead of FileReadToArray() use FileRead() + StringSplit().

This way you can use encoding parameter in FileOpen().

Edited December 1, 2021 by Zedna