Jump to content
Sign in to follow this  
seangriffin

CSV UDF - a UDF for manipulating CSV files

Recommended Posts

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.

#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 by seangriffin

Cheers, Sean.

See my other UDFs:

Chrome UDF - Automate Chrome | SAP UDF - Automate SAP | Java UDF - Automate Java Applications & Applets | Tesseract (OCR) UDF - Capture text from applications, controls and the desktop | Textract (OCR) UDF - Capture text from applications and controls | FileSystemMonitor UDF - File, Folder, Drive and Shell Monitoring | VLC (Media Player) UDF - Creating and controlling a VLC control in AutoIT | Google Maps UDF - Creating and controlling Google Maps (inc. GE) in AutoIT | SAPIListBox (Speech Recognition) UDF - Speech Recognition via the Microsoft Speech (SAPI) ListBox | eBay UDF - Automate eBay using the eBay API | ChildProc (Parallel Processing) UDF - Parallel processing functions for AutoIT | HyperCam (Screen Recording) UDF - Automate the HyperCam screen recorder | Twitter UDF - Automate Twitter using OAuth and the Twitter API | cURL UDF - a UDF for transferring data with URL syntax

See my other Tools:

Rapid Menu Writer - Add menus to DVDs in seconds | TV Player - Automates the process of playing videos on an external TV / Monitor | Rapid Video Converter - A tool for resizing and reformatting videos | [topic130531]Rapid DVD Creator - Convert videos to DVD fast and for free | ZapPF - A tool for killing processes and recycling files | Sean's eBay Bargain Hunter - Find last minute bargains in eBay using AutoIT | Sean's GUI Inspector - A scripting tool for querying GUIs | TransLink Journey Planner with maps - Incorporating Google Maps into an Australian Journey Planner | Automate Qt and QWidgets | Brisbane City Council Event Viewer - See what's going on in Brisbane, Australia

Share this post


Link to post
Share on other sites
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 by seangriffin

Cheers, Sean.

See my other UDFs:

Chrome UDF - Automate Chrome | SAP UDF - Automate SAP | Java UDF - Automate Java Applications & Applets | Tesseract (OCR) UDF - Capture text from applications, controls and the desktop | Textract (OCR) UDF - Capture text from applications and controls | FileSystemMonitor UDF - File, Folder, Drive and Shell Monitoring | VLC (Media Player) UDF - Creating and controlling a VLC control in AutoIT | Google Maps UDF - Creating and controlling Google Maps (inc. GE) in AutoIT | SAPIListBox (Speech Recognition) UDF - Speech Recognition via the Microsoft Speech (SAPI) ListBox | eBay UDF - Automate eBay using the eBay API | ChildProc (Parallel Processing) UDF - Parallel processing functions for AutoIT | HyperCam (Screen Recording) UDF - Automate the HyperCam screen recorder | Twitter UDF - Automate Twitter using OAuth and the Twitter API | cURL UDF - a UDF for transferring data with URL syntax

See my other Tools:

Rapid Menu Writer - Add menus to DVDs in seconds | TV Player - Automates the process of playing videos on an external TV / Monitor | Rapid Video Converter - A tool for resizing and reformatting videos | [topic130531]Rapid DVD Creator - Convert videos to DVD fast and for free | ZapPF - A tool for killing processes and recycling files | Sean's eBay Bargain Hunter - Find last minute bargains in eBay using AutoIT | Sean's GUI Inspector - A scripting tool for querying GUIs | TransLink Journey Planner with maps - Incorporating Google Maps into an Australian Journey Planner | Automate Qt and QWidgets | Brisbane City Council Event Viewer - See what's going on in Brisbane, Australia

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...