We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Error when csv header has empty space
### Discussed in https://github.com/mindsdb/mindsdb/discussions/2343

<div type='discussions-op-text'>

<sup>Originally posted by **Ashok0** June 13, 2022</sup>
Hi,

I have a question regarding usage of imported data with MindsDB.  I registered a MindsDB account and selected "Add Data" > "Import File" and uploaded some sample CSV data to a table.   I can view the data just fine as shown in the following screencap:  https://imgur.com/a/X6mrzHt

If I attempt to select an individual column i.e. 'sensitivity' with the following statement:  **SELECT sensitivity FROM files.bedcheck**, 
I receive the following error message:  **Binder Error: Referenced column "sensitivity" not found in FROM clause! Candidate bindings: "df_table. sensitivity" LINE 1: SELECT sensitivity AS sensitivity**

Any assistance in how I can resolve this error would be greatly appreciated.  

Thank you!</div>

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `mindsdb/integrations/handlers/file_handler/file_handler.py`
Content:
```
1 from io import BytesIO, StringIO
2 import os
3 import csv
4 import json
5 import codecs
6 import traceback
7 import tempfile
8 from urllib.parse import urlparse
9 
10 import requests
11 import pandas as pd
12 
13 from mindsdb_sql import parse_sql
14 from mindsdb_sql.parser.ast.base import ASTNode
15 from mindsdb_sql.parser.ast import DropTables, Select
16 
17 from mindsdb.api.mysql.mysql_proxy.utilities.sql import query_df
18 from mindsdb.integrations.libs.base_handler import DatabaseHandler
19 from mindsdb.integrations.libs.response import (
20     HandlerStatusResponse as StatusResponse,
21     HandlerResponse as Response,
22     RESPONSE_TYPE
23 )
24 
25 
26 def clean_row(row):
27     n_row = []
28     for cell in row:
29         if str(cell) in ['', ' ', '  ', 'NaN', 'nan', 'NA']:
30             n_row.append(None)
31         else:
32             n_row.append(cell)
33 
34     return n_row
35 
36 
37 class FileHandler(DatabaseHandler):
38     """
39     Handler for files
40     """
41     type = 'files'
42 
43     def __init__(self, name=None, db_store=None, fs_store=None, connection_data=None, file_controller=None):
44         super().__init__(name)
45         self.parser = parse_sql
46         self.fs_store = fs_store
47         self.custom_parser = connection_data.get('custom_parser')
48         self.clean_rows = connection_data.get('clean_rows', True)
49         self.file_controller = file_controller
50 
51     def connect(self, **kwargs):
52         return
53 
54     def disconnect(self, **kwargs):
55         return
56 
57     def check_connection(self) -> StatusResponse:
58         return StatusResponse(True)
59 
60     def query(self, query: ASTNode) -> Response:
61         if type(query) == DropTables:
62             for table_identifier in query.tables:
63                 if len(table_identifier.parts) == 2 and table_identifier.parts[0] != self.name:
64                     return Response(
65                         RESPONSE_TYPE.ERROR,
66                         error_message=f"Can't delete table from database '{table_identifier.parts[0]}'"
67                     )
68                 table_name = table_identifier.parts[-1]
69                 try:
70                     self.file_controller.delete_file(table_name)
71                 except Exception as e:
72                     return Response(
73                         RESPONSE_TYPE.ERROR,
74                         error_message=f"Can't delete table '{table_name}': {e}"
75                     )
76             return Response(RESPONSE_TYPE.OK)
77         elif type(query) == Select:
78             table_name = query.from_table.parts[-1]
79             file_path = self.file_controller.get_file_path(table_name, company_id=None)
80             df, _columns = self._handle_source(file_path, self.clean_rows, self.custom_parser)
81             result_df = query_df(df, query)
82             return Response(
83                 RESPONSE_TYPE.TABLE,
84                 data_frame=result_df
85             )
86         else:
87             return Response(
88                 RESPONSE_TYPE.ERROR,
89                 error_message="Only 'select' and 'drop' queries allowed for files"
90             )
91 
92     def native_query(self, query: str) -> Response:
93         ast = self.parser(query, dialect='mindsdb')
94         return self.query(ast)
95 
96     @staticmethod
97     def _handle_source(file_path, clean_rows=True, custom_parser=None):
98         # get file data io, format and dialect
99         data, fmt, dialect = FileHandler._get_data_io(file_path)
100         data.seek(0)  # make sure we are at 0 in file pointer
101 
102         if custom_parser:
103             header, file_data = custom_parser(data, fmt)
104 
105         elif fmt == 'csv':
106             csv_reader = list(csv.reader(data, dialect))
107             header = csv_reader[0]
108             file_data = csv_reader[1:]
109 
110         elif fmt in ['xlsx', 'xls']:
111             data.seek(0)
112             df = pd.read_excel(data)
113             header = df.columns.values.tolist()
114             file_data = df.values.tolist()
115 
116         elif fmt == 'json':
117             data.seek(0)
118             json_doc = json.loads(data.read())
119             df = pd.json_normalize(json_doc, max_level=0)
120             header = df.columns.values.tolist()
121             file_data = df.values.tolist()
122 
123         else:
124             raise ValueError('Could not load file into any format, supported formats are csv, json, xls, xlsx')
125 
126         if clean_rows:
127             file_list_data = [clean_row(row) for row in file_data]
128         else:
129             file_list_data = file_data
130 
131         col_map = dict((col, col) for col in header)
132         return pd.DataFrame(file_list_data, columns=header), col_map
133 
134     @staticmethod
135     def _get_data_io(file_path):
136         """
137         This gets a file either url or local file and defiens what the format is as well as dialect
138         :param file: file path or url
139         :return: data_io, format, dialect
140         """
141 
142         ############
143         # get file as io object
144         ############
145 
146         # file_path = self._get_file_path()
147 
148         data = BytesIO()
149 
150         try:
151             with open(file_path, 'rb') as fp:
152                 data = BytesIO(fp.read())
153         except Exception as e:
154             error = 'Could not load file, possible exception : {exception}'.format(exception=e)
155             print(error)
156             raise ValueError(error)
157 
158         dialect = None
159 
160         ############
161         # check for file type
162         ############
163 
164         # try to guess if its an excel file
165         xlsx_sig = b'\x50\x4B\x05\06'
166         # xlsx_sig2 = b'\x50\x4B\x03\x04'
167         xls_sig = b'\x09\x08\x10\x00\x00\x06\x05\x00'
168 
169         # different whence, offset, size for different types
170         excel_meta = [('xls', 0, 512, 8), ('xlsx', 2, -22, 4)]
171 
172         for filename, whence, offset, size in excel_meta:
173 
174             try:
175                 data.seek(offset, whence)  # Seek to the offset.
176                 bytes = data.read(size)  # Capture the specified number of bytes.
177                 data.seek(0)
178                 codecs.getencoder('hex')(bytes)
179 
180                 if bytes == xls_sig:
181                     return data, 'xls', dialect
182                 elif bytes == xlsx_sig:
183                     return data, 'xlsx', dialect
184 
185             except Exception:
186                 data.seek(0)
187 
188         # if not excel it can be a json file or a CSV, convert from binary to stringio
189 
190         byte_str = data.read()
191         # Move it to StringIO
192         try:
193             # Handle Microsoft's BOM "special" UTF-8 encoding
194             if byte_str.startswith(codecs.BOM_UTF8):
195                 data = StringIO(byte_str.decode('utf-8-sig'))
196             else:
197                 data = StringIO(byte_str.decode('utf-8'))
198 
199         except Exception:
200             print(traceback.format_exc())
201             print('Could not load into string')
202 
203         # see if its JSON
204         buffer = data.read(100)
205         data.seek(0)
206         text = buffer.strip()
207         # analyze first n characters
208         if len(text) > 0:
209             text = text.strip()
210             # it it looks like a json, then try to parse it
211             if text.startswith('{') or text.startswith('['):
212                 try:
213                     json.loads(data.read())
214                     data.seek(0)
215                     return data, 'json', dialect
216                 except Exception:
217                     data.seek(0)
218                     return data, None, dialect
219 
220         # lets try to figure out if its a csv
221         try:
222             dialect = FileHandler._get_csv_dialect(file_path)
223             if dialect:
224                 return data, 'csv', dialect
225             return data, None, dialect
226         except Exception:
227             data.seek(0)
228             print('Could not detect format for this file')
229             print(traceback.format_exc())
230             # No file type identified
231             return data, None, dialect
232 
233     @staticmethod
234     def _get_file_path(path) -> str:
235         try:
236             is_url = urlparse(path).scheme in ('http', 'https')
237         except Exception:
238             is_url = False
239         if is_url:
240             path = FileHandler._fetch_url(path)
241         return path
242 
243     @staticmethod
244     def _get_csv_dialect(file_path) -> csv.Dialect:
245         with open(file_path, 'rt') as f:
246             try:
247                 accepted_csv_delimiters = [',', '\t', ';']
248                 dialect = csv.Sniffer().sniff(f.read(128 * 1024), delimiters=accepted_csv_delimiters)
249             except csv.Error:
250                 dialect = None
251         return dialect
252 
253     @staticmethod
254     def _fetch_url(url: str) -> str:
255         temp_dir = tempfile.mkdtemp(prefix='mindsdb_file_url_')
256         try:
257             r = requests.get(url, stream=True)
258             if r.status_code == 200:
259                 with open(os.path.join(temp_dir, 'file'), 'wb') as f:
260                     for chunk in r:
261                         f.write(chunk)
262             else:
263                 raise Exception(f'Responce status code is {r.status_code}')
264         except Exception as e:
265             print(f'Error during getting {url}')
266             print(e)
267             raise
268         return os.path.join(temp_dir, 'file')
269 
270     def get_tables(self) -> Response:
271         """
272         List all files
273         """
274         files_meta = self.file_controller.get_files()
275         data = [{
276             'TABLE_NAME': x['name'],
277             'TABLE_ROWS': x['row_count']
278         } for x in files_meta]
279         return Response(
280             RESPONSE_TYPE.TABLE,
281             data_frame=pd.DataFrame(data)
282         )
283 
284     def get_columns(self, table_name) -> Response:
285         file_meta = self.file_controller.get_file_meta(table_name)
286         result = Response(
287             RESPONSE_TYPE.TABLE,
288             data_frame=pd.DataFrame([
289                 {
290                     'Field': x,
291                     'Type': 'str'
292                 } for x in file_meta['columns']
293             ])
294         )
295         return result
296
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/mindsdb/integrations/handlers/file_handler/file_handler.py b/mindsdb/integrations/handlers/file_handler/file_handler.py
--- a/mindsdb/integrations/handlers/file_handler/file_handler.py
+++ b/mindsdb/integrations/handlers/file_handler/file_handler.py
@@ -128,6 +128,7 @@
         else:
             file_list_data = file_data
 
+        header = [x.strip() for x in header]
         col_map = dict((col, col) for col in header)
         return pd.DataFrame(file_list_data, columns=header), col_map
 
@@ -287,7 +288,7 @@
             RESPONSE_TYPE.TABLE,
             data_frame=pd.DataFrame([
                 {
-                    'Field': x,
+                    'Field': x.strip(),
                     'Type': 'str'
                 } for x in file_meta['columns']
             ])

{"golden_diff": "diff --git a/mindsdb/integrations/handlers/file_handler/file_handler.py b/mindsdb/integrations/handlers/file_handler/file_handler.py\n--- a/mindsdb/integrations/handlers/file_handler/file_handler.py\n+++ b/mindsdb/integrations/handlers/file_handler/file_handler.py\n@@ -128,6 +128,7 @@\n         else:\n             file_list_data = file_data\n \n+        header = [x.strip() for x in header]\n         col_map = dict((col, col) for col in header)\n         return pd.DataFrame(file_list_data, columns=header), col_map\n \n@@ -287,7 +288,7 @@\n             RESPONSE_TYPE.TABLE,\n             data_frame=pd.DataFrame([\n                 {\n-                    'Field': x,\n+                    'Field': x.strip(),\n                     'Type': 'str'\n                 } for x in file_meta['columns']\n             ])\n", "issue": "Error when csv header has empty space\n### Discussed in https://github.com/mindsdb/mindsdb/discussions/2343\r\n\r\n<div type='discussions-op-text'>\r\n\r\n<sup>Originally posted by **Ashok0** June 13, 2022</sup>\r\nHi,\r\n\r\nI have a question regarding usage of imported data with MindsDB.  I registered a MindsDB account and selected \"Add Data\" > \"Import File\" and uploaded some sample CSV data to a table.   I can view the data just fine as shown in the following screencap:  https://imgur.com/a/X6mrzHt\r\n\r\nIf I attempt to select an individual column i.e. 'sensitivity' with the following statement:  **SELECT sensitivity FROM files.bedcheck**, \r\nI receive the following error message:  **Binder Error: Referenced column \"sensitivity\" not found in FROM clause! Candidate bindings: \"df_table. sensitivity\" LINE 1: SELECT sensitivity AS sensitivity**\r\n\r\nAny assistance in how I can resolve this error would be greatly appreciated.  \r\n\r\nThank you!</div>\n", "before_files": [{"content": "from io import BytesIO, StringIO\nimport os\nimport csv\nimport json\nimport codecs\nimport traceback\nimport tempfile\nfrom urllib.parse import urlparse\n\nimport requests\nimport pandas as pd\n\nfrom mindsdb_sql import parse_sql\nfrom mindsdb_sql.parser.ast.base import ASTNode\nfrom mindsdb_sql.parser.ast import DropTables, Select\n\nfrom mindsdb.api.mysql.mysql_proxy.utilities.sql import query_df\nfrom mindsdb.integrations.libs.base_handler import DatabaseHandler\nfrom mindsdb.integrations.libs.response import (\n    HandlerStatusResponse as StatusResponse,\n    HandlerResponse as Response,\n    RESPONSE_TYPE\n)\n\n\ndef clean_row(row):\n    n_row = []\n    for cell in row:\n        if str(cell) in ['', ' ', '  ', 'NaN', 'nan', 'NA']:\n            n_row.append(None)\n        else:\n            n_row.append(cell)\n\n    return n_row\n\n\nclass FileHandler(DatabaseHandler):\n    \"\"\"\n    Handler for files\n    \"\"\"\n    type = 'files'\n\n    def __init__(self, name=None, db_store=None, fs_store=None, connection_data=None, file_controller=None):\n        super().__init__(name)\n        self.parser = parse_sql\n        self.fs_store = fs_store\n        self.custom_parser = connection_data.get('custom_parser')\n        self.clean_rows = connection_data.get('clean_rows', True)\n        self.file_controller = file_controller\n\n    def connect(self, **kwargs):\n        return\n\n    def disconnect(self, **kwargs):\n        return\n\n    def check_connection(self) -> StatusResponse:\n        return StatusResponse(True)\n\n    def query(self, query: ASTNode) -> Response:\n        if type(query) == DropTables:\n            for table_identifier in query.tables:\n                if len(table_identifier.parts) == 2 and table_identifier.parts[0] != self.name:\n                    return Response(\n                        RESPONSE_TYPE.ERROR,\n                        error_message=f\"Can't delete table from database '{table_identifier.parts[0]}'\"\n                    )\n                table_name = table_identifier.parts[-1]\n                try:\n                    self.file_controller.delete_file(table_name)\n                except Exception as e:\n                    return Response(\n                        RESPONSE_TYPE.ERROR,\n                        error_message=f\"Can't delete table '{table_name}': {e}\"\n                    )\n            return Response(RESPONSE_TYPE.OK)\n        elif type(query) == Select:\n            table_name = query.from_table.parts[-1]\n            file_path = self.file_controller.get_file_path(table_name, company_id=None)\n            df, _columns = self._handle_source(file_path, self.clean_rows, self.custom_parser)\n            result_df = query_df(df, query)\n            return Response(\n                RESPONSE_TYPE.TABLE,\n                data_frame=result_df\n            )\n        else:\n            return Response(\n                RESPONSE_TYPE.ERROR,\n                error_message=\"Only 'select' and 'drop' queries allowed for files\"\n            )\n\n    def native_query(self, query: str) -> Response:\n        ast = self.parser(query, dialect='mindsdb')\n        return self.query(ast)\n\n    @staticmethod\n    def _handle_source(file_path, clean_rows=True, custom_parser=None):\n        # get file data io, format and dialect\n        data, fmt, dialect = FileHandler._get_data_io(file_path)\n        data.seek(0)  # make sure we are at 0 in file pointer\n\n        if custom_parser:\n            header, file_data = custom_parser(data, fmt)\n\n        elif fmt == 'csv':\n            csv_reader = list(csv.reader(data, dialect))\n            header = csv_reader[0]\n            file_data = csv_reader[1:]\n\n        elif fmt in ['xlsx', 'xls']:\n            data.seek(0)\n            df = pd.read_excel(data)\n            header = df.columns.values.tolist()\n            file_data = df.values.tolist()\n\n        elif fmt == 'json':\n            data.seek(0)\n            json_doc = json.loads(data.read())\n            df = pd.json_normalize(json_doc, max_level=0)\n            header = df.columns.values.tolist()\n            file_data = df.values.tolist()\n\n        else:\n            raise ValueError('Could not load file into any format, supported formats are csv, json, xls, xlsx')\n\n        if clean_rows:\n            file_list_data = [clean_row(row) for row in file_data]\n        else:\n            file_list_data = file_data\n\n        col_map = dict((col, col) for col in header)\n        return pd.DataFrame(file_list_data, columns=header), col_map\n\n    @staticmethod\n    def _get_data_io(file_path):\n        \"\"\"\n        This gets a file either url or local file and defiens what the format is as well as dialect\n        :param file: file path or url\n        :return: data_io, format, dialect\n        \"\"\"\n\n        ############\n        # get file as io object\n        ############\n\n        # file_path = self._get_file_path()\n\n        data = BytesIO()\n\n        try:\n            with open(file_path, 'rb') as fp:\n                data = BytesIO(fp.read())\n        except Exception as e:\n            error = 'Could not load file, possible exception : {exception}'.format(exception=e)\n            print(error)\n            raise ValueError(error)\n\n        dialect = None\n\n        ############\n        # check for file type\n        ############\n\n        # try to guess if its an excel file\n        xlsx_sig = b'\\x50\\x4B\\x05\\06'\n        # xlsx_sig2 = b'\\x50\\x4B\\x03\\x04'\n        xls_sig = b'\\x09\\x08\\x10\\x00\\x00\\x06\\x05\\x00'\n\n        # different whence, offset, size for different types\n        excel_meta = [('xls', 0, 512, 8), ('xlsx', 2, -22, 4)]\n\n        for filename, whence, offset, size in excel_meta:\n\n            try:\n                data.seek(offset, whence)  # Seek to the offset.\n                bytes = data.read(size)  # Capture the specified number of bytes.\n                data.seek(0)\n                codecs.getencoder('hex')(bytes)\n\n                if bytes == xls_sig:\n                    return data, 'xls', dialect\n                elif bytes == xlsx_sig:\n                    return data, 'xlsx', dialect\n\n            except Exception:\n                data.seek(0)\n\n        # if not excel it can be a json file or a CSV, convert from binary to stringio\n\n        byte_str = data.read()\n        # Move it to StringIO\n        try:\n            # Handle Microsoft's BOM \"special\" UTF-8 encoding\n            if byte_str.startswith(codecs.BOM_UTF8):\n                data = StringIO(byte_str.decode('utf-8-sig'))\n            else:\n                data = StringIO(byte_str.decode('utf-8'))\n\n        except Exception:\n            print(traceback.format_exc())\n            print('Could not load into string')\n\n        # see if its JSON\n        buffer = data.read(100)\n        data.seek(0)\n        text = buffer.strip()\n        # analyze first n characters\n        if len(text) > 0:\n            text = text.strip()\n            # it it looks like a json, then try to parse it\n            if text.startswith('{') or text.startswith('['):\n                try:\n                    json.loads(data.read())\n                    data.seek(0)\n                    return data, 'json', dialect\n                except Exception:\n                    data.seek(0)\n                    return data, None, dialect\n\n        # lets try to figure out if its a csv\n        try:\n            dialect = FileHandler._get_csv_dialect(file_path)\n            if dialect:\n                return data, 'csv', dialect\n            return data, None, dialect\n        except Exception:\n            data.seek(0)\n            print('Could not detect format for this file')\n            print(traceback.format_exc())\n            # No file type identified\n            return data, None, dialect\n\n    @staticmethod\n    def _get_file_path(path) -> str:\n        try:\n            is_url = urlparse(path).scheme in ('http', 'https')\n        except Exception:\n            is_url = False\n        if is_url:\n            path = FileHandler._fetch_url(path)\n        return path\n\n    @staticmethod\n    def _get_csv_dialect(file_path) -> csv.Dialect:\n        with open(file_path, 'rt') as f:\n            try:\n                accepted_csv_delimiters = [',', '\\t', ';']\n                dialect = csv.Sniffer().sniff(f.read(128 * 1024), delimiters=accepted_csv_delimiters)\n            except csv.Error:\n                dialect = None\n        return dialect\n\n    @staticmethod\n    def _fetch_url(url: str) -> str:\n        temp_dir = tempfile.mkdtemp(prefix='mindsdb_file_url_')\n        try:\n            r = requests.get(url, stream=True)\n            if r.status_code == 200:\n                with open(os.path.join(temp_dir, 'file'), 'wb') as f:\n                    for chunk in r:\n                        f.write(chunk)\n            else:\n                raise Exception(f'Responce status code is {r.status_code}')\n        except Exception as e:\n            print(f'Error during getting {url}')\n            print(e)\n            raise\n        return os.path.join(temp_dir, 'file')\n\n    def get_tables(self) -> Response:\n        \"\"\"\n        List all files\n        \"\"\"\n        files_meta = self.file_controller.get_files()\n        data = [{\n            'TABLE_NAME': x['name'],\n            'TABLE_ROWS': x['row_count']\n        } for x in files_meta]\n        return Response(\n            RESPONSE_TYPE.TABLE,\n            data_frame=pd.DataFrame(data)\n        )\n\n    def get_columns(self, table_name) -> Response:\n        file_meta = self.file_controller.get_file_meta(table_name)\n        result = Response(\n            RESPONSE_TYPE.TABLE,\n            data_frame=pd.DataFrame([\n                {\n                    'Field': x,\n                    'Type': 'str'\n                } for x in file_meta['columns']\n            ])\n        )\n        return result\n", "path": "mindsdb/integrations/handlers/file_handler/file_handler.py"}], "after_files": [{"content": "from io import BytesIO, StringIO\nimport os\nimport csv\nimport json\nimport codecs\nimport traceback\nimport tempfile\nfrom urllib.parse import urlparse\n\nimport requests\nimport pandas as pd\n\nfrom mindsdb_sql import parse_sql\nfrom mindsdb_sql.parser.ast.base import ASTNode\nfrom mindsdb_sql.parser.ast import DropTables, Select\n\nfrom mindsdb.api.mysql.mysql_proxy.utilities.sql import query_df\nfrom mindsdb.integrations.libs.base_handler import DatabaseHandler\nfrom mindsdb.integrations.libs.response import (\n    HandlerStatusResponse as StatusResponse,\n    HandlerResponse as Response,\n    RESPONSE_TYPE\n)\n\n\ndef clean_row(row):\n    n_row = []\n    for cell in row:\n        if str(cell) in ['', ' ', '  ', 'NaN', 'nan', 'NA']:\n            n_row.append(None)\n        else:\n            n_row.append(cell)\n\n    return n_row\n\n\nclass FileHandler(DatabaseHandler):\n    \"\"\"\n    Handler for files\n    \"\"\"\n    type = 'files'\n\n    def __init__(self, name=None, db_store=None, fs_store=None, connection_data=None, file_controller=None):\n        super().__init__(name)\n        self.parser = parse_sql\n        self.fs_store = fs_store\n        self.custom_parser = connection_data.get('custom_parser')\n        self.clean_rows = connection_data.get('clean_rows', True)\n        self.file_controller = file_controller\n\n    def connect(self, **kwargs):\n        return\n\n    def disconnect(self, **kwargs):\n        return\n\n    def check_connection(self) -> StatusResponse:\n        return StatusResponse(True)\n\n    def query(self, query: ASTNode) -> Response:\n        if type(query) == DropTables:\n            for table_identifier in query.tables:\n                if len(table_identifier.parts) == 2 and table_identifier.parts[0] != self.name:\n                    return Response(\n                        RESPONSE_TYPE.ERROR,\n                        error_message=f\"Can't delete table from database '{table_identifier.parts[0]}'\"\n                    )\n                table_name = table_identifier.parts[-1]\n                try:\n                    self.file_controller.delete_file(table_name)\n                except Exception as e:\n                    return Response(\n                        RESPONSE_TYPE.ERROR,\n                        error_message=f\"Can't delete table '{table_name}': {e}\"\n                    )\n            return Response(RESPONSE_TYPE.OK)\n        elif type(query) == Select:\n            table_name = query.from_table.parts[-1]\n            file_path = self.file_controller.get_file_path(table_name, company_id=None)\n            df, _columns = self._handle_source(file_path, self.clean_rows, self.custom_parser)\n            result_df = query_df(df, query)\n            return Response(\n                RESPONSE_TYPE.TABLE,\n                data_frame=result_df\n            )\n        else:\n            return Response(\n                RESPONSE_TYPE.ERROR,\n                error_message=\"Only 'select' and 'drop' queries allowed for files\"\n            )\n\n    def native_query(self, query: str) -> Response:\n        ast = self.parser(query, dialect='mindsdb')\n        return self.query(ast)\n\n    @staticmethod\n    def _handle_source(file_path, clean_rows=True, custom_parser=None):\n        # get file data io, format and dialect\n        data, fmt, dialect = FileHandler._get_data_io(file_path)\n        data.seek(0)  # make sure we are at 0 in file pointer\n\n        if custom_parser:\n            header, file_data = custom_parser(data, fmt)\n\n        elif fmt == 'csv':\n            csv_reader = list(csv.reader(data, dialect))\n            header = csv_reader[0]\n            file_data = csv_reader[1:]\n\n        elif fmt in ['xlsx', 'xls']:\n            data.seek(0)\n            df = pd.read_excel(data)\n            header = df.columns.values.tolist()\n            file_data = df.values.tolist()\n\n        elif fmt == 'json':\n            data.seek(0)\n            json_doc = json.loads(data.read())\n            df = pd.json_normalize(json_doc, max_level=0)\n            header = df.columns.values.tolist()\n            file_data = df.values.tolist()\n\n        else:\n            raise ValueError('Could not load file into any format, supported formats are csv, json, xls, xlsx')\n\n        if clean_rows:\n            file_list_data = [clean_row(row) for row in file_data]\n        else:\n            file_list_data = file_data\n\n        header = [x.strip() for x in header]\n        col_map = dict((col, col) for col in header)\n        return pd.DataFrame(file_list_data, columns=header), col_map\n\n    @staticmethod\n    def _get_data_io(file_path):\n        \"\"\"\n        This gets a file either url or local file and defiens what the format is as well as dialect\n        :param file: file path or url\n        :return: data_io, format, dialect\n        \"\"\"\n\n        ############\n        # get file as io object\n        ############\n\n        # file_path = self._get_file_path()\n\n        data = BytesIO()\n\n        try:\n            with open(file_path, 'rb') as fp:\n                data = BytesIO(fp.read())\n        except Exception as e:\n            error = 'Could not load file, possible exception : {exception}'.format(exception=e)\n            print(error)\n            raise ValueError(error)\n\n        dialect = None\n\n        ############\n        # check for file type\n        ############\n\n        # try to guess if its an excel file\n        xlsx_sig = b'\\x50\\x4B\\x05\\06'\n        # xlsx_sig2 = b'\\x50\\x4B\\x03\\x04'\n        xls_sig = b'\\x09\\x08\\x10\\x00\\x00\\x06\\x05\\x00'\n\n        # different whence, offset, size for different types\n        excel_meta = [('xls', 0, 512, 8), ('xlsx', 2, -22, 4)]\n\n        for filename, whence, offset, size in excel_meta:\n\n            try:\n                data.seek(offset, whence)  # Seek to the offset.\n                bytes = data.read(size)  # Capture the specified number of bytes.\n                data.seek(0)\n                codecs.getencoder('hex')(bytes)\n\n                if bytes == xls_sig:\n                    return data, 'xls', dialect\n                elif bytes == xlsx_sig:\n                    return data, 'xlsx', dialect\n\n            except Exception:\n                data.seek(0)\n\n        # if not excel it can be a json file or a CSV, convert from binary to stringio\n\n        byte_str = data.read()\n        # Move it to StringIO\n        try:\n            # Handle Microsoft's BOM \"special\" UTF-8 encoding\n            if byte_str.startswith(codecs.BOM_UTF8):\n                data = StringIO(byte_str.decode('utf-8-sig'))\n            else:\n                data = StringIO(byte_str.decode('utf-8'))\n\n        except Exception:\n            print(traceback.format_exc())\n            print('Could not load into string')\n\n        # see if its JSON\n        buffer = data.read(100)\n        data.seek(0)\n        text = buffer.strip()\n        # analyze first n characters\n        if len(text) > 0:\n            text = text.strip()\n            # it it looks like a json, then try to parse it\n            if text.startswith('{') or text.startswith('['):\n                try:\n                    json.loads(data.read())\n                    data.seek(0)\n                    return data, 'json', dialect\n                except Exception:\n                    data.seek(0)\n                    return data, None, dialect\n\n        # lets try to figure out if its a csv\n        try:\n            dialect = FileHandler._get_csv_dialect(file_path)\n            if dialect:\n                return data, 'csv', dialect\n            return data, None, dialect\n        except Exception:\n            data.seek(0)\n            print('Could not detect format for this file')\n            print(traceback.format_exc())\n            # No file type identified\n            return data, None, dialect\n\n    @staticmethod\n    def _get_file_path(path) -> str:\n        try:\n            is_url = urlparse(path).scheme in ('http', 'https')\n        except Exception:\n            is_url = False\n        if is_url:\n            path = FileHandler._fetch_url(path)\n        return path\n\n    @staticmethod\n    def _get_csv_dialect(file_path) -> csv.Dialect:\n        with open(file_path, 'rt') as f:\n            try:\n                accepted_csv_delimiters = [',', '\\t', ';']\n                dialect = csv.Sniffer().sniff(f.read(128 * 1024), delimiters=accepted_csv_delimiters)\n            except csv.Error:\n                dialect = None\n        return dialect\n\n    @staticmethod\n    def _fetch_url(url: str) -> str:\n        temp_dir = tempfile.mkdtemp(prefix='mindsdb_file_url_')\n        try:\n            r = requests.get(url, stream=True)\n            if r.status_code == 200:\n                with open(os.path.join(temp_dir, 'file'), 'wb') as f:\n                    for chunk in r:\n                        f.write(chunk)\n            else:\n                raise Exception(f'Responce status code is {r.status_code}')\n        except Exception as e:\n            print(f'Error during getting {url}')\n            print(e)\n            raise\n        return os.path.join(temp_dir, 'file')\n\n    def get_tables(self) -> Response:\n        \"\"\"\n        List all files\n        \"\"\"\n        files_meta = self.file_controller.get_files()\n        data = [{\n            'TABLE_NAME': x['name'],\n            'TABLE_ROWS': x['row_count']\n        } for x in files_meta]\n        return Response(\n            RESPONSE_TYPE.TABLE,\n            data_frame=pd.DataFrame(data)\n        )\n\n    def get_columns(self, table_name) -> Response:\n        file_meta = self.file_controller.get_file_meta(table_name)\n        result = Response(\n            RESPONSE_TYPE.TABLE,\n            data_frame=pd.DataFrame([\n                {\n                    'Field': x.strip(),\n                    'Type': 'str'\n                } for x in file_meta['columns']\n            ])\n        )\n        return result\n", "path": "mindsdb/integrations/handlers/file_handler/file_handler.py"}]}