• Login
Community
  • Login

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

Scheduled Pinned Locked Moved Notepad++ & Plugin Development
sql csv
19 Posts 6 Posters 26.4k Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J
    joakim wennergren
    last edited by May 10, 2017, 4:49 AM

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

    1 Reply Last reply Reply Quote 0
    • O
      OldskoolOrion
      last edited by May 19, 2017, 2:11 AM

      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

      1 Reply Last reply Reply Quote 0
      • J
        joakim wennergren
        last edited by Aug 10, 2017, 3:43 PM

        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
        1 Reply Last reply Reply Quote 0
        • D
          dinkumoil
          last edited by Aug 11, 2017, 1:02 PM

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

          1 Reply Last reply Reply Quote 0
          • J
            joakim wennergren
            last edited by Aug 17, 2017, 11:55 AM

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

            1 Reply Last reply Reply Quote 0
            • D
              dinkumoil
              last edited by dinkumoil Aug 17, 2017, 5:48 PM Aug 17, 2017, 5:48 PM

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

              1 Reply Last reply Reply Quote 0
              • D
                dinkumoil
                last edited by Nov 30, 2017, 8:16 PM

                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

                1 Reply Last reply Reply Quote 0
                • J
                  joakim wennergren
                  last edited by Nov 30, 2017, 10:00 PM

                  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!

                  1 Reply Last reply Reply Quote 0
                  • J
                    joakim wennergren
                    last edited by Nov 30, 2017, 11:09 PM

                    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

                    1 Reply Last reply Reply Quote 0
                    • D
                      dinkumoil
                      last edited by Dec 1, 2017, 1:05 AM

                      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?

                      1 Reply Last reply Reply Quote 0
                      • C
                        cipher-1024
                        last edited by Dec 1, 2017, 3:49 AM

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

                        1 Reply Last reply Reply Quote 0
                        • D
                          dinkumoil
                          last edited by Dec 1, 2017, 10:54 AM

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

                          1 Reply Last reply Reply Quote 0
                          • J
                            joakim wennergren
                            last edited by Dec 1, 2017, 4:31 PM

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

                            1 Reply Last reply Reply Quote 0
                            • First post
                              Last post
                            The Community of users of the Notepad++ text editor.
                            Powered by NodeBB | Contributors