logo
down
shadow

Import-Csv of huge (13M Rows) filtered using Where-Object with 11k rows


Import-Csv of huge (13M Rows) filtered using Where-Object with 11k rows

Content Index :

Import-Csv of huge (13M Rows) filtered using Where-Object with 11k rows
Tag : powershell , By : haver
Date : November 25 2020, 07:06 PM

it fixes the issue You can make this script to run much faster:
No need to use Import-Csv/Export-csv. Reading/Writng lines is sufficient and much faster $_.ip -in $IP is inefficient. Use hashtable for look ups (it will be about instant) Use .net tools rather than built in cmdlets
$inFile = "C:\stack\IpTables\Data.txt"
$IPfile =  "C:\stack\IpTables\IPs.txt"
$outFile = "C:\stack\IpTables\OutData.txt"
$ipIndexInData = 47 #index of IP address column in your data file

#build a hashtable for IP look up. 
$hash = @{}
[System.IO.File]::ReadAllLines($IPfile) | foreach {$hash.Add($_, $true)}
# if IP values in your list are not unique then wrap $hash.Add() with try/catch


$fsIn = [System.IO.StreamReader]$inFile
$fsOut = [System.IO.StreamWriter]$outFile

$fsOut.WriteLine($fsIn.ReadLine()) # this will write first row with column names to out file. Comment it out if first row is data row

while (!$fsIn.EndOfStream) {

 $line = $fsIn.ReadLine()
 $row = $line -split ","

 if($hash[$row[$ipIndexInData].Trim('"')]) { # remove .Trim('"') if values in your data file are not quoted with "

    $fsOut.WriteLine($line)
 }

}

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How to import huge CSV file with 200,00 rows to MySQL (asynchronous and fast)?


Tag : php , By : Ravenal
Date : March 29 2020, 07:55 AM
around this issue to everyone who gave answers to this question. I have discovered a solution! Just wanted to share it, in case someone needs to create a PHP script that will import a huge CSV file into MySQL database (asynchronously and fast!) I have tested my code with 400,000 rows and the importing is done in seconds. I believe it would work with larger files, you just have to modify maximum upload file size.
In this example, I will be importing a CSV file that contains two columns (name, contact_number) into a MySQL DB that contains the same columns.
CREATE TABLE `testdb`.`table_test`
( `id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
`contact_number` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)) ENGINE = InnoDB;
<form action="upload.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv" value="" />
<input type="submit" name="submit" value="Save" /></form>
<?php
//modify your connections here
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
?>
<?php
include('connect.php');
$data = $_POST['file'];
$handle = fopen($data, "r");
$test = file_get_contents($data);
if ($handle) {
    $counter = 0;
    //instead of executing query one by one,
    //let us prepare 1 SQL query that will insert all values from the batch
    $sql ="INSERT INTO table_test(name,contact_number) VALUES ";
    while (($line = fgets($handle)) !== false) {
      $sql .= "($line),";
      $counter++;
    }
    $sql = substr($sql, 0, strlen($sql) - 1);
     if ($conn->query($sql) === TRUE) {
    } else {
     }
    fclose($handle);
} else {  
} 
//unlink CSV file once already imported to DB to clear directory
unlink($data);
?>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.1/jquery.js"></script>
<script>
//Declaration of function that will insert data into database
 function senddata(filename){
        var file = filename;
        $.ajax({
            type: "POST",
            url: "senddata.php",
            data: {file},
            async: true,
            success: function(html){
                $("#result").html(html);
            }
        })
        }
 </script>
<?php
$csv = array();
$batchsize = 1000; //split huge CSV file by 1,000, you can modify this based on your needs
if($_FILES['csv']['error'] == 0){
    $name = $_FILES['csv']['name'];
    $ext = strtolower(end(explode('.', $_FILES['csv']['name'])));
    $tmpName = $_FILES['csv']['tmp_name'];
    if($ext === 'csv'){ //check if uploaded file is of CSV format
        if(($handle = fopen($tmpName, 'r')) !== FALSE) {
            set_time_limit(0);
            $row = 0;
            while(($data = fgetcsv($handle)) !== FALSE) {
                $col_count = count($data);
                //splitting of CSV file :
                if ($row % $batchsize == 0):
                    $file = fopen("minpoints$row.csv","w");
                endif;
                $csv[$row]['col1'] = $data[0];
                $csv[$row]['col2'] = $data[1];
                $min = $data[0];
                $points = $data[1];
                $json = "'$min', '$points'";
                fwrite($file,$json.PHP_EOL);
                //sending the splitted CSV files, batch by batch...
                if ($row % $batchsize == 0):
                    echo "<script> senddata('minpoints$row.csv'); </script>";
                endif;
                $row++; 
            }
            fclose($file);
            fclose($handle);
        }
    }
    else
    {
        echo "Only CSV files are allowed.";
    }
    //alert once done.
    echo "<script> alert('CSV imported!') </script>";
}
?>

Crystal reports shows all the rows of table even when rows are filtered C#


Tag : chash , By : JulianCT
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I found the answer after a lot of search:
I should use DataSet instead of DataTable
var command="Select ID,Nam,Family From Info where ID=2";
var connection=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data       Source=data.mdb");
var dt=new DataSet();//Here was the problem
 using (var da = new OleDbDataAdapter(command, Connect))
            da.Fill(dt);
var report=new Report();//prebuilt report
report.SetDataSource(dt);
CrystalReportViewer.ReportSource=report; 
report.Database.Tables[0].SetDataSource(dataTable);

Count rows and filter and then delete filtered rows with VBA


Tag : excel , By : Grik
Date : March 29 2020, 07:55 AM
Hope that helps Can any one tell me how to count the total number of rows in an Excel spreadsheet and then pass the value dynamically? , Regarding the question:
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Option Explicit

Sub Test()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range

    'set sheet reference
    Set ws = ActiveSheet

    'turn off autofilter
    ws.AutoFilterMode = False

    'get last row
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    'set range to filter
    Set rng = ws.Range("A1:C" & lastRow)

    'set filter
    rng.AutoFilter Field:=3, Criteria1:="=ABC", Operator:=xlOr, Criteria2:="=XYZ"

    'delete visible rows
    rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    'show remaining rows by removing autofilter
    ws.AutoFilterMode = False

End Sub

Excel VBA - Merge columns in X rows based on number of filtered rows in adjacent worksheet


Tag : excel , By : Magnus
Date : March 29 2020, 07:55 AM
this will help I have a worksheet (wsA) that I need to merge columns B and C for a variable amount of rows based on a filtered range in worksheet (wsB) (both in the same workbook). , To fix the issue, the code should be as follows:
filteredLastRow = ActiveSheet.UsedRange.Rows.Count
filledRows = ActiveSheet.Range("A2:A" & filteredLastRow).SpecialCells(xlCellTypeVisible).Count
Range("B12:C" & filledRows + 12).Merge True

Excel pivot table: display rows filtered, but keep total for all rows (unfiltered)


Tag : excel , By : 小和尚
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • change powershell command color
  • How to list all installed, runnable cmdlets in powershell?
  • PowerShell index value of PSObject
  • Powershell Foreach-Object behaves abnormaly on null values
  • My code will not write "No files to process"
  • Get certificates information using powershell
  • How to uninstall dell support assist with powershell?
  • EF6 Add-Migration in PowerShell, outside Visual Studio
  • Not able to rename button base on value
  • Description property dont give back result
  • Using powershell script with different parameters
  • How to get the current script to read off computer names off a txt file located on c:\
  • Powershell check if drive letter exists and if so remove
  • Powershell script to return search results from a list of keywords
  • PowerShell copy fails without warning
  • Get Windows Last Reboot Timestamp?
  • In PowerShell how to capture error, warning, write-host output into a single file?
  • Set-Acl only on on files containing certain keyword
  • Weird Coalesce Behavior
  • Search for a word within a file that has a specific modified date
  • How to fetch first column from given powershell array?
  • How to add text to a file foreach column using PowerShell?
  • How to share data between cmdlets in a module?
  • How to exclude some column in CSV using Powershell?
  • ARM Template for Redis Cache failing to deploy
  • Copy folders that match names to other sub folder of matching directory
  • How to set parameters for SaveAs() dialog in Word.Application?
  • Attempting to Filter out All .p12 AND .pfx files from a given Directory
  • Powershell FTP Script String Concatenation
  • How to GET variables used in a specific BUILD in Azure Devops / TFS via api
  • Cleaning up DistributionGroup in PowerShell
  • PowerShell method Contains not working as expected
  • Feeding Get-ChildItem path info from an array - Illegal characters
  • Script argument as private scope variable
  • PowerShell Hashtable to JSON without Quotes
  • How to set as variable csv column using powershell?
  • Move flag from one script to another in PowerShell
  • Sort-Object problem (one MAC address and two IP addresses)
  • concatenate columnar output in PowerShell
  • When a list of objects is retruned from Get-EventLog that itself contains an array of strings, how do I access the array
  • Passing down a variable to function creates an array
  • Get-Date cannot convert null to type "System.Datetime"
  • I want run PowerShell commands using administrator with normal PowerShell user
  • How to using loop to find a file and remove it using PowerShell?
  • PowerShell Get-ChildItem on folder that does not exist behaves different with the -recurse flag
  • How to do looping for checking an existing folder with PowerShell?
  • Having trouble outputting to a CSV file. I know the output is a curly brackets "collection", but I'm stumped
  • How to replace last part of a string dynamically?
  • Checking to see if PowerShell Command worked, Crap Error
  • (Date).ToString does not use specified delimiter
  • Loop through multiple array in powershell
  • How to mapping a file by matching data using powershell?
  • How to define the return type / OutputType of a function
  • Use multiple CSVs out a folder individually and save their names
  • Get-FileHash command in powershell 2.0
  • How to pick a files based on the first received in PowerShell?
  • Is there a way to get a range for numbers larger than int32?
  • PowerShell and process exit codes
  • Parse a txt file or an input using PowerShell
  • Store different positional numbers on a variable
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com