2

I am trying to import a CSV file with powershell in a database, with the path defined in a variable. But it doesn't seem to work.

$path = "C:\Users\Admin\Desktop\TestFolder\file.txt"   
Import-CSV -Header ("header1","header2","header3") -Path $path

The above doesn't work, and I am not sure why. If I print $path I do get the correct path, and if I run Import-CSV with the path directly in it, it also work fine.

Write-Host $path
C:\Users\Admin\Desktop\TestFolder\file.txt

Import-CSV -Header ("header1","header2","header3") -Path C:\Users\Admin\Desktop\TestFolder\file.txt
header1   test123
header2   test345
header3   test567

header1   test789
header2   test901
header3   test123
etc...

Using Windows Server 2012 Standard, and PowerShell 2.

Any help is appreciated,
Thanks


EDIT:
My full code looks like this, but this doesn't work. I am trying to watch a folder with PowerShell and then import it to SQL Server 2012. The folder watch works perfectly but I can't seem to get the import to work. I figured the problem is with $DataImport and thus I went back to the basics to figure out what the problem was. I tried the very basic setup of Import-CSV as mentioned above but this (in its plain version as above) doesn't work.. so I figured I am using Import-CSV wrong.

6
  • I just tried the first code in PowerShell V.3 ISE and it does work on my machine....
    – Darius
    Mar 14, 2013 at 16:30
  • Thanks, that is what I thought. It should just work, not sure why it doesn't work here. Should just work fine on V2 as well right?
    – John
    Mar 14, 2013 at 16:52
  • You mean you don't get any error messages? According to Don Jones, Microsoft PowerShell guru, if a cmdlet doesn't produce output nothing will appear on the screen. Same with a pipe line. Whatever the last command in a pipeline outputs you get that on the screen.
    – Darius
    Mar 14, 2013 at 17:03
  • How does it not seem to work? What are you doing with the imported CSV? Are you assigning it to another variable and then trying to list it's output and the output is blank? Are you using the ISE or are you running directly from the shell?
    – Josh
    Mar 14, 2013 at 17:08
  • I just tried it in V2 and it worked for me as well.
    – Josh
    Mar 14, 2013 at 17:12

2 Answers 2

0

The pastebin appears to have been removed, so I can't comment on the full script. However, you might want to try the following:

  • For any text-only strings being used as parameters in your script, use single-quotes ' '.
  • For any strings containing variables which are used as parameters in your script, use double-quotes " ".

For example, the first two command lines you give in your question should be:

$path = 'C:\Users\Admin\Desktop\TestFolder\file.txt'
Import-CSV -Header ('header1','header2','header3') -Path "$path"
0

Please try this way:

foreach($file in dir $CodesetBulkLoadLocation -Recurse -Include @("*.csv"));
{

#$files = @{
#'ICD10' = @{ 'EDIT_FUNCTION'='UCM-REPT-TEEN-PREG-DIAG'; 'TABLE_FUNCTION'='UCM-REPT'; 'CODE_FUNCTION' = 'I0' }
#}
#foreach($file_desc in $files.GetEnumerator())
#{
$filename = $file_desc.Key
$edit_func = $file_desc.Value.EDIT_FUNCTION
$table_func = $file_desc.Value.TABLE_FUNCTION
$code_func = $file_desc.Value.CODE_FUNCTION

$filePath = "C:\jobs\AA\DEV6\UCM\${filename}.csv"
$importFile = Import-CSV $filePath
foreach($item in $importFile)
    {
   $proc1 = $item.DIAG_NBR
   $proc2 = $item.DIAG_NBR
  # $ymdeff = $item.YMDEFF
   #$ymdend = $item.YMDEND
   #$ymdtrans = $item.YMDTRANS

   Write-Verbose "$proc1, $proc2"

   $currentDate = Get-Date -Format yyyyMMdd


# *******************************************************************************
# * Insert codes and fees into the su_edit_detail.
# *******************************************************************************

'{0,-60}{1,20}' -f "Insert TEEN PREGNANCY  ICD9 AND ICD10 CODES  into the su_edit_detail ",(Get-Date -Format yyyyMMdd:hhmmss);

$myQuery = @"
SET PAGES 600;
SET LINES 4000;
SET ECHO ON;
SET serveroutput on;

WHENEVER sqlerror exit sql.sqlcode;

insert into SU_EDIT_DETAIL(EDIT_FUNCTION, TABLE_FUNCTION, CODE_FUNCTION, CODE_TYPE,CODE_BEGIN, CODE_END, EXCLUDE, INCLUDE_X, OP_NBR, TRANSCODE, VOID, YMDEFF, YMDEND, YMDTRANS)
 select '$edit_func','$table_func','$code_func','DIAG','$proc1','$proc2',' ',' ','MIS', 'C',' ',20141001, 99991231, 20131120
from dual where not exists(select * from SU_EDIT_DETAIL where (EDIT_FUNCTION = '$edit_func' and TABLE_FUNCTION = '$table_func' and CODE_BEGIN='$proc1' and CODE_END='$proc2'));
commit;               
"@
$null = Invoke-SqlPlus -MessageOnFail 'Insert into su_edit_detail failed' -Query $myQuery;

}
}
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .