CSV-Import with variables (foreach) dosent work

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

Viewing 7 reply threads
  • 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: 5
      Replies: 2368
      Points: 5,987
      Helping Hand
      Rank: Community MVP

      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: 64
      Points: 363
      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: 64
        Points: 363
        Helping Hand
        Rank: Contributor

        What if you did:

        foreach ($t in $ticket_id){
        $t | Out-File -append -FilePath D:\text.txt
        }
    • #161528
      Participant
      Topics: 49
      Replies: 194
      Points: 783
      Helping Hand
      Rank: Major 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
      Senior Moderator
      Topics: 3
      Replies: 123
      Points: 653
      Helping Hand
      Rank: Major 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: 64
        Points: 363
        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.

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