Join the Community! Creating your account only takes a few minutes.

I need to write a vbs or batch file that will programmatically open a generated CSV file, write a new line at the beginning of the file (this will be a standard string) and also write a new line at the end of the file that will include a count of the number of rows in the file.

I also see lots of mention of using PowerShell, but I've no experience with it and not sure if it will place nicely with SQL 2000 DTS.

23 Replies

#Create the variable to hold the full CSV, add Line1 to it$Full=@()$Full+=New-ObjectPSObject-Property@{username="user0"country="mex"employeenum=87654}#Now add the CSV to it$Full+=Import-Csvc:\utils\test.csv#Now add the count to the end$Full+=New-ObjectPSObject-Property@{username="Number Lines in CSV: $($Full.Count)"country=""employeenum=""}#Save the CSV$Full|Export-Csvc:\utils\testsaved.csv-NoTypeInformation

But something you said confused me. If all we're doing is changing a CSV why does playing nice with SQL 2000 DTS come into play?

I don't know enough about PowerShell to comment whether it can be executed in a DTS package - We're moving to SQL 2008 R2 sometime this year and I know SSIS is much more flexible, but DTS doesn't natively support PS.

It can do VBS via ActiveX or a batch via the Execute Process Task - it could probably trigger PS (and may be the ideal solution in he long run) but if it's outside the DTS Package, it's potentially unreliable.

#Create the variable to hold the full CSV, add Line1 to it$Full=@()$Full+=New-ObjectPSObject-Property@{username="user0"country="mex"employeenum=87654}#Now add the CSV to it$Full+=Import-Csvc:\utils\test.csv#Now add the count to the end$Full+=New-ObjectPSObject-Property@{username="Number Lines in CSV: $($Full.Count)"country=""employeenum=""}#Save the CSV$Full|Export-Csvc:\utils\testsaved.csv-NoTypeInformation

In this example, where you write the columns in, does it require all the column headers to be predefined - I understand they're just array variable names, but is the Export-Csv command going to automatically create headers?

The imported .csv has the headers, which must be on line two. Line 1 has a control value ("0") and a UID across two cells before the line break.

I've done the footer and header with rowcounts myself; it's easy if you load the results into temp tables first and then output from a temp table. The dynamic filename might be a little tricky but I think it can still be done strictly in TSQL.

However you want to approach it is up to you - I'm just saying if it was me this is what I would do.

Sure. To get a header/footer in the output, you could do something like this:

SQL

CREATEPROCEDUREyourProcedureNameASBEGIN--this is the query in your view, just with the added "INTO" lineSELECTyourColumnsINTO#tmpResultsFROMyourTablesWHEREyourColumns=yourConditionsCREATETABLE#tmpOutput(rowTypevarchar(1),--this is what you will sort by in the endyourColumnvarchar(50)--or whatever datatype you need)--this is the header recordINSERTINTO#tmpOutput(rowType,yourColumn)VALUES('a','yourStandardHeaderString')--these are the actual records from the viewINSERTINTO#tmpOutput(rowType,yourColumn)SELECT'b',#tmpResults.yourColumnsFROM#tmpResults--this is your footer record. (since rowtype is 'c' it will show up on the bottom once sorted)INSERTINTO#tmpOutput(rowType,yourColumn)SELECT'c',count(*)FROM#tmpResults--this is your output. (the rowtype column allows you to sort the records however you want them to show up in the results)SELECTyourColumnFROM#tmpOutputORDERBYrowTypeEND

Still rough, and more of an example of what can be done and not necessarily how you actually want to do it. I expect you'd want more logic around the reference numbers, UID's, etc. You also mentioned a custom output name which you'd have to code in there but this will show you how to manipulate the file the way you wanted.

Powershell

$InputPath="c:\utils\test.csv"$OutputPath="c:\utils\newtest.csv"$ControlUUID="0,27615,"$Ref="99"#Import CSV$CSV=Import-Csv$InputPath#Prepare the output file and export first line$ControlUUID>$OutputPath#Add the CSV to the new file$CSV|ConvertTo-Csv-NoTypeInformation>>$OutputPath#Now add the footer line"$Ref,$($CSV.Count+3),">>$OutputPath

All of this said, I actually agree with @Trunker that if you can keep it all "in-house" so to speak then doing it from SQL is the better way. Would be much faster then shelling out to Powershell and manually manipulating the file, and you would incur less technical debt.

Still, I enjoy doing different things with Powershell and this was a different challenge!! Not sure I'll ever encounter this kind of thing again but now I know how!! :)

You could also do it in a more "Powershell" way, by replacing the ">" with " | Out-File $OutputPath" and ">>" with " | Add-Content $OutputPath" but while doing some performance testing I found the > and >> to be very performant (my own word), and it's very easy to type so why not?

Still rough, and more of an example of what can be done and not necessarily how you actually want to do it. I expect you'd want more logic around the reference numbers, UID's, etc. You also mentioned a custom output name which you'd have to code in there but this will show you how to manipulate the file the way you wanted.

Powershell

$InputPath="c:\utils\test.csv"$OutputPath="c:\utils\newtest.csv"$ControlUUID="0,27615,"$Ref="99"#Import CSV$CSV=Import-Csv$InputPath#Prepare the output file and export first line$ControlUUID>$OutputPath#Add the CSV to the new file$CSV|ConvertTo-Csv-NoTypeInformation>>$OutputPath#Now add the footer line"$Ref,$($CSV.Count+3),">>$OutputPath

As you both mention, the Stored Procedure is likely the best method and I'll pursue that for the project, but I'll also persevere with PowerShell, as I need to learn it (we're upgrading from AD/Exchange 2003 some time this year, so it's pretty much a required string to my bow).

Dropping back into this after being heavily distracted with other work.

It seems that although the process is creating a file that 'seems' correct, it's actually an invalid CSV file.

Where I mentioned Excel needs to use the Import Wizard to generate a CSV, it seems this is the case everywhere. The file is not (for some unfathomable reason) a valid CSV file - it will not open correctly in any program that accepts CSV files.

Not sure what a "valid" CSV is. It's just a text file with the values seperated by a comma! But, in keeping with trying to figure out programs I have no idea what you're using or what the error message is, I did have one thought:

Powershell

"""$Ref"",""$($CSV.Count+3)"",""">>$OutputPath

For the last line. This will surround the $null values with double quotes, maybe that's what's screwing up your programs. That said I open up CSV's with Excel all the time that I've created with Powershell so not sure what the error is you're having? Don't bother with the Import Wizard either, just double click on the file and it'll open in Excel. You can "Save As" an XLSX file than.

Something that was happening when the 'Import-CSV' cmdlet was executed was generating what amounts to an invalid CSV. It wasn't just Excel kicking out to the Import Wizard, programs such as CSVed either refused to open the file and/or showed random characters in column A row 1. Open it all in a text editor though and it was fine!

The Import-CSV was still happening as a ghetto way to count the contents of the file, but what I needed to do was use the get-content cmdlet, which simply grabbed and dumped the raw content of the original file.

Move Post

Read these next...

We are an SMB with a lot of in-house-developed applications in Visual Basic. One of our programmers is leaving on short notice. I'm the network guy and I have little experience coding, but I would like to learn so I can understand and modify code as needed.

Can I just convert an existing Windows 7 install into a VM using VMware Converter and plug it into an ESXi install? And since the original physical install was done with an OEM disc, would the licensing still be valid?