Change null values to zero on import

Question

Hi, I have some code to import a csv file called "plot". On import i would like to change the null values in selected columns to zero. I have made an sql query using Nz() to change the nulls in a query but i think it would be more efficient to execute this on import.

My sql statement is:

SELECT Nz([water_info_water_body],"0") AS water_body, Nz([water_info_water_dynamics],"0") AS water_dynamics, Nz([water_info_artificiality],"0") AS water_artificiality
FROM plot;

I would like to include the process in my vba code below so it only required one action:

Private Sub cmdImportPlot_Click()
Dim strfilename As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select the CSV file to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv", 1
        .Filters.Add "All Files", "*.*", 2
        If .Show = -1 Then
            strfilename = .SelectedItems(1)
            DoCmd.TransferText TransferType:=acImportDelim, _
                TableName:="plot", hasfieldnames:=True, fileName:=strfilename
            Else
            Exit Sub
        End If
    End With
End Sub

................................

Any advice appreciated

Answer 1

Hi, I have some code to import a csv file called "plot". On import i would like to change the null values in selected columns to zero. I have made an sql query using Nz() to change the nulls in a query but i think it would be more efficient to execute this on import.

Rather than importing the .csv file directly, link to it, using a temporary table name. Then run an append query that selects from the linked table and appends to the target table, using the NZ() function in the append query to convert the null fields to your desired 0 values.  After the import, you can delete the temporary linked table.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

>>>On import i would like to change the null values in selected columns to zero. I have made an sql query using Nz() to change the nulls in a query but i think it would be more efficient to execute this on import.<<<

According to your description, I suggest that you could update table after you have imported *.csv file, refer to below code:

update  TBNullField set  water_body =IIf([water_body] Is Null, "0", [water_body ]), 

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

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

where [water_body] is null or [water_dynamics] is null or [water_artificiality] is null

then you could use DoCmd.RunSQL Method to execute the corresponding SQL statement, refer to below code:

Dim SQL As String 
     
SQL = "your SQL statement" 
 
DoCmd.RunSQL SQL 

For more information, click here to refer about DoCmd.RunSQL Method (Access)