CSV-Import with variables (foreach) dosent work

Welcome Forums General PowerShell Q&A CSV-Import with variables (foreach) dosent work

This topic contains 9 replies, has 5 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #160629

    Participant
    Topics: 1
    Replies: 3
    Points: 22
    Rank: Member

    Hello together,

    i have multiple .csv files which looks similar to this:

    Ticket_ID;Date_Submitted;Company_Name;Company_ID;
    TICK012345;04.01.2019 10:14;44444;companyname1;
    TICK012346;04.01.2019 11:16;55555;companyname2;

    now i want to import the .csv files to powershell and from there with ms orchestrator to sql, for automation.

    I made the following powershell script:

    $ticket= import-csv “C:\csv\*.csv” -Delimiter ";"
    ForEach ($TICK in $ticket){
    
    $ticket_id = $($TICK .Ticket_ID)
    $date_submitted = $($TICK .Date_Submitted)
    $company_id = $($TICK .Company_ID)
    $company_name = $($TICK .Company_Name)

    The problem is, if i have two or more tickets, like above, it only shows me the data of the last one. In this case TICK012346. Dosent matter if i do it in powershell or orchestrator.

    I need one record for each ticket.

    Where is the failure?

     

     

  • #160647

    Participant
    Topics: 1
    Replies: 1530
    Points: 2,591
    Helping Hand
    Rank: Community Hero

    Your csv files sould not have a semikolon at the end of each line!! If it's a valid csv file this should be enough:

    Get-ChildItem -Path C:\csv -Filter *.csv |
        ForEach-Object {
            Import-Csv -Path $_.FullName -Delimiter ';'
        }
  • #160649

    Participant
    Topics: 1
    Replies: 59
    Points: 328
    Helping Hand
    Rank: Contributor

    What's the rest of the of the script?

    The script supplied has a space between the variable and the field, I'm assuming that's just a formatting issue.
    Also, When I try to do this:
    $ticket=import-csv *.csv -delimiter ";" I get this:
    Cannot perform operation because the path resolved to more than one file. This command cannot operate on
    multiple files

    You'll probably want to do something along the lines of

    $files=(get-childitem *.csv)
    $ticket=foreach ($f in $files){import-csv $f -delimiter ";"}

    I took your sample lines, and wrote this:

    ForEach ($TICK in $ticket){
    $company_name = $($TICK.Company_Name)
    $company_id = $($TICK.Company_ID)
    $date_submitted = $($TICK.Date_Submitted)
    $ticket_id = $($TICK.Ticket_ID)
    write-host $company_name
    }

    and the output was:
    44444
    55555

     

  • #161499

    Participant
    Topics: 1
    Replies: 3
    Points: 22
    Rank: Member

    thank you guys.

    with:

    write-host $ticket_id
    

    i also get both ticket ids. When i do

    $ticked_id

    i only get the last one.

    Also when i try to forward the data with orchestrator to .txt or sql i only get the last ticket.

    I tryed:

    $ticket_id | Out-File -FilePath D:\text.txt

    and i also got only the last ticket.

    • #161586

      Participant
      Topics: 1
      Replies: 59
      Points: 328
      Helping Hand
      Rank: Contributor

      What if you did:

      foreach ($t in $ticket_id){
      $t | Out-File -append -FilePath D:\text.txt
      }
  • #161528

    Participant
    Topics: 37
    Replies: 155
    Points: 479
    Helping Hand
    Rank: Contributor

    " i have multiple .csv files", should you not be appending as you loop through the CSV files ??

    
    $Results = @()
    
    ForEach ($TICK in $ticket){
    
    $Results += [PSCustomObject] @{
    
    ticket_id = $($TICK .Ticket_ID)
    date_submitted = $($TICK .Date_Submitted)
    company_id = $($TICK .Company_ID)
    company_name = $($TICK .Company_Name)
    
    }
    
    # this should contain data from all CSVs passed to it
    
    $Results
    
    
  • #161535

    Participant
    Topics: 1
    Replies: 3
    Points: 22
    Rank: Member

    No, I need the individual variables.

    Like ticket_id and company_id, to work with them later in sql.

  • #161570

    Participant
    Topics: 2
    Replies: 54
    Points: 278
    Helping Hand
    Rank: Contributor

    No, I need the individual variables.

    Like ticket_id and company_id, to work with them later in sql.

    Do you want every ticket ID number to be contained in the single ticket_id variable, or do you want each ticket ID number to be contained in a separate ticket_id variable so that they can be referenced individually?

  • #161681

    Participant
    Topics: 1
    Replies: 3
    Points: 22
    Rank: Member

    This is right:

    each ticket ID number to be contained in a separate ticket_id variable so that they can be referenced individually

    i later need to work with the difference tickets and its related data.

     

    • #161721

      Participant
      Topics: 1
      Replies: 59
      Points: 328
      Helping Hand
      Rank: Contributor

      I'm still not sure what the issue is then.

      This will import all of your .csv files into an object:

      $files=(get-childitem *.csv)
      $ticket=foreach ($f in $files){import-csv $f -delimiter ";"}

      So you know have an object called $ticket. In my test I had 2 rows per file and 3 files. When I did
      $ticket.count I had 6 records in it.

      $ticket |out-file -append alltickets.txt
      $ticket |export-csv alltickets.csv -notypeinformation
      

      Either command will output all of the tickets into a .txt format or a .csv format.

The topic ‘CSV-Import with variables (foreach) dosent work’ is closed to new replies.