CSV-Import with variables (foreach) dosent work

PowerShell Help
1

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?

 

 

2

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 ‘;’

}

3

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

 

4

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.

5

" 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
6

No, I need the individual variables.

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

7

[quote quote=161535]No, I need the individual variables.

Like ticket_id and company_id, to work with them later in sql.[/quote]
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?

8

What if you did:

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

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.

 

10

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.