Jump to content

CSV UDF - a UDF for manipulating CSV files


seangriffin
 Share

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
Link to comment
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
Link to comment
Share on other sites

  • 1 year later...

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

??

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 6 months later...

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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...