CSV UDF - a UDF for manipulating CSV files

[seangriffin 33]

This UDF provides functions for editing CSV files.

It uses the features of SQLite already existing within AutoIT to open, edit and save data in the CSV format.

Because SQLite is used the performance of the UDF is fast and the functions are accurate.

REQUIREMENTS:

• Windows 64-bit (not tested under Windows 32-bit)
• AutoIt3 3.3 or higher
• sqlite3.exe (included)
• sqlite3.dll (included)

LIST OF FUNCTIONS:

Quote

_CSV_Initialise()
_CSV_Open($csv_file)
_CSV_Exec($csv_handle, $csv_query)
_CSV_GetRecordArray($csv_handle, $row_number_or_query = "", $include_header = False)
_CSV_DisplayArrayResult($csv_result)
_CSV_GetRecordCount($csv_handle)
_CSV_SaveAs($csv_handle, $csv_file, $csv_query = "SELECT * FROM csv;")
_CSV_Cleanup()

EXAMPLES:

Note - To make this example work, you must make sure sqlite3.exe, sqlite3.dll and Item.csv are present in the same folder as the examples.

expandcollapse popup

#include-once
#include "CSV.au3"

ConsoleWrite(@CRLF & "Initialise the CSV handler ... ")
_CSV_Initialise()
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Open the CSV file 'Item.csv' ... ")
Local $item_csv_handle = _CSV_Open("Item.csv")
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Delete all CSV records with an 'Assigned to' value of 'DELI_SIT_S0003' ... ")
_CSV_Exec($item_csv_handle, "delete from csv where `Assigned to` = 'DELI_SIT_S0003';")
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Get all CSV records with the header and display the result ..." & @CRLF & @CRLF)
Local $csv_result = _CSV_GetRecordArray($item_csv_handle, "", True)
_CSV_DisplayArrayResult($csv_result)

ConsoleWrite(@CRLF & "Get the first CSV record without the header and display the result ..." & @CRLF & @CRLF)
Local $csv_result = _CSV_GetRecordArray($item_csv_handle, 1, False)
_CSV_DisplayArrayResult($csv_result)

ConsoleWrite(@CRLF & "Get all CSV records with an 'Assigned to' value of 'DELI_SIT_S0004' and display the result ..." & @CRLF & @CRLF)
Local $csv_result = _CSV_GetRecordArray($item_csv_handle, "select * from csv where `Assigned to` = 'DELI_SIT_S0004';", False)
_CSV_DisplayArrayResult($csv_result)

ConsoleWrite(@CRLF & "Get a count of the number of records in the CSV file ... ")
Local $number_of_records = _CSV_GetRecordCount($item_csv_handle)
ConsoleWrite("There are " & $number_of_records & " records in the CSV." & @CRLF)

ConsoleWrite(@CRLF & "Save the entire CSV file as 'Item complete.csv' ... ")
_CSV_SaveAs($item_csv_handle, "Item complete.csv")
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Sort and Save the entire CSV file as 'Item sorted.csv' ... ")
_CSV_SaveAs($item_csv_handle, "Item sorted.csv", "select * from csv order by `Assigned to`, `Comment 1`;")
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Query records with an 'Assigned to' value of 'DELI_SIT_S0004' and Save as 'Item DELI_SIT_S0004.csv' ... ")
_CSV_SaveAs($item_csv_handle, "Item DELI_SIT_S0004.csv", "select * from csv where `Assigned to` = 'DELI_SIT_S0004';")
ConsoleWrite("Done." & @CRLF)

ConsoleWrite(@CRLF & "Cleanup the CSV handler ... ")
_CSV_Cleanup()
ConsoleWrite("Done." & @CRLF)

DOWNLOAD:

Source: https://github.com/seanhaydongriffin/CSV-UDF

 

Edited February 25, 2019 by seangriffin

[Skeletor 70]

This is cool. I see you need SQLite for this.. what if the user has SQL Server ? 
 

[seangriffin 33]

9 minutes ago, Skeletor said:

This is cool. I see you need SQLite for this.. what if the user has SQL Server ? 
 

No this works strictly with SQLite only.  SQLite functions are already included within AutoIT (see the SQLite UDF already inside AutoIT).  But we still need to provide additionally two files - sqlite3.exe and sqlite3.dll.  sqlite3.exe provides the features to import and export CSV files to and from SQLite, and sqlite3.dll is required for all other SQLite functions.

 

Edited February 25, 2019 by seangriffin