Repository: netbox-community/netbox
Pull request: Adds csv dialect detection to bulk import view
<!--
    Thank you for your interest in contributing to NetBox! Please note that
    our contribution policy requires that a feature request or bug report be
    approved and assigned prior to opening a pull request. This helps avoid
    waste time and effort on a proposed change that we might not be able to
    accept.

    IF YOUR PULL REQUEST DOES NOT REFERENCE AN ISSUE WHICH HAS BEEN ASSIGNED
    TO YOU, IT WILL BE CLOSED AUTOMATICALLY.

    Please specify your assigned issue number on the line below.
-->
### Fixes: #13239

<!--
    Please include a summary of the proposed changes below.
-->


Problem statement:
CSV import: add support for alternate separators
### NetBox version

v3.5.3

### Feature type

Change to existing functionality

### Proposed functionality

- Currently the CSV import only supports `,` as field seperator – it would really help if it would support `;` and `\t` aka `	`, too.

### Use case

* copy&paste tables from spreadsheet software to the import text-area of the web UI, 
* import CSV files create with Numbers, etc.

### Database changes

_No response_

### External dependencies

_No response_

Hints from issue discussion:
This would help a lot. `;` is used by Excel in many European countries. If you save in csv format in France or Sweden, Excel will use `;`. 
From your description, I just want to point out that `\t` is used for tsv files. Although they are not so different but I'm not sure if that'll be in scope for the change here

Commit message: adds csv delimiter tests #13239

Patch to review:
diff --git a/netbox/utilities/forms/bulk_import.py b/netbox/utilities/forms/bulk_import.py
index 6bdfd566261..efdef0c7d55 100644
--- a/netbox/utilities/forms/bulk_import.py
+++ b/netbox/utilities/forms/bulk_import.py
@@ -90,8 +90,17 @@ def _clean_csv(self, data):
         """
         Clean CSV-formatted data. The first row will be treated as column headers.
         """
+
+        # Determine the CSV dialect
+        try:
+            # This uses a rough heuristic to detect the CSV dialect. If the data is malformed, we'll fall back to
+            # the default Excel dialect.
+            dialect = csv.Sniffer().sniff(data.strip(), delimiters=',; ')
+        except csv.Error:
+            dialect = csv.excel
+
         stream = StringIO(data.strip())
-        reader = csv.reader(stream)
+        reader = csv.reader(stream, dialect=dialect)
         headers, records = parse_csv(reader)
 
         # Set CSV headers for reference by the model form
diff --git a/netbox/utilities/tests/test_forms.py b/netbox/utilities/tests/test_forms.py
index 79ba3f4d8bd..a6a810c0a7e 100644
--- a/netbox/utilities/tests/test_forms.py
+++ b/netbox/utilities/tests/test_forms.py
@@ -331,3 +331,36 @@ def test_format_detection(self):
             form._detect_format('')
         with self.assertRaises(forms.ValidationError):
             form._detect_format('?')
+
+    def test_csv_delimiters(self):
+        form = BulkImportForm()
+
+        data = (
+            "a,b,c\n"
+            "1,2,3\n"
+            "4,5,6\n"
+        )
+        self.assertEqual(form._clean_csv(data), [
+            {'a': '1', 'b': '2', 'c': '3'},
+            {'a': '4', 'b': '5', 'c': '6'},
+        ])
+
+        data = (
+            "a;b;c\n"
+            "1;2;3\n"
+            "4;5;6\n"
+        )
+        self.assertEqual(form._clean_csv(data), [
+            {'a': '1', 'b': '2', 'c': '3'},
+            {'a': '4', 'b': '5', 'c': '6'},
+        ])
+
+        data = (
+            "a b c\n"
+            "1 2 3\n"
+            "4 5 6\n"
+        )
+        self.assertEqual(form._clean_csv(data), [
+            {'a': '1', 'b': '2', 'c': '3'},
+            {'a': '4', 'b': '5', 'c': '6'},
+        ])


Code review comments:
diff_hunk: @@ -90,8 +90,17 @@ def _clean_csv(self, data):
         """
         Clean CSV-formatted data. The first row will be treated as column headers.
         """
+
+        # Determine the CSV dialect
+        try:
+            # This uses a rough heuristic to detect the CSV dialect. If the data is malformed, we'll fall back to
+            # the default Excel dialect.
+            dialect = csv.Sniffer().sniff(data.strip(), delimiters=',; ')
path: netbox/utilities/forms/bulk_import.py
text: @user1:
Should `delimiters` include a space? Should this be a tab (`\t`) instead?

@author:
I tried but the internals for sniff simply iterate over the single char like `[key for key in delimiters]` due to which it's not really possible to pass `\t`. I tried using a list of strings but my IDE was telling me the expected type is `str` or `None` only,

@user2:
Maybe I'm misunderstanding, but seems to work here?

```
>>> data = """test1\ttest2\ttest3
... dss\tdsds\tdsds"""
>>> print(data)
test1   test2   test3
dss     dsds    dsds
>>> dialect = csv.Sniffer().sniff(data.strip(), delimiters=',;\t')
>>> dialect.delimiter
'\t'
```

Also:

```
>>> [key for key in ",;\t"]
[',', ';', '\t']
```

@author:
This is odd, when I tried from the Import UI, the code blew up because it could not detect the delimiter

@author:
It's my fault I guess to assume the issue originated at the sniff function, rather it was with header checks

![image](https://github.com/netbox-community/netbox/assets/5083532/9de59190-eb8e-4fed-96cb-01e0ffe64350)


@user3:
This screenshot makes no sense, you're trying to put \t into the web form? That's not the same as a tab character.

Try creating a text file in Notepad with tabs seperating the fields and copy-paste this into the form instead.

diff_hunk: @@ -90,8 +90,17 @@ def _clean_csv(self, data):
         """
         Clean CSV-formatted data. The first row will be treated as column headers.
         """
+
+        # Determine the CSV dialect
+        try:
+            # This uses a rough heuristic to detect the CSV dialect. If the data is malformed, we'll fall back to
+            # the default Excel dialect.
+            dialect = csv.Sniffer().sniff(data.strip(), delimiters=',; ')
path: netbox/utilities/forms/bulk_import.py
text: @user1:
It would probably be a good idea to provide the user a mechanism for overriding this as well, like what we do for bulk import formats (CSV/JSON/auto).

@author:
If you want specific selectors in the form we can get rid of this logic and simply pass the `delimiter` to the reader. Using sniff seemed more manageable in case we decide to include more delimiters or even provide ways for the user to set this in the configuration.

@user1:
We should retain the automatic detection as the default behavior, but also offer the option for the user to override it.

@author:
I have made some changes to the code, see if that fits what you were articulating. It may need more cleanup though.