noob62

Compare CSV Files

15 posts in this topic

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.

Share this post


Link to post
Share on other sites



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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

noob62,

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

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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

 


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

noob62,

Great news - glad you got it working.

M23

 


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

... 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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now