locked
out of memory condition when loading file RRS feed

  • Question

  •  I have a folder where there are multiple files(100mb+) in size where I'm trying to pull certain columns
    to create a single. When I run it consumes all of the memory loading a single .csv file. Is there a better way to perform
    this task .. I can't install ACE driver on the machine where process will run.

    # Folder containing source CSV files. Be sure to include a backslash after the folder.
    $folderPath = 'C:\CSV_Source\'
    
    # Destination folder for the new files. Be sure to include a backslash after the folder.
    $folderPathDest = 'C:\CSV_Destination\'
    
    # Desired columns - enclose in single quotes.
    $desiredColumns = 'SOD_NBR','SOD_DUE_DATE','SOD_PER_DATE','SOD_REQ_DATE','SOD_LINE'
    
    # Generate a list of all files in the folder and pipe it to ForEach-Object
    Get-ChildItem $folderPath -Name |
    
    # Loop through each file
    ForEach-Object { 
    
        # Combines source folder path and file name
        $filePath = $folderPath + $_
    
        # Combines destination folder and file name
        $filePathdest = $folderPathDest + $_
    
        # Imports CSV file, selects desired columns, and then exports as CSV to the desired destination
        Import-Csv $filePath | Select $desiredColumns |
        Export-Csv -Path $filePathDest –NoTypeInformation
    }

     Thanks.

    Tuesday, August 15, 2017 7:58 PM

Answers

  • Import-Csv file.csv | 
    	Where-Object{
    		Try{
    			if ([datetime]$_.SOD_DUE_DATE -ge [dattetime]::Today) {
    				$true
    	        }else{
    				$false
    	        }
    	    }
    		Catch{
    			$false
    	    }
    	} |
    	Select-Object $destiredColumns
    	
    


    \_(ツ)_/

    • Marked as answer by hart60 Thursday, August 17, 2017 12:50 AM
    Wednesday, August 16, 2017 5:10 PM

All replies

  • Processing  in a pipeline does not consume all memory although a system that is not busy may appear to use a lot of memory.

    Import-Csv and posting to a SQL connection would be slow but will work.  For performance you will have to use BCP or any of the powerful third party loaders.


    \_(ツ)_/


    • Edited by jrv Tuesday, August 15, 2017 9:00 PM
    Tuesday, August 15, 2017 8:59 PM
  •  Would there be a way to add a filter to the column selection?

    # Desired columns - enclose in single quotes.
    $desiredColumns = 'SOD_NBR','SOD_DUE_DATE','SOD_PER_DATE','SOD_REQ_DATE','SOD_LINE'

    I want these columns, but only if say

    SOD_DUE_DATE

    is equal to today.

     Thanks.

     

    Wednesday, August 16, 2017 5:51 AM
  • Import-Csv | Where{ [datetime]$_.SOD_DUE_DATE -ge  [dattetime]::Today} |select $destiredColumns

    \_(ツ)_/

    Wednesday, August 16, 2017 1:27 PM
  •  I'm trying to Insert a check to make sure I have a valid date before the Where clause since
    I found there are bad characters in that field in csv.

      if ([string]$_.SOD_DUE_DATE -as [DateTime])

     If I don't have a valid date then I just want to loop to next record in file and maybe log the
    bad data record to an error file.

     Thanks.

    Wednesday, August 16, 2017 3:28 PM
  • If the string is not a proper datetime format then you will have t0 manually convert it to a datatime using one of the many conversion techniques.  You cannot test a string using "-as [datetime]"  That is the same thing as [datetime]$_.SOD_DUE_DATE.  If the string is in the wrong format then it must be converted manually.

    You can use Get-Date or [datetime]::ParseExact() to convert the string.

    Since we have no idea what is in your CSV we cannot help more than this.


    \_(ツ)_/

    Wednesday, August 16, 2017 3:35 PM
  •  The system that creates the file(csv) doesn't have a check against that field so it's either Empty

    or has special character value of '?' in the field. The process only needs to export-csv if that field
    has a valid date or maybe set it to DBNULL than export.

    Thanks.

    Wednesday, August 16, 2017 4:07 PM
  • Import-Csv file.csv | 
    	Where-Object{
    		Try{
    			if ([datetime]$_.SOD_DUE_DATE -ge [dattetime]::Today) {
    				$true
    	        }else{
    				$false
    	        }
    	    }
    		Catch{
    			$false
    	    }
    	} |
    	Select-Object $destiredColumns
    	
    


    \_(ツ)_/

    • Marked as answer by hart60 Thursday, August 17, 2017 12:50 AM
    Wednesday, August 16, 2017 5:10 PM
  •  It runs, but output file is empty. I look at the csv and see many records with 08/16/17 in sod_due_date
    field. I tried a format on the date, but still no output.

     Thanks.

    Wednesday, August 16, 2017 8:10 PM
  • That tends to indicate that your column names are wrong.  Add write-host statements to trace the code.


    \_(ツ)_/

    Wednesday, August 16, 2017 8:41 PM
  •  The Delimiter was set wrong.

     Many Thanks again for ALL your help... its working great!!!!!!

    Thursday, August 17, 2017 12:50 AM