56893

Import Excel, Export CSV with PowerShell

Question:

Due to restrictions I either need to use VB or PowerShell for this task.

I have an Excel that looks like:

ColumA, ColumB,ColumC,ColumD,ColumE,ColumF 000|Txt,MoreTxt , ColumB,ColumC,ColumD,ColumE,ColumF

I read about import_csv -header, but I'm under to successfully do it. I'll post my script below. The export I expect is:

ColumA, ColumB, ColumC, ColumD, ColumE, ColumF 000, ColumB, ColumC, ColumD, ColumE, ColumF

Only Colum gets modified, and I -only- need the digits from before that pipe. It also has to stay three digits, so 1 becomes 001, etc.

This is the script I modified based on some previous inquiries I saw, and the MS Tutorial.

$file = import-csv "C:\path\to\my\file\test.csv" foreach ($row in $file){ $tempfile = New-Object psobject -Property @{ ColumA = $row. 'ListName'.substring(0,2) ColumB = $row. 'ColumB' ColumC = $row. 'ColumC' ColumE = $row. 'ColumE' ColumF = $row. 'ColumF' } $expandfile = @() $expandfile += $tempfile | select ColumA, ColumB, ColumC, ColumD, ColumE, ColumF }

PS gives me both errors on not liking everything I have in quotes (Which I thought was the column name, but I guess not. And also a parse error on the entire array. Essentially the entire script.

<strong>UPDATE</strong> Providing real examples of source.

"Tiam Name",SiamName,Siam,Ciam,Piam,Liam,Niam,Diam "002|City, State","City, State - Some text (15092)",1,"3,408",99,"3,408",780,22.89% "009|City, State","City, State - Some Text (E) (15450)",1,"1,894",81,"1,894",543,28.67%

Answer1:

Edit:

$expandfile = Import-Csv "C:\path\to\my\file\test.csv" | ForEach-Object { $_."Tiam`r`nName" = $_."Tiam`r`nName".SubString(0,3) $_ }

Recommend

  • Oracle Sql Update with values using foreign key?
  • Create columns in R within a for loop
  • How to efficiently get 10% of random numbers, then 10% of remaining 90 etc untill all points allocat
  • printk - showing in message log but not in terminal for any kernel log level
  • Functions returning a const value [duplicate]
  • How do I get a positive modulo on a negative dividend
  • Pattern combining type test and literal
  • How do I narrow down scope when running an ansible playbook?
  • HTML file fetched using 'wget' reported as binary by 'less'
  • Git cherry-Pick a batch of commits from a file
  • console.log printing statements in the wrong order for learnyounode node.js tutorial
  • Is there a way to query who are activesync/bb users through Exchange PowerShell?
  • Will WaitForMultipleObjects modify the state of *multiple* objects? [duplicate]
  • Changing Entity's State in Entity Framework 4.1
  • EF6 and pre-generated views
  • javascript Confirm replacement with return true/false
  • How to prompt user that edits have been made upon changing pages or sorting in Kendo Grid
  • windows batch file combine csv in a folder by column
  • cannot load gems in test environment
  • Determining the length of a read stream in node js
  • How to distribute an event to all nodes in a (Wildfly) cluster?
  • php show all images in directory and sort by last modified
  • EF 4.1 DBContext AutoDetectChangesEnabled
  • IE6 changes DOCTYPE to a bad one
  • How to read piped content in C?
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Use of this Javascript
  • Android full screen on only one activity?
  • C++ Partial template specialization - design simplification
  • Updated Ionic CLI but shows previous version (Windows)
  • How to get next/previous record number?
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Transpose CSV data with awk (pivot transformation)
  • Python: how to group similar lists together in a list of lists?
  • Buffer size for converting unsigned long to string
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Bitwise OR returns boolean when one of operands is nil
  • MATLAB: Piecewise function in curve fitting toolbox using fittype
  • Conditional In-Line CSS for IE and Others?