Compare CSV Files

[noob62]

I'm trying to import two different CSV files into two arrays and find the differences in the two files where a string may exist in CSV_one column 1 but, not exist in CSV_two column 2.

The problem I'm having is the script isn't loading the second CSV into the array. is there something I'm doing wrong?

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Local $aRetArray1
Local $aRetArray2
Local $Rows, $Find, $Result
Local $i = 0
Local $sFilePath1 = "C:\Script\CSV_one.csv"
Local $sFilePath2 = "C:\Script\CSV_two.csv"


_FileReadToArray($sFilePath2, $aRetArray1, $FRTA_NOCOUNT, ",")
$Rows = UBound($aRetArray1, $UBOUND_ROWS)

_FileReadToArray($sFilePath1, $aRetArray2, $FRTA_NOCOUNT, ",")


While $i < $Rows
      $Find = $aRetArray1[$i][0]
      $Result = _ArraySearch($aRetArray2, $Find)
      if @error = 6 Then
         FileWrite("C:\Script\compare.txt" , $aRetArray1[$i][0] & "," & $aRetArray1[0][$i] & @CRLF)
      EndIf
   $i = $i + 1
  WEnd

Also, if the CSV has multiple columns how do I write those values to a file as well since from what I understand File Read to Array will only do a 2D array.

[Melba23]

noob62,

Your code runs for me. How do you know you are not loading the second CSV file? What does _ArrayDisplay show you?

And you can write an array into a CSV file using _FileWriteFromArray.

M23

[noob62]

When I run _ArrayDisplay it will only show the first Array and does not show anything for the second array. I added in a msgbox in the loop to show what is happening during the compare and it shows error -1

[Melba23]

noob62,

If the second array is not displayed then you are not loading the file. What error do you get from _FileReadToArray? Are you sure the file exists on the path you specify?

M23

[noob62]

Getting error 0 from the FileReadToArray it even happens if I point it to the same file. I know the files are there and can see the files

[Melba23]

noob62,

Can you please attach the files so I can take a look and see if I can spot anything amiss.

M23

[noob62]

Added in the @error and it is showing a 3 on the CSV files that I'm trying to import and compare. I looked up the error "3 - File lines have different numbers of fields (only if $FRTA_INTARRAYS flag not set)" does that mean there are to many spaces in some of the lines or other characters that it doesn't know what to do with? 

[Melba23]

noob62,

That error means that the file in question does not have the same number of fields on each line, so it cannot be loaded into a "square" array. Again, can you attach the files so I can take a look and see how best to proceed.

M23

[czardas]

Try _CSVSplit(). It doesn't care if you have missing cells: it just adds them to rows where they are missing.  https://www.autoitscript.com/forum/topic/155748-csvsplit/ You have to read the file using FileRead() as shown in the examples lower down the page. I'm not sure if a consistent number of entries per row is part of the standard csv spec (perhaps it is, but I don't recall reading it). Actually I don't think there is any official standard - unless that's recently changed.

Edited September 23, 2015 by czardas

[TheSaint]

It's probably not relevant, but it seems you are asking for possible trouble when not matching up numbers.

$sFilePath2, $aRetArray1
$sFilePath1, $aRetArray2

Shouldn't file number match array number? It would seem less chance of a mistake occurring.

$sFilePath1, $aRetArray1
$sFilePath2, $aRetArray2

 

[noob62]

I figured out the error, there were objects that had commas in the string causing it to think there were additional fields. Thanks for all the help!

[Melba23]

noob62,

Great news - glad you got it working.

M23

 

[czardas]

... there were objects that had commas in the string causing it to think there were additional fields. Thanks for all the help!

The additional commas are likely to be wrapped in double quotes (if the syntax is correct) and _FileReadToArray() doesn't currently cater for this as you have discovered. It is intended to be a basic function - not catering for this specific syntax. You are better off with a CSV dedicated function. There a few in example scripts besides the one I wrote.

[noob62]

What would be the best way to display a progress bar for comparing of the CSV files? Would i just add in a variable to increase each time and display that variable?

[czardas]

I think that is the easiest way (if I understand you correctly). For a progress bar I would calculate the percentage of the total number of elements compared so far. I haven't done much with progress bars on GUIs, so someone else might be able to give you better advice. Try it first and see how far you get. Ask a question if you get stuck and post your code you: so we can see what you are attempting to do and offer assistance.

Edit: You don't necessarily have to calculate the percentage on every comparison. It all depends on the total number of comparisons you need to make. You might want to run additional code to update the progress bar after every 10, 50 or 100 comparisons etc... If you do something like that, it should run faster (slightly at least).

Edited September 24, 2015 by czardas