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

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