[New Plugin] CSV Query v1.0.1 - Use SQL queries against CSV files
CSV Query is a plugin that parses CSV files and stores them in a SQLite database in RAM, allowing you to run SQL queries against it.
You can parse several files and do joins etc too. It’s a full SQLite implementation included.
Separators etc. is detected automatically, no need to configure each import. Column types not so much, but SQLite doesn’t really care anyway.
I guess this is mostly for DBA’s or similar like myself. Hope anyone can get some use out of it!
Download x86 or x64 builds here:
Issues can be reported at github, here:
Ah! An opportunity for an improvement! :P
It should work with your setup.
You need to create the target database yourself (or use an existing one). Let’s say you have a database called “mydb”.
In settings, set StorageProvider=MSSQL and Database=mydb
The next time you click “Read File” it should create a new table in the database mydb called “T1” (i.e. mydb.dbo.T1). It should contain all the data from the CSV.
Obviously it needs some work in the usability departement. ;)
Thanks for your reply.
It still doesn’t work. I always get the error message “Incorrect syntax near the keyword ‘IF’.” The message box with this message has the title “Microsoft .NET Framework”.
If I set “Storage Provider=SQLite” and “Database=:memory:” your plugin is able to parse the CSV file loaded in Notepad++, a datagrid with the file’s content is shown in the query window.
Today I downloaded and tested the new version of your plugin (v1.2.0). It still doesn’t work. With SQL Server Profiler I was able to figure out that the plugin issues the command
DROP TABLE IF EXISTS T1
when the “Read File” button is clicked.
This command is supported from SQL Server 2016 upwards. I use SQL Server 2014 SP2 Standard Edition x64 - it can not work.
An equivalent command which works also on older versions of SQL Server would be:
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'T1' AND TABLE_SCHEMA = 'dbo') drop table dbo.T1
Ah. Yeah, I tent to use CTP’s on my local machines :P
I’ll change it to something more backwards compatible
Thank’s for the info!
I put up a bugfix release on github. Don’t have time to do the plugin manager tango today, if you want to try here’s the release:
Thank you for the bug fix. It’s getting better! :)
Now the plugin creates the table. But I was not able to tell it how to name the table’s columns.
Let’s assume we have the following CSV file:
ArtId;ArtDesc;ArtPrice 0;"Article1";5.20 € 1;"Article2";8.30 € 2;"Article3";7.80 €
The resulting table has three columns named
Col2. The content of the first line (
ArtPrice) is part of the table’s content.
If the CSV file is the following
0;"Article1";5.20 € 1;"Article2";8.30 € 2;"Article3";7.80 €
the table’s columns are named
520. The table gets filled with the content of line #2 (Article2) and higher, i.e. the plugin interprets the first line as column names. Why is this not working in the first case?
@dinkumoil, I tried your first example and encapsulated the column names in quotes and they turned into column names. I tried your second example and my column names were col0 - col1, not the first row of data like you said. For my 32bit NPP, it seems like if the first row is all encapsulated, it becomes the column names. Otherwise you get generic col0, col1…
@joakim-wennergren, I’m curious if you determine data types in the plugin, or does sqlite make it’s own guess? How does it know what type to make a column when it imports the csv into SQL Server?
I’m curious because this works (even with the euro sign in the data):
SELECT sum(artPrice) FROM THIS
But summing the “ArtDesc” column returns 0.0. I would think it would return 6 if it’s just yanking the numbers out of the data.
Back in the day the MS csv text driver gave me all kinds of trouble with zip codes.
"No MS it is text! Text I say!
“No, it is numbers, I shall help you and remove the leading zeros from the east coast and any Canada or UK postals will be unrecognizable or cause an error. You’re welcome”
<shakes fist at helpful MS>
Unrelated, but I found out you can concatenate with SQLite using || instead of +.
I can not confirm your experience, quoting (with double and also with single quotes) the desired column names doesn’t improve anything. Maybe it depends on the DBMS, which one (exactly) do you use?
Whenn quoting the whole first line I get (as expected) an error:
The input array is longer than the number of columns of this table.
When checking the things above this morning I got every times the following error:
Error when executing an action: There is already an object named 'this' in the database.
This message box appears although the plugin was able to create the table in SQL Server.
Yeah, the header detection needs improvement.
The goal with CsvQuery has always been to detect everything automatically, usually (e.g. Excel and SQL Server) when reading CSV you always have to enter separator char, “has header row” etc.
But this detection can be tricky.
It works by reading 20 lines or so, and try to guess the type of each column.
If the first row had only strings, but all other rows had at least one (common) number column, it assumes the file has a header row. Yeah, not exactly foolproof. There is a reason the only issues in github right now is Better detect header row and Add “has header row” to Manual Parse Settings (so you can force it when it guess wrong) :P
And yes, the MSSQL code use a different header detector right now, since I’m writing a generic one both can use, but the SQLite code still use the old detection.
Regarding the “already an object named ‘this’” error - yeah, bug. SQLite is much more forgiving than MSSQL :)
I’m curious if you determine data types in the plugin, or does sqlite make it’s own guess? How does it know what type to make a column when it imports the csv into SQL Server?
In MSSQL there is currently no (working) type detection. SQLite doesn’t have column types, everything is strings. So “Sum()” etc is done by type converting on the fly. Apparently SQLite can handle euro :)