seangriffin 34 Posted February 25, 2019 (edited) 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 1 mLipok reacted to this 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 syntaxSee 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
Skeletor 70 Posted February 25, 2019 This is cool. I see you need SQLite for this.. what if the user has SQL Server ? Kind RegardsSkeletor "I need coffee to turn me back to a human" Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen Share this post Link to post Share on other sites
seangriffin 34 Posted February 25, 2019 (edited) 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 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 syntaxSee 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
JonF 1 Posted August 5, 2020 Not working for me. Got it from GitHub today, all the files including the SQLite ones. The example worked except "Get all CSV records with an 'Assigned to' value of 'DELI_SIT_S0004' and display the result ..." didn't display anything and none of the file saves saved (probably Windows). But: _CSV_Initialise() Local $item_csv_handle = _CSV_Open("F:\jonf\Documents\Creationism\Dave Hawkins\AF Dave's UPDATED Creator God Hypothesis\Pages.csv") Local $csv_result = _CSV_GetRecordArray($item_csv_handle, "", False) _CSV_Cleanup() fails: >"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "F:\jonf\Documents\Creationism\Dave Hawkins\AF Dave's UPDATED Creator God Hypothesis\Fix_Pages.au3" /UserParams +>19:17:14 Starting AutoIt3Wrapper (19.1127.1402.0} from:SciTE.exe (4.2.0.0) Keyboard:00000409 OS:WIN_10/ CPU:X64 OS:X64 Environment(Language:0409) CodePage:0 utf8.auto.check:4 +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\jonf\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\jonf\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.14.5) from:C:\Program Files (x86)\AutoIt3 input:F:\jonf\Documents\Creationism\Dave Hawkins\AF Dave's UPDATED Creator God Hypothesis\Fix_Pages.au3 +>19:17:16 AU3Check ended.rc:0 >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "F:\jonf\Documents\Creationism\Dave Hawkins\AF Dave's UPDATED Creator God Hypothesis\Fix_Pages.au3" +>Setting Hotkeys...--> Press Ctrl+Alt+Break to Restart or Ctrl+BREAK to Stop. ! SQLite.au3 Error --> Function: _SQLite_Query --> Query: SELECT * FROM csv; --> Error: no such table: csv A typical CSV line is: http://www.antievolution.org/cgi-bin/ikonboard/ikonboard.cgi"s=5f201cdc628f4c83;act=ST;f=14;t=3131;st=3510,#118_top ?? Share this post Link to post Share on other sites
JonF 1 Posted August 6, 2020 I did some investigation. Importing the CSV is failing with result code 21. Which according to Result and Error Codes means "The SQLITE_MISUSE return code might be returned if the application uses any SQLite interface in a way that is undefined or unsupported." I added single quotes to all my CSV fields: 'http://www.antievolution.org/cgi-bin/ikonboard/ikonboard.cgi"s=5f201cdc628f4c83;act=ST;f=14;t=3131;st=3510','#118_top' but that didn't help. Share this post Link to post Share on other sites
queensoft 0 Posted Tuesday at 12:40 PM How to specify different delimiter? Share this post Link to post Share on other sites