locked
remove double quotes RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

           

      If my data has a delimiter of | and all fields have double quotes how do I remove double quotes during import-csv and load to SQL.


    # Open the text file from disk
$reader = new-object System.IO.StreamReader($csvfile)
$line = $reader.ReadLine()
$columns =  $line.Split($csvdelimiter)

      It imports the data to the table but with all the double quotes> It always thinks I have more columns in csv than SQL table has but they are the same.

    Thanks.
    Friday, August 15, 2014 2:20 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Try this:

    (Get-Content .\input.csv).Replace('"','') | Set-Content .\input.csv


Example:
PS C:\Scripting\Testing\8-14-2014> Import-Csv .\input.csv -Delimiter '|'

Header One                                                   Header Two                                                   Header Three                                               
----------                                                   ----------                                                   ------------                                               
Data One                                                     Data Two                                                     Data Three                                                 


PS C:\Scripting\Testing\8-14-2014> cat .\input.csv
"Header One"|"Header Two"|"Header Three"
"Data One"|"Data Two"|"Data Three"

PS C:\Scripting\Testing\8-14-2014> (Get-Content .\input.csv).Replace('"','') | Set-Content .\input.csv

PS C:\Scripting\Testing\8-14-2014> cat .\input.csv
Header One|Header Two|Header Three
Data One|Data Two|Data Three

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    • Proposed as answer by mjolinor Friday, August 15, 2014 2:46 AM
    • Marked as answer by AnnaWY Wednesday, September 10, 2014 5:27 AM
    Friday, August 15, 2014 2:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     V2

         (Get-Content"c:\Archive\codemstr.csv".Replace('"','') | Set-Content"c:\Archive\codemstr.csv")

     WIll I have to run this prior to my Import or can it be inserted when I'm reading each line from above post?

     Thanks.

    Yeah, that's not going to work. You need to surround Get-Content with parens so it reads the file and then closes it, so Set-Content can overwrite it.


    (Get-Content c:\Archive\codemstr.csv).Replace('"','') | Set-Content c:\Archive\codemstr.csv

    This should be run before the import.

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    • Edited by Mike Laughlin Friday, August 15, 2014 2:07 PM
    • Marked as answer by hart60 Friday, August 15, 2014 2:15 PM
    Friday, August 15, 2014 2:06 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Try this:

    (Get-Content .\input.csv).Replace('"','') | Set-Content .\input.csv


Example:
PS C:\Scripting\Testing\8-14-2014> Import-Csv .\input.csv -Delimiter '|'

Header One                                                   Header Two                                                   Header Three                                               
----------                                                   ----------                                                   ------------                                               
Data One                                                     Data Two                                                     Data Three                                                 


PS C:\Scripting\Testing\8-14-2014> cat .\input.csv
"Header One"|"Header Two"|"Header Three"
"Data One"|"Data Two"|"Data Three"

PS C:\Scripting\Testing\8-14-2014> (Get-Content .\input.csv).Replace('"','') | Set-Content .\input.csv

PS C:\Scripting\Testing\8-14-2014> cat .\input.csv
Header One|Header Two|Header Three
Data One|Data Two|Data Three

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    • Proposed as answer by mjolinor Friday, August 15, 2014 2:46 AM
    • Marked as answer by AnnaWY Wednesday, September 10, 2014 5:27 AM
    Friday, August 15, 2014 2:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     It gave message File in Use  .. I don't have file open ...

     Thanks.

    Friday, August 15, 2014 1:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    What verion of Powershell are you running?

    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Friday, August 15, 2014 1:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     It gave message File in Use  .. I don't have file open ...

     Thanks.

    Are you sure you surrounded Get-Content with parens? Please post exactly what you ran.

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Friday, August 15, 2014 1:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     V2

         (Get-Content"c:\Archive\codemstr.csv".Replace('"','') | Set-Content"c:\Archive\codemstr.csv")

     WIll I have to run this prior to my Import or can it be inserted when I'm reading each line from above post?

     Thanks.



    Friday, August 15, 2014 2:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     V2

         (Get-Content"c:\Archive\codemstr.csv".Replace('"','') | Set-Content"c:\Archive\codemstr.csv")

     WIll I have to run this prior to my Import or can it be inserted when I'm reading each line from above post?

     Thanks.

    Yeah, that's not going to work. You need to surround Get-Content with parens so it reads the file and then closes it, so Set-Content can overwrite it.


    (Get-Content c:\Archive\codemstr.csv).Replace('"','') | Set-Content c:\Archive\codemstr.csv

    This should be run before the import.

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    • Edited by Mike Laughlin Friday, August 15, 2014 2:07 PM
    • Marked as answer by hart60 Friday, August 15, 2014 2:15 PM
    Friday, August 15, 2014 2:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

       Perfect Thanks!!!

    Friday, August 15, 2014 2:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Cheers, you're very welcome.

    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Friday, August 15, 2014 2:17 PM