[New Plugin] CSV Query v1.0.1 - Use SQL queries against CSV files

joakim wennergren

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:
https://github.com/jokedst/CsvQuery/releases/tag/v1.0.1

Issues can be reported at github, here:
https://github.com/jokedst/CsvQuery/issues

KySoto

That is freaking cool, ESPECIALLY the fact that it runs on the x64 build. (dba/programmer type here)

dinkumoil

You are in contest with this plugin:
http://www.scout-soft.com/sql/

It’s 32 bit only.
Already available via plugin manager.

Ethan Piliavin

Great work and nice UI. I am looking forward to seeing this in plugin manager and some additional features:

Export Query Results to CSV
Save common queries for reuse, or at least store last X queries for reuse/edit
convert delimiter and string delimiter

just amazing! thank you!

joakim wennergren

Thanks! :)

It’s available in the x64 Plugin Manager, but I’ve not gotten any response from the x86 Plugin Manager register tool yet. @bruderstein can perhaps approve my user (jokedst) manually?

I know about the “other” SQL query plugin, but my use case is mostly “exploratory error finding” and it’s not really optimized for many quick queries.

I must admit the idea to export CSV never occured to me :P
To me they are mostly a pain in the *** that should be imported into a database as soon as possible :D
What would be best UI there? Right-click the grid and choose “export to new file”?
Or magic table names, like “SELECT * INTO new FROM THIS”?

– Save common queries for reuse, or at least store last X queries for reuse/edit
It does autocomplete the query from the last queries entered, but it’s not saved between restarts of N++.

Oh, it has some half-assed support for fixed-width files too. Perhaps should document that somewhere…

Ethan Piliavin

FYI, I just found this amazing tool: https://sqlnotebook.com/

Anyway, to answer your questions:

I must admit the idea to export CSV never occured to me :P
To me they are mostly a pain in the *** that should be imported into a database as soon as possible :D

True!! :) But sometimes we want to do some quick work on data that is being imported into some kind of database (what else is CSV used for) and needs to stay in CSV format… It will be very helpful.

What would be best UI there? Right-click the grid and choose “export to new file”?
Or magic table names, like “SELECT * INTO new FROM THIS”?

IMHO, I think “export to new file” is the right way.

– Save common queries for reuse, or at least store last X queries for reuse/edit
It does autocomplete the query from the last queries entered, but it’s not saved between restarts of N++.

Saved dropdown between restarts would be useful… in case we want to do something again we did a few months ago, or computer crashes, etc.

joakim wennergren

FYI, I just found this amazing tool: https://sqlnotebook.com/

Wow! That a really cool program! Thanks for the tip!

I created issues for your suggestions, but like I guess most people here this is a hobby project so no promises ;)

OldskoolOrion

Love it already ! :-) exporting would be sweet for sure… I don’t mind which format… sql format, json, csv… all good :-) since u say it’s an hobby I now expect all formats, since I’d love to see you spend more time on hobbies! :P

joakim wennergren

Alraight, v1.1 is available in plugin manager (x86 and x84).

New features:

Export to CSV
Stores autocomplete between restarts
You can switch backend from SQLite to SQL Server (only localhost and trusted connection) -> good for importing CSV into SQL Server

dinkumoil

@joakim-wennergren

Could you please explain this “switch backend from SQLite to SQL Server” feature more detailed (what can one do with it and how to use)?

Sounds interesting but I was not able to do anything with it, only got error messages (I’m sure that happened because I did something inappropriate).

On my Windows 7 (64 Bit) machine a SQL Server 2014 Standard Edition SP2 (64 Bit) is installed as default instance and my Windows user account is listed in SSMS under “Security\Logins”.

joakim wennergren

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. ;)

dinkumoil

@joakim-wennergren

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.

dinkumoil

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

joakim wennergren

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!

joakim wennergren

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:

https://github.com/jokedst/CsvQuery/releases/tag/v1.2.1

dinkumoil

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 Col0 to Col2. The content of the first line (ArtId to 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 0, Article1 and 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?

cipher-1024

@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 +.

dinkumoil

@cipher-1024

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.

@joakim-wennergren
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.

joakim wennergren

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 :)