Power Shell convert from excel to csv

 $excelFile = "D:\Test\excelfile\file.xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false

 $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
    }

   
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "D:\Test\excelfile\" + $excelFileName + ".xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
ExportWSToCSV -excelFileName "file" -csvLoc "D:\Test\csv file\"

stop-process -processname EXCEL

$ens = Get-ChildItem "D:\Test\excelfile\" -filter *.xlsx
foreach($e in $ens)
{
    ExportWSToCSV -excelFileName $e.BaseName -csvLoc "D:\Test\csv file\"
}

Using Marco to save file to sharepoint document library :)

 

Just learnt something new from user, which is we can run macro then the excel file can save to SharePoint document library .

 

here is the code :

 

Sub TestSave()
     FolderPath = “http://sharepoint.abc.com/sites/abc/docmentLibrary/”
         SavePath = FolderPath & "sinpeow.xls"
     ActiveWorkbook.SaveAs Filename:=SavePath, FileFormat:=xlNormal
End Sub

Shorting the content DB from enumlist.txt

this morning try to get the URL and Database from enumlist for orphan object checking , but after import to excel with delimited but look like the information is not good enough. ha ha, then learnt some excel function to short it out. so want to keep track new excel command i learnt :

 

<Site Url=http://abc.abc.com Owner="abc\abc" ContentDatabase="MOSS_Content_DB1" StorageUsedMB="28.7" StorageWarningMB="80" StorageMaxMB="100" />

 

this command is trim up the front part

=RIGHT(A1,LEN(A1)-FIND("e=",A1))

 

Results is :

="MOSS_Content_DB1" StorageUsedMB="28.7" StorageWarningMB="80" StorageMaxMB="100" />

 

Another column  is trim out the left over

=LEFT(B2,(FIND("St",B2)))

 

Results is:

="MOSS_Content_DB1" S

 

At last replace the “ and “ S with space, then you will get the DB name :)

 

Maybe look stupid , but i get what i want!! i think will have more easy way , but i unable to think out yet. so i just use the faster skill i know to get my information i think this is fair.

 

please do share with me the easy way , if you have :)

Learn how to use excel generate Batch file

Some time we need to create some batch file with a lot command line from some list , example you have MOSS list in excel then you want add the parameter to stsadm then create batch file.

here is the some command i learn and able to help on the purpose :

=VLOOKUP ( what you want to look , look from where, which column you want the return, false value is exact value)

=REPLACE( start from, start from which location ,how many world u want replace, copy from where )

=CONCATENATE( "string", c1 (Colum parameter)  )
>>>>> results is string [c1 value]

VLOOKUP for you to join table

Just now try to gather all the DB size from DB and SharePoint CA, to join the data from DB and sharepoint at excel i use the VLOOKUP :

From CA Content DB

Database Name	Database Status	Current Number of Sites	Site Level Warning	Maximum Number of Sites
Contend_DB1	Started	4	8	10

From SQL Query > use this command at SQL query exec sp_databases

DB name	Size	MB	GB
Contend_DB1	165860032	161972.69	158.1764526

Combine the data in one excel sheer : DB size GB with following VLOOKUP
 =VLOOKUP(A2(this is the data you want to match), [the rage of the SQL query data],4(which role want to return),FALSE)

Database Name	DB Size GB	Database Status	Current Number of Sites	Site Level Warning	Maximum Number of Sites
Contend_DB1	158.1764526	Started	4	8	10