Member Avatar for TJoF
TJoF 0 Newbie Poster

I am very new to powershell and I am attempting to import multiple csv files into a SQL database. I have it working if I specify the file but I want to have it import multiple files from a specific folder. The way it's working right now, it will import the one specified file multiple times, based on how many files are in the folder. I've done some digging around and I believe I need to do something like

Set objFSO = CreateObject("Scripting.FileSystemObject")
objStartFolder = "C:\Files"

Set objFolder = objFSO.GetFolder(objStartFolder)
Set Files = objFolder.Files
For each objfile in Files

and then do the portion that is working below but I am having trouble figuring it out. When I attempted to put the above code in with it I get an error expecting a "(" after "For"

$reportLocation="\\Location\Files\"

$reportname = $reportlocation + "File.csv"

      foreach ($file in Get-ChildItem $reportLocation )

    {   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server=ServerName;Database=DatabaseName;Integrated Security=True"
        $SqlConnection.Open()
        $ExecuteDate=(Get-Date -Format s)
        $Today=(Get-Date -Format s)
        $csv = Import-Csv $reportname
        foreach ($line in $csv ) 
        {
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.Connection=$SqlConnection
            $newline = $line.'Hour'.split(" ")
            $SqlCmd.CommandText = ("Insert TableName Values('{0}','{1}','{2:hh:mm}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','','$Today','','','','$Today','')" -F
            $line.'TollFree #',                     
            $line.'Date',                       
            [datetime]$newline[0],              
            $newline[1],                        
            $line.'Attempted Calls',                
            $line.'Completed Calls',                
            $line.'Completed MoU',              
            $line.'Incomplete Calls',           
            $line.'Percent Complete',           
            $line.'Busy Calls',                 
            $line.'Percent Busy'                
            )
            $null=$SqlCmd.ExecuteScalar()   
        }
        $SqlConnection.close()
    }

Hopefully I provided enough information. If someone could provide some assitance it would be greatly appreciated!

  • 1 Contributor
  • 0 Replies
  • 356 Views

Dani AI

Generated

— the core problem is that the loop is enumerating the files but the import always uses a single hard-coded filename. Also the VBScript-style snippet (Set objFSO, For each ...) won't parse in PowerShell, which explains the "expecting a '(' after 'For'" error. Use PowerShell's Get-ChildItem to collect CSV files and Import-Csv -Path $file.FullName (or Join-Path) inside the loop. Open the SQL connection once (outside the file loop) and use parameterized commands to avoid quoting issues and improve reliability.

Example (minimal, corrected pattern):

$reportLocation = '\\Location\Files\'
$files = Get-ChildItem -Path $reportLocation -Filter '*.csv' -File

$cn = New-Object System.Data.SqlClient.SqlConnection('Server=ServerName;Database=DatabaseName;Integrated Security=True')
$cn.Open()

foreach ($file in $files) {
    $rows = Import-Csv -Path $file.FullName
    foreach ($r in $rows) {
        $cmd = $cn.CreateCommand()
        $cmd.CommandText = 'INSERT INTO TableName (TollFree, CallDate, CallTime, Attempts) VALUES (@toll, @date, @time, @att)'
        $cmd.Parameters.AddWithValue('@toll', $r.'TollFree #') | Out-Null
        $cmd.Parameters.AddWithValue('@date', [datetime]::Parse($r.Date)) | Out-Null
        $cmd.Parameters.AddWithValue('@time', [datetime]::Parse($r.Hour).TimeOfDay) | Out-Null
        $cmd.Parameters.AddWithValue('@att', [int]$r.'Attempted Calls') | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }
}

$cn.Close()

Troubleshooting notes: use -Filter '*.csv' -File (or Where-Object { -not $_.PSIsContainer } on older PS), wrap parsing in Try/Catch for malformed rows, and consider SqlBulkCopy for large imports. Log filenames and row counts so it's clear which file produced errors.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.