0

I am making a table that absorbs values from a csv file. I have several numeric columns and when converting text to number, when inserting values, I get an error.

I have NULL values in the csv. Is there a way to remove them from the database. I remove them manually.
NULL -> '0'.

I tried DEFAULT. ISNULL, I don't know if I can use it when creating a table. What I want to try is not to open the csv anymore. This way I get it. And when importing to the database, the NULLs go to zero.

I receive the files in csv. I open them, remove the NULL values. Data screenshot

   7939102772 2401679 108271 0 3000062862 174529 8129 
   7939102772 2401679 108271 0 3000062862 174529 8129 
   7939102772 2401679 108271 0 3000062862 174529 8129 
1. NULL NULL NULL NULL NULL NULL NULL

And I use:

BULK INSERT [dbo] 
FROM 'C:csv' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR='\n');
Luuk
  • 12,245
  • 5
  • 22
  • 33
steve_mc
  • 17
  • 4
  • 6
    Please don't use images for data, code or errors - use formatted/tabular text. And how are you importing your values? – Dale K Feb 23 '21 at 07:09
  • 1
    We need more information how the CSV is imported. But my first guess was that you can try to use [`CASE .. WHEN`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql) – Stephan Bauer Feb 23 '21 at 07:12
  • How does the `NULL` value looks like in the csv file ? – Squirrel Feb 23 '21 at 07:34
  • TEXT, no pictures... But now it is unknown if this matches your situation, so please use [edit] to copy/paste your data in your question. – Luuk Feb 23 '21 at 07:34
  • _when inserting values, I get an error_ Post the complete and actual error message, all of it. – SMor Feb 23 '21 at 14:01

2 Answers2

0

You can allow nulls while importing to remove errors caused by null insertion. If you are importing csv using a script or sql query you can update your query as

INSERT INTO table(Col1,Col2,Col3.....) VALUES(ISNULL(value1,0),ISNULL(value2,0),ISNULL(value3,0))

Or You can update your table after successfuly importing the csv with following script

update  Tableset  Col1=IIf([Col1] Is Null, "0", [Col1]), 

[Col2]=IIf([Col2] Is Null, "0", [Col2]), 

[Col3] =IIf([Col3] Is Null, "0", [Col3]) 

where [Col1] is null or [Col2] is null or [Col3] is null

The best approach though is to process and clean your data before importing into a database using python, jupyter or any language you are comfortable with.

Bilal Bin Zia
  • 596
  • 3
  • 12
  • this works how ever it is not generic. I wonder if you can use a table function that can use system tables to identify all the column names then return a table with all the columns isnull – Golden Lion Feb 23 '21 at 12:57
  • This is common practice when importing from csv. There are a lot of nulls and inconsistent data. There is no such function to my knowledge but it would be helpful if there was. That's why cleaning your data before inserting in database is better – Bilal Bin Zia Feb 23 '21 at 15:00
  • a function like fillna(0) needs to be built for the dataset. One approach is to load the csv in pandas and then fillna(0) the missing data then use python sqlalchemy to upload the data – Golden Lion Feb 23 '21 at 16:03
  • That is exactly what I am suggesting. To clean data using pandas, python prior to insertion – Bilal Bin Zia Feb 23 '21 at 16:06
  • of course, everyone loves pandas. microsoft should provide equivalent etl functionality, in the future – Golden Lion Feb 23 '21 at 16:07
  • We can hope. Net 6 is getting a lot of updates. – Bilal Bin Zia Feb 23 '21 at 18:52
  • look at creating a staging table then inspect the numeric column data and update nulls to 0 – Golden Lion Feb 23 '21 at 18:57
0

SQL Server Import Wizard treats NULL as literal string 'NULL'

another idea is to put the imported data into a staging table then apply and update for the string 'NULL' resulting from the import

Golden Lion
  • 3,840
  • 2
  • 26
  • 35