WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
UDF - UDS Library
List Manipulation

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Add Remove CSV Columns

Here's a quickie set of UDFs to work with a CSV file, so you can remove and add columns... 

#DefineFunction CSVFileToList(file, delimiter)
   ;   this retrieves the contents of a CSV file and inserts it into a user-specified delimited list...
   Terminate (StrLen(delimiter) > 1 || StrLen(delimiter) < 1, "CSVFileToList", "Invalid Delimiter")
   csv = FileGet(file)
   Return(StrReplace(csv, @CRLF, delimiter))
#EndFunction

#DefineFunction CSVFileLineCount(file, delimiter)
   Return(ItemCount(CSVFileToList(file, delimiter), delimiter))
#EndFunction

#DefineFunction CSVFileColumnDelete(data, deleteColumn, columnDelimiter, lineDelimiter)
   ; this removes the numbered column from a list of CSV data...
   newdata = ""
   For xx = 1 To ItemCount(data, lineDelimiter)
      line = ItemExtract(xx, data, lineDelimiter)
      newline = ""
      For yy = 1 To ItemCount(line, columnDelimiter)
         If yy == deleteColumn Then Continue
         newline = ItemInsert(ItemExtract(yy, line, columnDelimiter), -1, newline, columnDelimiter)
      Next
      newdata = ItemInsert(newline, -1, newdata, lineDelimiter)
   Next
   Return(newdata)
#EndFunction

#DefineFunction CSVFileColumnInsert(data, insertColumn, insertDataList, columnDelimiter, lineDelimiter)
   ;   this inserts a column into each row from a list of CSV data...
   ;    if you want the new items quoted, you'll either have to pass it in the list, or alter this function...
   Terminate(ItemCount(data, lineDelimiter) <> ItemCount(insertDataList, lineDelimiter), "CSVFileColumnInsert", "New data is not same length")
   newdata = ""
   For xx = 1 To ItemCount(data, lineDelimiter)
      line = ItemExtract(xx, data, lineDelimiter)
      For yy = 1 To ItemCount(line, columnDelimiter)
   ;   the lineDelimiters need to match in the data and the CSV...
         If yy == insertColumn Then line = ItemInsert(ItemExtract(xx, insertDataList, lineDelimiter), yy-1, line, columnDelimiter)
      Next
      newdata = ItemInsert(line, -1, newdata, lineDelimiter)      
   Next
   Return(newdata)
#EndFunction

InputCSVFile = "C:\Test\coding\ORIGINAL.CSV"
;OutputCSVFile = "C:\Test\coding\NEW.CSV"

;   get a count of the lines...
Message("Debug", CSVFileLineCount(InputCSVFile, @LF))

;   show the original csv use the LF so they'll show on separate lines for the message functions...
data = CSVFileToList(InputCSVFile, @LF)
Message("Debug", data)

;   delete a column...and show it...
data = CSVFileColumnDelete(data, 1, ",", @LF)
Message("Debug", data)

;   build a column in a list, use the same delimiter for each line...
NewData = StrReplace(`"A","B","C","D","E","F","G","H","I","J"`, ",", @LF)

;   insert the new column and show it...
data = CSVFileColumnInsert(data, 2, NewData, ",", @LF)
Message("Debug", data)

Exit
Article ID:   W16738

File Created: 2005:02:18:12:22:08
Last Updated: 2005:02:18:12:22:08