_FileReadToArray issue [Solved]

[IanN1990]

Hi, 

I am trying to compare the data within two .csv files but am running into an issue. 

#include "file.au3"
Local $ArrayA

_FileReadToArray("c:\test.CSV", $ArrayA, 0, ",")
_ArrayDisplay($ArrayA)

This is returning an error code of 3 - File lines have different numbers of fields (only if $FRTA_INTARRAYS flag not set).

Example Data

ID                 Access Granted
TESTING      Mar 31, 2016 2:03:31 PM
TESTING      Mar 1, 2017 10:31:00 AM

 

I believe this is caused because the Access Granted is a date containing a , which is also the condition for the split. How would i get around this?

Edited April 28, 2017 by IanN1990

[BrewManNH]

Why do you need to split the lines? Can't you just do it without doing the stringsplit?

[kylomas]

IanN1990,

The date is NOT the cause.  Some record(s) have different number of fields (commas).

Kylomas

[IanN1990]

3 hours ago, BrewManNH said:

Why do you need to split the lines? Can't you just do it without doing the stringsplit?

Though my example was limited, the full .csv file is 10,000 entries and 7 columns wide. Some data does repeats between columns.

1 hour ago, kylomas said:

IanN1990,

The date is NOT the cause.  Some record(s) have different number of fields (commas).

Kylomas

From my troubleshooting i cant see what else it can be. Below is a improved example illustrating the problem
 

#include "file.au3"
#NoTrayIcon
Local $ArrayA
Local $ArrayB

_FileReadToArray("C:\Working.txt", $ArrayA, 0, ",")
ConsoleWrite(@error & @crlf)
_ArrayDisplay($ArrayA)

_FileReadToArray("C:\NotWorking.txt", $ArrayB, 0, ",")
ConsoleWrite(@error & @crlf)
_ArrayDisplay($ArrayB)

Working.txt - (Edited .csv saved as txt removing commas from date)

ID,Name,Email,Username,DB,Access Granted,Change
0,Test Test,test.test@test.com,test_test,test,Mar 31 2016 2:03:31 PM,TESTING
0,Test Test,test.test@test.com,test_test,test,Mar 1 2017 10:31:00 AM,TESTING

Notworking.txt - (Original unedited .csv saved as .txt)

ID,Name,Email,Username,DB,Access Granted,Change
0,Test Test,test.test@test.com,test_test,test,"Mar 31, 2016 2:03:31 PM",TESTING
0,Test Test,test.test@test.com,test_test,test,"Mar 1, 2017 10:31:00 AM",TESTING

[kylomas]

InaN1990,

Are you sure that you don't have a blank line at the start or end?

Can you post the file that you are using?

kylomas

edit: You might try a simple script to run through each line and counts the commas per line.  Stringreplace give you a count of the number of replacements.

Edited April 27, 2017 by kylomas
additional data

[IanN1990]

Files uploaded as requested.

Funny enough i had the same idea and am editing _FileReadToArray to do just that.

NotWorking.txt

Working.txt

Edited April 28, 2017 by IanN1990

[Subz]

If you have Excel you could just change the extension to .csv and it will create the array correctly.

#include <Array.au3>
#include <Excel.au3>
Local $sFilePath = @ScriptDir & "\NotWorking.csv"
Local $oExcel = _Excel_Open(False)
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $aWorkBook = _Excel_RangeRead($oWorkBook)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)
_ArrayDisplay($aWorkBook, "Before")
For $i = 0 To UBound($aWorkBook) - 1
    $aWorkBook[$i][5] = StringReplace($aWorkBook[$i][5], ",", "")
Next
_ArrayDisplay($aWorkBook, "After")

 

[BrewManNH]

As explained in the help file for _FileReadToArray, the stringsplit algorithm in the function is limited. Use one of the many CSV functions that you can find in the Examples forum. For example this one;

 

[IanN1990]

7 hours ago, Subz said:

If you have Excel you could just change the extension to .csv and it will create the array correctly.

The code provided works but I have found Excel com objects very unreliable on my system.

7 hours ago, BrewManNH said:

As explained in the help file for _FileReadToArray, the stringsplit algorithm in the function is limited. Use one of the many CSV functions that you can find in the Examples forum. For example this one;

CSVSplit was the answer  Thank you BrewManNH

Edited April 28, 2017 by IanN1990

[BrewManNH]