none
Change null values to zero on import RRS feed

  • 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



    Saturday, January 23, 2016 7:35 AM

Answers

  • 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

    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:39 AM
    Saturday, January 23, 2016 9:25 PM
  • >>>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)


    Monday, January 25, 2016 2:11 AM

All replies

  • 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

    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:39 AM
    Saturday, January 23, 2016 9:25 PM
  • >>>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)


    Monday, January 25, 2016 2:11 AM