Hello, I am new to programming, I have problems importing data from excel to SQL, this time one of the columns of the xls file that has alphanumeric content, related to car models on time.
After some tests I realized that SQL takes the first cell of the column and according to the data type of that cell assigns to the rest of the column, that is: if the first data of the cell is "500" it will read all the cells as numeric and it will pass null those cells that have text and vice versa, if the cell starts with "a500" it passes null the cells that have numbers.
Explained in another way, if we talk about source data and data received in relation to the data received, I can choose the mapping, that is, where those source data will "land", in which table which Excel column for which table column in SQL. The problem is that SQL when reading the data type of the source data seems to cancel the numeric or text cells of the column taken depending on the data type of the first excel cell.
So the question is question is: How could I do to transport all the data of that column without nulls? Is there a way to choose how it has to read the SQL data?
It seems that the behavior of the import is quite particular, it effectively defines the column based on the first row. One possibility could have been to add a first dummy row to force the column evaluation to a string, something like this:
But what happens in these cases is that it correctly imports the strings but ignores the numbers, for example: the one
500in the third row does not transform it into a string, it ignores it.Modifying the definition of the columns of the table does not work either. The solution I found is to "tell" Excel that a certain number is considered a text, the classic method is to add a single quote in front of the number:
And now yes: