CSV UDF - a UDF for manipulating CSV files

[seangriffin]

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]

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

[seangriffin]

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

[JonF]

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

??

[JonF]

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.

[queensoft]

How to specify different delimiter?