Solved

import csv to use with powershell

Posted on 2014-09-12
12
279 Views
Last Modified: 2014-09-13
hi folks,

how can i import csv with converting to powershell understanding format?
 
Import-Csv -Path c:\test.csv -Delimiter ';'

source data
0
Comment
Question by:Mandy_
  • 5
  • 5
  • 2
12 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40319846
Here is the full technet
http://technet.microsoft.com/en-us/library/hh849891.aspx

the delimiter you specify is anticipated to be the separator for each column of data

e.g

Server;Database;LastAccessed
SERVER1;MYSQLBD;10/01/2013
SERVER2;MYSQLBD2;10/02/2013
SERVER3;MYSQLBD3;10/0/2013

import-csv c:\file.csv -delimiter ";" |  %{write-host $_.server $_.Database $_.LAstAccessed}
what the line above does, is it tells import csv ";" represents a new column and then writes the values for each column in the foreach loop, that matches the header I specify.
0
 
LVL 39

Expert Comment

by:footech
ID: 40319851
A rather vague question...
Typically you'll do one of two things with Import-CSV, assign the results to a variable, or send the results to the pipeline for further processing.
$data = Import-CSV somefile.csv
Import-CSV somefile.csv | % { "do something" }

Open in new window


Is there a problem you're encountering?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40319864
I would like to import data in colums like that

Userid     dispalyname    
12345      jones, mike
 
to use them they should looks like that:

"UserId","Name"
"12345", "jones, mike"
0
 
LVL 29

Expert Comment

by:becraig
ID: 40319879
My post gives you a clear example of what to do.

Is there something specific about the process you do not get ?

I gave an example of the input and how to read the input and output it to screen.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40319886
see my post above
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40319894
(import-csv -delimiter ';' c:\export_9.csv | sort produkt, version -descending | convertto-csv -notype -delimiter ',') -replace '"'

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 29

Expert Comment

by:becraig
ID: 40319905
Can you give a sample of your input file ?

It helps to be able to repro the exact circumstance so the solution is exactly what you need.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40319942
Source
Userid     dispalyname    
12345      jones, mike
 
DEST:

"UserId","Name"
"12345", "jones, mike"
0
 
LVL 29

Expert Comment

by:becraig
ID: 40319950
For this I would suggest a simple get-content and use a split create a hash then export to csv (since your source does not have a valid delimiter).

Footech may have another approach.

I will put a quick script together.
0
 
LVL 29

Assisted Solution

by:becraig
becraig earned 200 total points
ID: 40319974
$nucsv = @()
 gc c:\data\file.csv | select -skip 1 | % {
            $cols = $_ -split "\s+"
            $item = New-Object PSObject
            $item | Add-Member -type NoteProperty -Name 'USERID' -Value $cols[0].trim()
            $item | Add-Member -type NoteProperty -Name 'DISPLAYNAME' -Value $cols[1].trim()
            $nucsv += $item
            }

            $nucsv | Export-Csv -Delimiter ";" -nti c:\data\report.csv

Open in new window

0
 
LVL 39

Accepted Solution

by:
footech earned 300 total points
ID: 40319996
One issue I see is that it looks like a field can contain spaces like "jones, mike".  You could adjust becraig's code with the following (only the split is changed).
$nucsv = @()
gc c:\test.csv | select -skip 1 | % {
    $cols = $_ -split "\s{2,}"
    $item = New-Object PSObject
    $item | Add-Member -type NoteProperty -Name 'USERID' -Value $cols[0].trim()
    $item | Add-Member -type NoteProperty -Name 'DISPLAYNAME' -Value $cols[1].trim() -PassThru
    $nucsv += $item
    }

Open in new window


Another method would be like
Get-Content input.csv | % { $_ -replace "\s{2,}",";" } | ConvertFrom-Csv -Delimiter ";" | Export-CSV output.csv -notype

Open in new window

0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40320606
Thank you so much
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
This article will help you understand what HashTables are and how to use them in PowerShell.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now