Community
    • Login

    [New Plugin] CSV Lint

    Scheduled Pinned Locked Moved Notepad++ & Plugin Development
    81 Posts 25 Posters 71.9k 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.
    • Bas de ReuverB
      Bas de Reuver @_ C64 CUSTOMS _
      last edited by

      @_-c64-customs-_ Also, there is a small bug at the moment which was already mentioned, it doesn’t vertically align the column headers (the first line) correctly. This will be fixed in the next update.

      _ C64 CUSTOMS __ 1 Reply Last reply Reply Quote 1
      • _ C64 CUSTOMS __
        _ C64 CUSTOMS _ @Bas de Reuver
        last edited by

        @bas-de-reuver Great, thank you very much for the info and thank you very much for the plugin at all, forgot to say this in my first post! Great work!

        1 Reply Last reply Reply Quote 2
        • _ C64 CUSTOMS __
          _ C64 CUSTOMS _ @Bas de Reuver
          last edited by

          @bas-de-reuver It looks like there is another small bug, not all lines are aligned equally:
          c1ca78e9-941b-48b1-9e6b-a6b742762453-image.png

          PeterJonesP 1 Reply Last reply Reply Quote 1
          • PeterJonesP
            PeterJones @_ C64 CUSTOMS _
            last edited by PeterJones

            @_-c64-customs-_

            My guess that the problem isn’t with the plugin not aligning things. The problem is that you don’t have your encoding correct, so Notepad++ is displaying the n-byte sequence for unicode characters as multiple characters instead of properly displaying the single character represented by those multiple bytes. You might try Settings > Preferences > New Document > Encoding > UTF-8: Apply to opened ANSI files

            860006b9-dfe7-4738-b6bb-cd29ba5c5150-image.png

            … which should make Notepad++ use UTF-8 instead of mis-reading that as an Win-1252 file even though it should be UTF-8.

            –
            Addendum: specifically, ä are the Win-1252 interpretation of the two bytes at 0xC3 (decimal 195) and 0xA4 (decimal 164). The sequence 0xC3 0xA4 is the two-byte UTF-8 sequence for ä. Once you load a file and it’s misinterpreted the UTF-8 as Win-1252, the easiest way to fix things is to change from Encoding > ANSI to Encoding > UTF-8 (use the top half of the menu, not the bottom “convert” half, because you don’t want to convert the file to a different byte sequence, you just want to change how Notepad++ interprets the bytes it’s already read)
            47417bab-6594-410f-9e1e-5fd31d46c76a-image.png

            _ C64 CUSTOMS __ Bas de ReuverB 2 Replies Last reply Reply Quote 3
            • _ C64 CUSTOMS __
              _ C64 CUSTOMS _ @PeterJones
              last edited by

              @peterjones Ah, great, thanks a lot. I have set the preferences as shown above by you but it somehow hasn’t worked but your hint about changing the encoding afterwards has worked very well!

              1 Reply Last reply Reply Quote 2
              • Bas de ReuverB
                Bas de Reuver @PeterJones
                last edited by

                @peterjones That is indeed the cause of the problem, thanks for the thorough answer👍

                It has to do with ANSI<->UTF8 conversion, and the letters with diacritics (à é ü etc.) are incorrectly split into two characters, “Bünder Voll” becomes “Bünder Voll”.

                When the CSVLint plug-in reformats the csv file, I use a StringBuilder to create the new reformatted file content:

                StringBuilder datanew = new StringBuilder();
                
                // .. do the reformat
                
                // update text in editor
                scintillaGateway.SetText(datanew.ToString());
                

                Right up until the line SetText(datanew.ToString()) the StringBuilder contains the correct “Bünder Voll” etc. values. So I suspect the problem is in scintillaGateway.SetText(?).

                It’s good that there is a work-around, but I don’t know how to fix this properly.

                PeterJonesP rdipardoR 2 Replies Last reply Reply Quote 0
                • PeterJonesP
                  PeterJones @Bas de Reuver
                  last edited by

                  @bas-de-reuver ,

                  I cannot give you the answer, because I don’t know the StringBuilder or scintillaGateway requirements. But my guess is that .ToString() is outputting the data in a different encoding than .SetText() wants coming in, hence the corruption of the encoding. Maybe look into the APIs of both, and see if either come with an option to change the encoding. Or if they don’t have that, then maybe you need some translator in between: scintillaGateway.SetText(translate(datanew.ToString()))

                  Sorry I cannot be more specific than that.

                  1 Reply Last reply Reply Quote 2
                  • rdipardoR
                    rdipardo @Bas de Reuver
                    last edited by

                    @bas-de-reuver,

                    Right up until the line SetText(datanew.ToString()) the StringBuilder contains the correct “Bünder Voll” etc. values.

                    That’s because the API detects the file encoding for you:

                      /// <summary>
                      /// Reads the whole document as a text stream, trying to use the right encoding
                      /// </summary>
                      public static StreamReader StreamAllText()
                      {
                        var doc = PluginBase.CurrentScintillaGateway;
                        var codepage = doc.GetCodePage();
                        var encoding = codepage == (int)SciMsg.SC_CP_UTF8 ? Encoding.UTF8 : Encoding.Default;
                        return new StreamReader(StreamAllRawText(), encoding);
                      }
                    

                    Problem is, a StringBuilder is just a simple utility with no encoding property that you can set, so the text returned by ToString() will be encoded in the system’s default (usually single-byte) code page.

                    Creating a StreamWriter with the StreamWriter(Stream, Encoding) overload would be more useful. The second parameter could be set by calling scintillaGateway.GetCodePage() and choosing an appropriate System.Text.Encoding based on the return value (as in the API method shown above). Scintilla doesn’t declare unique constants for every possible encoding; SC_CP_UTF8 really stands for “Unicode,” i.e., any multi-byte encoding.

                    If want to keep the simplicity of StringBuilder, you could always reduce the reformatted text to bytes, encode each one, then recompose them into a string, like this:

                                  StringBuilder datanew = new StringBuilder();
                    
                                  // ... do the reformat
                    
                                 /// try to match the file encoding of the open buffer
                                 /// <seealso cref="CsvQuery.PluginInfrastructure.ScintillaStreams.StreamAllText"/>
                                 Encoding docEncoding =
                                     scintillaGateway.GetCodePage() == (int)SciMsg.SC_CP_UTF8
                                     ? Encoding.UTF8
                                     : Encoding.Default;
                    
                                 // update text in editor
                                 var byteBuf = new char[datanew.Length];
                                 datanew.CopyTo(0, byteBuf, 0, datanew.Length);
                                 var dataBytes = docEncoding.GetBytes(byteBuf);
                                 scintillaGateway.SetText(docEncoding.GetString(dataBytes));
                    

                    Note The fallback choice of System.Text.Encoding.Default is just for illustration. It’s not recommended in practice on .NET Framework. Besides, every character in the ASCII code page fits inside the CLR Char type (which is always UTF-16).

                    1 Reply Last reply Reply Quote 5
                    • Alan KilbornA
                      Alan Kilborn @Bas de Reuver
                      last edited by

                      @bas-de-reuver said in [New Plugin] CSV Lint:

                      That’s why I’ve created a video to show how you can use this plug-in to validate data, reformat datetime values, split column functions

                      I got around to watching the video. Very nice intro to the plugin!

                      1 Reply Last reply Reply Quote 3
                      • Lycan ThropeL
                        Lycan Thrope @Bas de Reuver
                        last edited by Lycan Thrope

                        @bas-de-reuver ,

                        I had watched your video earlier, but being involved elsewhere with my developing UDL and associated files needing to be done, I didn’t get to really appreciate what it was offering. However, now that the language is mostly done, for now, I started going back to a project of mine that has been “slow-rolling” and started working on it. One of the things that I was trying to do was break down what were fledgling attempts at a quick database that was huge. The data was all needed, I just didn’t take the time to break them into smaller usable entities while I was making a quick app for data entry, viewing, searching, etc.

                        I needed to clean up, and I was able to separate in dBASE some of the table information, in this case, customers (actually shippers and receivers but am combining their information under just customers) and I needed to clean up and split a field. I could have probably done it in my environment, but decided to take the time to see if I could use your plug-in to do some of the work and simplify the cleanup. It worked beautifully, and although I could accomplish it by not converting it to CSV, it was so much simpler just to convert the data and split and clean it up via your plugin.

                        I just wanted to thank you for developing this plugin, and making the video, that, although I didn’t understand all of the capabilities you were mentioning about it at the time, I figured it couldn’t hurt to play with it a little, and I’m very happy I did. Thanks for doing the plugin and video. Keep up the good work. :)

                        Bas de ReuverB 1 Reply Last reply Reply Quote 6
                        • Bas de ReuverB
                          Bas de Reuver @Lycan Thrope
                          last edited by

                          @lycan-thrope said in [New Plugin] CSV Lint:

                          It worked beautifully

                          Cool, that is also the goal for this plugin; save time by making the inspecting and cleaning of data easier. So thanks 😀 that’s nice to hear you found it useful.

                          1 Reply Last reply Reply Quote 2
                          • TanquenT
                            Tanquen
                            last edited by

                            I’m not able to get this plugin to work. I need to make sure it’s not adding any text/data. I just want to make the CSV data easier to read.
                            My CSV has a large number of columns and different headers every few rows. It defaults to FixedLength but after changing to CSVDelimited it just adds the text “XML” at the top and nothing changes.

                            Format=FixedLength
                            ColNameHeader=False
                            Col1=XML Text Width 9999

                            Format=CSVDelimited
                            ColNameHeader=False
                            Col1=XML Text Width 9999

                            Bas de ReuverB 1 Reply Last reply Reply Quote 1
                            • T SwitzerT
                              T Switzer
                              last edited by

                              any update planned to update CSV Lint to work with current version of notepad ++

                              Lycan ThropeL Bas de ReuverB 2 Replies Last reply Reply Quote 1
                              • Lycan ThropeL
                                Lycan Thrope @T Switzer
                                last edited by

                                @t-switzer ,
                                There already is, but since you haven’t posted which version of NPP you’re using, the assumption is that it is the latest version, and yes, there is an update for it. At present, you’ll need to delete the current version in the plugin folders, or it won’t allow the new NPP to start. Then after you get it started, you can install the newest plugin via the Plugin manager, or go to this site and download it yourself for a self install: CSVList Github page

                                1 Reply Last reply Reply Quote 2
                                • Bas de ReuverB
                                  Bas de Reuver @Tanquen
                                  last edited by

                                  @Tanquen said in [New Plugin] CSV Lint:

                                  My CSV has a large number of columns and different headers every few rows. It defaults to FixedLength but after changing to CSVDelimited it just adds the text “XML” at the top and nothing changes.

                                  Thanks for mentioning your issue. It sounds like the plug-in can’t recognise this specific data file. I suspect the file includes many < or > characters as well as many , or ; characters or something like that. This can “confuse” the autodetect function so to speak, meaning it can’t determine which is the correct separator character, so it doesn’t interpret the data and columns correctly.

                                  Is it possible to send the data file to my e-mail address (see About dialog)? If it contains privacy sensitive data or is too large, then maybe edit the file and just include a few lines of data to reproduce this issue?

                                  Btw someone metioned a similar issue so in a future update I want to add a where you can (optionally) manually specify the separator character.

                                  In the mean time you can somehow manually construct the meta data, like below.

                                  Format=CSVDelimited
                                  ColNameHeader=False
                                  Col1=Field1 Text Width 50
                                  Col2=Field2 Text Width 50
                                  Col3=Field3 Text Width 50
                                  Col4=Field4 Text Width 50
                                  etc.

                                  Or alternatively, first try to delete the rows (if possible) that are causing trouble, so keep only a few rows with representative data, and then click Refresh from data, and then apply that resulting metadata to the complete file with all the rows.

                                  1 Reply Last reply Reply Quote 3
                                  • Bas de ReuverB
                                    Bas de Reuver @T Switzer
                                    last edited by

                                    @T-Switzer said in [New Plugin] CSV Lint:

                                    any update planned to update CSV Lint to work with current version of notepad ++

                                    Like @Lycan-Thrope mentioned, there is a new CSV Lint v0.4.5.2 which you can manually download from the github page. That version will be included automatically in the Plugin Admin in the upcoming Notepad++ v8.4.3.

                                    It looks like the compatibility issues with the new Lexer v5 are solved now 🤞 and I want to wait and see before continuing and adding too many other features to the plug-in.

                                    1 Reply Last reply Reply Quote 3
                                    • PeterJonesP PeterJones referenced this topic on
                                    • Eric YangE
                                      Eric Yang
                                      last edited by

                                      Is there a way not to change the background color? Notepad++'s default theme has white background so CSV Lint looks OK after syntax coloring. But I normally use Solarized theme (dark bg) and CSV Lint changes all the text to white background.

                                      Bas de ReuverB 1 Reply Last reply Reply Quote 1
                                      • Bas de ReuverB Bas de Reuver referenced this topic on
                                      • Bas de ReuverB
                                        Bas de Reuver @Eric Yang
                                        last edited by

                                        @Eric-Yang sorry for the late answer, I had missing this post.

                                        You can go to the menu Plugins -> CSV Lint -> Settings there is a button “Colors” to select from 4 pre-defined colorsets for the column syntax highlighting, see color preview here.

                                        If you use a dark mode/dark background theme, then it’s best to select either Dark mode (pastel) or Dark mode (neon). Btw you need to close and restart Notepad++ before the new colors are visible.

                                        Bas de ReuverB 1 Reply Last reply Reply Quote 3
                                        • Bas de ReuverB
                                          Bas de Reuver @Bas de Reuver
                                          last edited by

                                          With the Notepad++ update to v8.4.7 yesterday, the new Plugin Admin now also contains an update for CSVLint plug-in from v0.4.5.4 to v0.4.6.2. I hope the plugin will save everyone some time when working with csv files, let me know what you think.

                                          It now also has a sort function and improved compatibility with Windows 11 unicode UTF8 setting. Also the default syntax highlighting now has 12 colors instead of 8, with a bit more pleasing colors imho.

                                          csvlint_sort.png

                                          See below for complete list of plugin updates and bugfixes since the last Notepad++ version:

                                          v0.4.6

                                          • Improved compatibility with Windows unicode UTF8 setting
                                          • Sort data, new option to sort on column
                                          • Split column, add options pad character and search and replace
                                          • Split column, remove options when contains and decode multiple value
                                          • Default color sets now have 12 colors instead of 8 (less repeats) + optimal color contrast
                                          • Settings dialog, color set preview icons
                                          • Autodetect improved, skip empty lines + clear message when nothing detected
                                          • Metadata for fixed width, also output absolute positions

                                          v0.4.6.1

                                          • Apply quotes bugfix, also values that contain CrLf character
                                          • Sort data and split column, use quotes correctly
                                          • Sort data and split column, also support fixed width

                                          v0.4.6.2

                                          • Detect fixed width, allow manual column positions
                                          • Button to toggle syntax highlighting
                                          • Allow user to change font in docked window textboxes
                                          datatraveller1D 1 Reply Last reply Reply Quote 7
                                          • datatraveller1D
                                            datatraveller1 @Bas de Reuver
                                            last edited by

                                            HI @Bas-de-Reuver,
                                            Thank you for the plugin. The plugin is nice, but sometimes I want to switch to the original Notepad++ view for a .csv file. Is there an option to turn off the CSV Lint view?

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