none
Powershell to import csv file

    Question

  • Hello all,

    Running sql 2008 sp1

    New to PowerShell. Can someone please check the syntax and tell me if something doesn't look right.

    Trying to load a csv file into a table

    The csv file is comma delimited and contains double quotes around values that have a comma in them.

            $Server = "MyServer"
            $Database = "MyDatabase"
            $Table = "MyTable"
            $ShareUNCPath = "\\MyShareUNCpath\MyTestfile.csv"
            $DataImport = Import-Csv -Path $ShareUNCPath
            $DataTable = Out-DataTable -$DataImport
            Write-DataTable -ServerInstance $Server -Database $Database -TableName $Table -Data $DataTable

    Thanks

    gv


    Sword

    Wednesday, January 23, 2013 7:43 PM

All replies

  • Hi,

    What happens when you run it? Without setting up a test environment for myself I cannot determine any error you may encounter. At this time I cannot test this but I ran into a similar scenario that involved multiple files and the link below was able to get me through.

    http://www.sqlserver-dba.com/object-management/page/2/

    There is some good scripts and examples in this link. Can't remember where I found the examples for importing multiple files but it is in there.

    You may reference this until an MVP or MSFT Eng replies.

    Frank.


    Frank Garcia

    Wednesday, January 23, 2013 8:09 PM
  • http://www.sqlserver-dba.com/2012/07/powershell-scripts-for-dba.html

    Frank Garcia

    Wednesday, January 23, 2013 8:10 PM
  • Hello Sword,

    The syntax looks good, that far. You want to use the Write-DataTable CmdLet from TechNet ScriptCenter? I never tested it, but I also will give it a try, when I have time to test it.


    Olaf Helper

    Blog Xing

    Thursday, January 24, 2013 4:52 PM
  • This is really a PowerShell question, not a SQL Server question.   You might get better results from the PowerShell forums.

    Thursday, January 24, 2013 5:15 PM
  • Hello,

    I guess I need to step back a little here... yes I want to use "Write-DataTable" cmdlet.

    How do you add a function to powershell to be able to use it from a sql job or query window?

    located example here : \\Myserver\MyShareDrive\MyFunctions\Functions.ps1 (contains the Write-Datatable function)

    I'm always logging into powershell from ssms as well as running commands from query.

    like so:

    xp_cmdshell 'PowerShell.exe -noprofile Get-Service' -- this works

    so with the above function I will be using in a job.

    thanks,

    gv


    Sword

    Friday, January 25, 2013 1:18 PM
  • Sword, did you try this link to try to find an example?

    http://www.sqlserver-dba.com/2012/07/powershell-scripts-for-dba.html

    Frank


    Frank Garcia

    Friday, January 25, 2013 1:26 PM
  • Frank,

    yes I went there but, I guess I didn't spend enough time looking around. was moving in a lot of different directions with other stuff

    and never went back to that link. I will look there some more today.

    thanks everyone for your help...


    Sword

    Friday, January 25, 2013 1:36 PM
  • You cannot vote on your own post
    0

    Not really..since I'm launching powershell from sql server and running powershell commands using sql xp_cmdshell.

    And that the first thing I did before, I posted here was look for forums under powershell with no luck that put me right here.

    so I went to forum home:

    http://social.msdn.microsoft.com/Forums/en-US/categories

    typed in "powershell" walla no results...so no go on that idea.


    • Edited by gv-sword Friday, January 25, 2013 1:38 PM
    Friday, January 25, 2013 1:37 PM
  • Check out this thread if it is that I am following your question correctly; you are looking for exec of ps from xp_cmdshell.

    http://stackoverflow.com/questions/7341763/running-powershell-scripts-through-sql

    Frank.

    Frank Garcia

    Friday, January 25, 2013 1:39 PM
  • I can run ps using xp_cmdshell....not the problem.

    My initial question\ posted was to import csv file into table using powershell.

    What I didn't mention was more details: sorry...

    I'm trying to import large csv files with over million rows in each file.

    this would be very easy using bcp or bulk insert but, there is a problem.

    The csv file are comma delimited and double quotes as text qualifier for string values for cases with delimiter within the text.

    Will that still should be no problem using a format file but, the vender leaves off the double quotes if the value is NULL...

    so the column is not consistent. (Fail by the vender and out of my control)

    This brings me to look for another solution short of writing a vb script to do it. Didn't want to go that road unless I had to.

    thanks again for help

    gv


    Sword


    • Edited by gv-sword Friday, January 25, 2013 1:54 PM
    Friday, January 25, 2013 1:53 PM
  • I would have to create an example and play around with it. I will get back to you hopefully today. If not that link I sent you has an email address to contact the person that created all of the scripts.

    http://www.sqlserver-dba.com/2012/07/powershell-scripts-for-dba.html

    You may contact him as well to see if he can give you some advice.

    Olaf may reply soon as well so hang tight.

    Frank


    Frank Garcia

    Friday, January 25, 2013 2:46 PM
  • Sword,

    I followed this post exactly and I was successful. Of course, my csv files contained about 100 records each but I tried to emulate your scenario.

    Please follow the instructions in this link and see if you can be successful in doing your import.

    This is the best I can do at this point.

    http://blog.sqlauthority.com/2012/06/27/sql-server-powershell-importing-csv-file-into-database-video/

    Frank


    Frank Garcia

    Friday, January 25, 2013 4:05 PM
  • Ok.. will try and get some testing done today or tomorrow and then give some feedback...

    Thanks all for your help!!!

    gv


    Sword

    Monday, January 28, 2013 4:24 PM
  • Thanks again for all your help...

    this is where I get stumped from that link.

    Save it in a module and add it in your profile. In my case, the module is called functions.psm1.

    ok I can create a file called functions.psm1 and save it to a shared location as it says...

    Add it to your profile? ok this is where I'm lost.....

    I open up PowerShell from within SSMS not a PowerShell editor. why do I need to worry about profile?

    what is profile? etc....

    thanks

    gv


    Sword

    Tuesday, January 29, 2013 6:53 PM
  • using SQL 2008 sp1

    Ok, I ran the following in query window in SSMS and getting the following error(see below at bottom):

    was getting a different error when I didn't include column names like so

    "....Cannot process argument because the value of argument “name” is invalid......"

    Note: Need to change server name and path in code to match your location

    USE [AdventureWorks]
    GO
    
    SET NOCOUNT ON
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
    
    CREATE TABLE [dbo].[MyTable]
    (	[Col1]		INT NOT NULL,
    	[Col2]		INT NOT NULL,
    	[Col3]		INT NOT NULL,
    	[Col4]		TINYINT NOT NULL,
    	[Col5]		INT NOT NULL,
    	[Col6]		SMALLINT NOT NULL,
    	[Col7]		INT NOT NULL,
    	[Col8]		INT NULL,
    	[Col9]		INT NULL,
    	[Col10]		DATETIME NOT NULL,
    	[Col11]		VARCHAR(20) NOT NULL,
    	[Col12]		VARCHAR(10) NULL,
    	[Col13]		INT NOT NULL,
    	[Col14]		SMALLINT NOT NULL,
    	[Col15]		TINYINT NULL,
    	[Col16]		MONEY NULL,
    	[Col17]		MONEY NULL,
    	[Col18]		VARCHAR(50) NULL,
    	[Col19]		SMALLINT NULL,
    	[Col20]		SMALLINT NULL,
    	[Col21]		VARCHAR(50) NULL,
    	[Col22]		SMALLINT NULL, -- 22
    	[Col23]		VARCHAR(50) NULL,
    	[Col24]		VARCHAR(50) NULL,
    	[Col25]		VARCHAR(50) NULL,
    	[Col26]		SMALLINT NULL,
    	[Col27]		SMALLINT NULL,
    	[Col28]		VARCHAR(50) NULL,
    	[Col29]		SMALLINT NULL,
    	[Col30]		VARCHAR(50) NULL,
    	[Col31]		VARCHAR(50) NULL,
    	[Col32]		VARCHAR(50) NULL,
    	[Col33]		INT NOT NULL,
    	[Col34]		INT NULL,
    	[Col35]		VARCHAR(50) NULL,--35
    	[Col36]		VARCHAR(50) NULL,
    	[Col37]		VARCHAR(50) NULL,
    	[Col38]		INT NULL,
    	[Col39]		MONEY NULL,
    	[Col40]		MONEY NULL, --40
    	[Col41]		VARCHAR(15) NULL,
    	[Col42]		VARCHAR(19) NULL,
    	[Col43]		VARCHAR(15)NULL,
    	[Col44]		INT NULL,
    	[Col45]		TINYINT NULL,
    	[Col46]		INT NULL,
    	[Col47]		VARCHAR(15)NULL,
    	[Col48]		VARCHAR(15)NULL,
    	[Col49]		TINYINT NULL,
    	[Col50]		INT	NULL,
    	[Col51]		TINYINT	NULL,
    	[Col52]		BIT	NULL,
    	[Col53]		VARCHAR(16)NULL )
    	
     ON [PRIMARY]
    
    DECLARE @SQL VARCHAR(2000)
    DECLARE @ColNames VARCHAR(2000) 
    DECLARE @CRLF CHAR(1) = CHAR(13) + CHAR(10)
    
    SET @ColNames =	   '("Col1","Col2","Col3","Col4","Col5","Col6,"Col7","Col8","Col9","Col10", ' + @CRLF +
    				   '"Col11","Col12","Col13","Col14","Col15","Col16","Col17","Col18","Col19","Col20","Col21",'+ @CRLF +
    				   '"Col22","Col23","Col24","Col25","Col26","Col27","Col28","Col29","Col30","Col31","Col32",'+ @CRLF +
    				   '"Col33","Col34","Col35","Col36","Col37","Col38","Col39",'+ @CRLF +
    				   '"Col40","Col41","Col42","Col43","Col44","Col45","Col46","Col47","Col48",'+ @CRLF +
    				   '"Col49","Col50","Col51","Col52","Col53")'				  
    
    --PRINT @ColNames				  
    
    SET @SQL =  'exec master.dbo.xp_cmdshell ''' + @CRLF + '' +
    			' PowerShell ' + @CRLF + 
    			'$Server = "MyServer" ' + @CRLF +
                '$Database = "AdventureWorks" ' + @CRLF +
                '$Table = "MyTable" ' + @CRLF +          
                '$ShareUNCPath = "\\MyShareFolder\MyPath\mycsvfile.csv"' + @CRLF +
                '$DataImport = Import-Csv -Path $ShareUNCPath -header ' + @ColNames + '' + @CRLF +
                '$DataTable = Out-DataTable -$DataImport' + @CRLF + 
                'Write-DataTable -ServerInstance $Server -Database $Database -TableName $Table -Data $DataTable' + @CRLF +
                ''''
    --PRINT @sql           
    EXEC (@sql)

    Create a csv file called "mycsvfile.csv" and put this in there: should be 8 lines. (I changed data for this example)

    13925195,1832,65,1,3085,5,9643,2765490,32,2012-12-17 11:32:12.000,"8881112553",,390,19045,22,.0000,.1900,"New York",132,212,"NY",0,"8886092553","9104","Boston",128,617,"MA",0,"8882830179","4036","0",387,365559,,,,390,.1900,.0000,,,,,,,,,,,,,
    18537771,1829,23,1,3085,4,9643,2765490,32,2012-12-17 11:38:45.000,"8881112553",,42,19045,21,.0000,.1400,"New York",132,212,"NY",0,"8886092553","9104","Devonshire",830,441,"BM",0,"8882955929","8905","0",38,360955,,,,42,.1400,.0000,,,,,,,,,,,,,
    13902201,132,71,1,3085,5,9643,2765490,32,2012-12-17 11:40:54.000,"8886092553",,120,19045,22,.0000,.0600,"New York",132,212,"NY",0,"8886092553","9104","Boston",128,617,"MA",0,"8882830179","4036","0",118,553359,,,,120,.0600,.0000,,,,,,,,,,,,,
    13837775,1829,22,1,3085,4,9643,2765490,0,2012-12-17 11:45:27.000,"8886092553",,84,19045,21,.0000,.0800,"New York",132,212,"NY",0,"8886092553","9104","Jersey, Channel",0,0,,44,"8884605755",,"0",81,551292,,,,84,.0800,.0000,,,,,,,,,,,,,
    13901871,1864,58,1,3085,11,9643,2765490,32,2012-12-17 12:02:56.000,"8886092553",,60,19045,24,.0000,.0000,"New York",132,212,"NY",0,"8886092553","9104","New York",132,917,"NY",0,"8885329645","6664","0",3,355713,,,,60,.0000,.0000,,,,,,,,,,,,,
    13905248,1832,74,1,3085,5,9643,2765490,32,2012-12-17 12:17:57.000,"8886092553",,246,19045,22,.0000,.1200,"New York",132,212,"NY",0,"8886092553","9104","Beverlyhls",730,310,"CA",0,"8882721242","6232","0",246,553359,,,,246,.1200,.0000,,,,,,,,,,,,,
    13885830,1829,27,1,3085,5,9643,2765490,32,2012-12-17 12:57:23.000,"8886092553",,2646,19045,22,.0000,1.2800,"New York",132,212,"NY",0,"8886092553","9104","Boston",128,617,"MA",0,"8887201617","9102","0",2646,363559,,,,2646,1.2800,.0000,,,,,,,,,,,,,
    13837829,129,26,1,3085,4,9643,2765490,0,2012-12-17 12:59:05.000,"88892553",,2544,19045,21,.0000,2.5400,"New York",132,212,"NY",0,"88892553","9104","Jersey, Channel",0,0,,44,"8884605755",,"0",2544,361292,,,,2544,2.5400,.0000,,,,,,,,,,,,,

    Error:

    The term 'MyServer' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name,
    or if a path was included, verify that the path is correct and try again.
    At line:1 char:18
    + $Server = MyServer <<<<  $Database = AdventureWorks $Table = MyTable $ShareUNC Path = \\mysharefolder\mypath\mycsvfile.csv$Dat
    aImport = Import-Csv -Path $ShareUNCPath -header (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10, Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Co
    l20,Col21,Col22,Col23,Col24,Col25,Col26,Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Col40,Col41,Col42,Col43,Col44,Col45,Col4
    6,Col47,Col48,Col49,Col50,Col51,Col52,Col53)
    $DataTable = Out-DataTable -$DataImportWrite-DataTable -ServerInstance $Server -Database $Database -TableName $Table -Data $DataTable
        + CategoryInfo          : ObjectNotFound: (MyServer:String) [], CommandNotF
       oundException
        + FullyQualifiedErrorId : CommandNotFoundException
     
    NULL

    thanks

    gv


    Sword

    Wednesday, January 30, 2013 6:13 PM
  • Hi Sword,

    I will look at this in my test environment. Right now I cannot dedicate time to testing this because I am tied up with projects but I would encourage you to break this down to it's simplest setup and test one line to import. Isolating is always the way to get to the snafu.

    I will get back to you hopefully by tomorrow morning or Friday the latest.

    Frank


    Frank Garcia

    Wednesday, January 30, 2013 6:26 PM
  • Sword, I haven't forgotten about this... I have been really busy with projects but so far with the example you sent me I am having the same issue you are having. I am comparing it to my data etc. Please give til next week.

    Frank.


    Frank Garcia

    Friday, February 01, 2013 3:49 PM
  • Thanks Frank !! no problem....


    Sword

    Friday, February 01, 2013 6:54 PM
  • Sword,

    You have to create a $Profile and then save the modules in there. This is what I did and it worked fine.

    The modules can be downloaded at http://poshcode.org/2118 and http://poshcode.org/2119.

    This is the error you are receiving. There are no cmdlets created in the profile.

    Frank.


    Frank Garcia

    Tuesday, February 05, 2013 10:48 PM