batch to remove lines: XL-style csv

jon_k January 30, 2009 at 04:03:05
Specs: Windows XP
hello all

Got a problem with an "excel style" csv. All the fields are separated by commas, but certain fields (fields 1, 2, 7 and 10) have double-quotes around them and MAY contain commas.

Basically I'm trying to take a file (C:\stk_adj.csv) and making a copy that has only one branches stock adjustments. This is so the file can be manipulated in excel 2003 and emailed without being too big. (current csv file (all branches) sits at in excess of 100000 lines). Here is my code as it stands:

setlocal enabledelayedexpansion
echo just one branch grabber
echo.
echo.
echo ensure you've saved the stock adjustment full csv to c:\stk_adj.csv
echo.
echo.
echo enter the new filename (suggest using temp). This will delete any csv file with the same name in your root c: drive
echo.
echo.
SET /P varnewfile=New file name (no dots, slashes or spaces):^>
echo.
echo.
echo.
echo now enter the branchname. This has to be EXACTLY as it appears in iRetail
echo.
echo.
SET /P branchname=Branch name:^>
echo. > c:\%varnewfile%.csv
FOR /F "tokens=1,2,3,4,5,6,7,8,9,10,11,12 delims=," %%I IN (c:\stk_adj.csv) do (IF %%I=="%branchname%" echo %%I,%%J,%%K,%%L,%%M,%%N,%%O,%%P,%%Q,%%R,%%S,%%T>>c:\%varnewfile%.csv)
cmd

Think I have 2 issues at the moment.

1) If a text field contains commas, I get an extra separator and can miss some data.
2) If a field contains no data (i.e. the csv file reads ,,), I'm getting nothing posted so that data appears one column short once I get it into excel.

Hope someone can help!


See More: batch to remove lines: XL-style csv

Report •


#1
February 2, 2009 at 07:52:28
I hate to ask this, but if it's "Excel style," why not just automate Excel to do your logic?

VBScript:

Main
WScript.Quit 0

Function GetInput(prompt)
  GetInput = InputBox(prompt)
  If GetInput = "" Then
    WScript.Echo "Aborted"
    WScript.Quit 1
  End If
End Function

Sub Main
  varnewfile = GetInput("New file name (no dots, slashes or spaces)")
  branchname = GetInput("Branch name")
  If InStr(1, Right(varnewfile, 4), ".csv", 1) = 0 Then _
    varnewfile = varnewfile & ".csv"
  
  With CreateObject("Excel.Application")
    .DisplayAlerts = False
    .Visible = False 'Set to True for debugging
    .Workbooks.OpenText "c:\stk_adj.csv", 2
    Set oldCSV = .Workbooks(.Workbooks.Count).Sheets(1)
    .Workbooks.Add
    Set newCSV = .Workbooks(.Workbooks.Count).Sheets(1)
    
    'There's probably a built-in function to do this, but I can't find it.
    For Each c In .Intersect(oldCSV.UsedRange, oldCSV.Columns("A")) 
      If c = branchname Then _
        c.EntireRow.Copy newCSV.Cells.SpecialCells(11).EntireRow.Offset(1, 0)
    Next 'c
    
    'Cleanup and save
    newCSV.Rows(1).Delete
    newCSV.SaveAs "C:\" & varnewfile, 6
    .Quit
  End With
End Sub


Report •

#2
February 3, 2009 at 06:31:21
Thanks for that, it works fine on my machine with excel 2007, but it doesn't work on a machine with excel 2003.

My guess is this is because the fixed limit for number of rows in excel 2003 is 65536, and the branch I want is on row 90,000+

Which is why I can't use Excel as a "holding point" for my csv file. I know there is text file manipulation in VBA but can't remember how to do it. Any ideas?


Report •

#3
February 6, 2009 at 23:50:06
Well, you can try this Excel free variant, I just don't know how long it'll take to run. (You should get a dialog box when it finishes.)
Main
WScript.Echo "Done"
WScript.Quit 0

Function GetInput(prompt)
  GetInput = InputBox(prompt)
  If GetInput = "" Then
    WScript.Echo "Aborted"
    WScript.Quit 1
  End If
End Function

Sub Main
  varnewfile = GetInput("New file name (no dots, slashes or spaces)")
  branchname = GetInput("Branch name")
  If InStr(1, Right(varnewfile, 4), ".csv", 1) = 0 Then _
    varnewfile = varnewfile & ".csv"
  
  With CreateObject("Scripting.FileSystemObject")
    Set inFile  = .OpenTextFile("c:\stk_adj.csv")
    Set outFile = .OpenTextFile("C:\" & varnewfile, 2, True)
  End With
  
  On Error Resume Next
  Do Until inFile.AtEndOfStream
    line = inFile.ReadLine
    If Split(line, ",", 2)(0) = branchname Then _
      outFile.WriteLine line    
  Loop
End Sub


Report •

Related Solutions

#4
February 7, 2009 at 03:29:31
find "branchname" < bid csv > new.csv


=====================================
If at first you don't succeed, you're about average.

M2


Report •


Ask Question