My objective is to be able to delete ranges of all the records (ex. "Name" and "Extension"), and delete any record where any of the fields contain a given string (ex. "N/A" and/or "??" and/or " ").
I have found the following code to start, but now am lost. Not even sure that this is the best starting point.
Partial Solution, by Mardmac, for anyone searching for similar solution
The following VBScript will delete FIELDS (example 10 and 3) within the CSV file C:\TEST.csv
'On Error Resume Next Dim objFSO, dataArray, clippedArray() Set objFSO = CreateObject("Scripting.FileSystemObject")
'Create an array out of the CSV 'open the data file Set oTextStream = objFSO.OpenTextFile("C:\test.csv") Set newFile = objFSO.CreateTextFile("C:\newCSV.csv") 'make an array from the data file dataArray = Split(oTextStream.ReadAll, vbNewLine) 'close the data file oTextStream.Close
x = 0 For Each strLine In dataArray 'Now make an array from each line ReDim Preserve clippedArray(x) clippedArray(x) = Split(strLine,",")
' Delete Field 10 and 3 (highest to lowest) CutColumn = 10 CutColumn = 3
intCount = 0 newLine = "" For Each Element In clippedArray(x) If intCount = UBound(clippedArray(x)) Then EndChar = vbCrLf Else EndChar = "," End If
If intCount <> CutColumn -1 Then newLine = newLine & Element & EndChar End If intCount = intCount + 1 If intCount = UBound(clippedArray(x))+1 Then newFile.Write newLine End If Next
Next
Now the simple question; Is it even possible to redefine field 2 as field 6, etc?
Thanks in advance
k
< Message edited by kriemer -- 10/7/2008 9:49:23 PM >
Once again I feel like the South end of a horse facing North, but I have to ask, how do I use your script?
Could you explain your code a bit? For example:
Since I'm not too bright could you explain how the code deletes records based on N/A or "N/A"?
Am I correct in my understanding that your code appends the headers to kriemer02.csv (just trying to demonstrate I'm not a complete dope, though I think I failed)?
to use the script you either read it carefully - without executing - and and adapt it to your needs or you set up a suitable environment and execute it (preferably in a debugger) to understand it better. What you need is a directory (mine is .\adotext) containing a schema.ini file and the sample .csv (kriemer01.csv) you posted. Put the definitions for kriemer01.txt and kriemer02.txt in zhe schema.ini file (perhaps after finetuning the data types).
we see, that the ADO driver understands/translates "N/A" as NULL (missing). So to exclude rows with "N/A" the where clause of the SQL statement checks for (Not) NULL:
SELECT [TKR],[Date],[Open],[High],[Low],[Close] INTO [kriemer02.csv] FROM [kriemer01.csv] WHERE NOT ([TKR] IS NULL OR [Name] IS NULL OR [Close] IS NULL OR [Date] IS NULL OR [Time] IS NULL OR [Change] IS NULL OR [Open] IS NULL OR [High] IS NULL OR [Low] IS NULL OR [Volume] IS NULL )
Ehvbs's solution is quite elegent and I took the liberty of putting it into a form that would allow the script to be called, passing the source and destination as arguments.
If you were to copy the following code into a script file and name it CVS.vbs, for example, you could call it from a batch file or command line in the following way ...
csv.vbs "tst1.csv" "tst6.csv"
...with tst1.csv being used as the source and tst6.csv being the new CSV file. In the above example, the source CSV file would be expected to be in the same directory as the script. However, a full path can be passed, allowing for the use of a source in another directory, like this...
csv.vbs "c:\temp\tst1.csv" "tst6.csv"
One thing to keep in mind is that the destination file must be in the same directory as the source (at least that is my understanding).
< Message edited by wjcott -- 10/8/2008 2:44:47 PM >
And now the inevitable part n of my question. Can you recommend a book to gain a better understanding of VBS? Googleling helps some, but I am thinking of "start here", then ... kind of book.
What I know is almost entirely from the school of hard knocks, initially starting with VBA and MSOffice. I would mostly recommend hanging out in forums like this one, as you likely to see practical problems addressed that may be missed or considered too complicated for books. If you prefer to do some initial learning through a book, I have enjoyed the WROX press books (i.e. http://www.bookpool.com/sm/0470168080) that I have used for other subjects.There are some more subject specifc books, such as working with VBScript, ActiveDirectory, and WMI (i.e. http://www.bookpool.com/sm/0321501713), so your purposes may have an impact on he books to read.
PS. I would also recommend looking over the ASP references and tutorials at w3schools.com (http://www.w3schools.com/asp/default.asp & http://www.w3schools.com/ado/default.asp), as ASP web pages are often created using VBScript. Understanding the filesystem, textstream, file, folder, and ADO objects will get you a long way in creating solutions.
< Message edited by wjcott -- 10/8/2008 2:44:08 PM >
Unfortunately, besides the decimal precision, I could find no way of accomplishing the changes through the schema.ini and SQL. While it loses the elegance of the original solution, instead of having the ADO create the output file, I have added scripting to process the returned recrdset and create the output file. I am not entirely certain what the 4th item means, so this does not address that.
Your solution works, except numbers are still set to 6 significant decimal places
Item 4 is to append a phony record, which I call DAMN to the modified file <<<SOLVED
Thanks again (hoping to get smarter ASAP)
K
My solution to Item 4 is not elegant but it has the merit of working (and I found it myself!!!)
Inserted at end of wjcott's code:
Const FOR_READING = 1 Const FOR_WRITING = 2 Set objFS = CreateObject("Scripting.FileSystemObject") Set objTS = objFS.OpenTextFile("c:\b.csv",FOR_READING) strContents = objTS.ReadAll objTS.Close Set objTS = objFS.OpenTextFile("c:\b.csv",FOR_WRITING) objTS.WriteLine "DAMN,,,,,," objTS.Write strContents
Trying to understand how the coding that passes the source and destination file(s) works; actually looking to hard code these and having a surprising amount of difficulty doing so.
< Message edited by kriemer -- 10/10/2008 3:11:47 AM >
All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBScript to modify csv file 
Printable Version
VBScript to modify csv file - 
New Messages
No New Messages