0

I have a CSV with invoice data - e.g.:

  • Invoice #
  • PO Ref
  • Customer Code
  • Customer Name
  • Document Date
  • Product Code
  • Product Name

This CSV will have varying amounts of lines per invoice, which relate to invoice produt lines, which may span multiple invoice numbers.

I need to be able to run a batch file to look at this CSV and split off into multiple CSVs based on the value in Col A - Invoice #.

Eg:

  • 111222,PO123,C100000,Test Store,1/1/15,AB1000,Test Soft Toy
  • 111222,PO123,C100000,Test Store,1/1/15,AB1001,Test Soft Toy1
  • 111222,PO123,C100000,Test Store,1/1/15,AB1002,Test Soft Toy2
  • 222111,PO321,C111000,My Store,1/10/15,AB1000,Test Soft Toy
  • 222111,PO321,C111000,My Store,1/10/15,AB1001,Test Soft Toy1
  • 222111,PO321,C111000,My Store,1/10/15,AB1002,Test Soft Toy2

The .bat should run and split the CSV into 2 separate CSV's:

CSV1:

  • 111222,PO123,C100000,Test Store,1/1/15,AB1000,Test Soft Toy
  • 111222,PO123,C100000,Test Store,1/1/15,AB1001,Test Soft Toy1
  • 111222,PO123,C100000,Test Store,1/1/15,AB1002,Test Soft Toy2

CSV2:

  • 222111,PO321,C111000,My Store,1/10/15,AB1000,Test Soft Toy
  • 222111,PO321,C111000,My Store,1/10/15,AB1001,Test Soft Toy1
  • 222111,PO321,C111000,My Store,1/10/15,AB1002,Test Soft Toy2

Getting software installed on my server is going to a hassle - as such, if there is anyway that this can be done without having to install software this would make me very happy.

I appreciate that there have been similar questions asked previously - however, I haven't been able to find out if there is another script I can run without installing Awk.

Regards Rick

Rick
  • 45
  • 7
  • 3
    SO is not a free coding service, so please show your efforts and describe problems you encounter... – aschipfl Dec 21 '15 at 03:55
  • Well to be honest - I have no idea where to start. I have very limited skills when it comes to this area - and I thought I would ask for help - happy to be pointed in the right direction. – Rick Dec 21 '15 at 04:07
  • A good starting point is `for /F` to read a text file line by line; it allows to extract the first token, which you could use as the file name for the output file; type `for /?` and `if /?` in command prompt and read the help text carefully; to writ a line to a file you need `>` redirection like `> file.txt echo some text` (`echo` prints `some text` on screen; the `>` makes `some text` to be written to file `file.txt` instead); to append to an existing file, use `>>`... – aschipfl Dec 21 '15 at 05:11
  • looks like a [duplicate](http://stackoverflow.com/q/33938425/2152082) – Stephan Dec 21 '15 at 06:26
  • @Stephan: I don't think so. That question just requested _two_ output files: with true/false values of one condition. In this problem there are as many "conditions" as different invoice numbers. – Aacini Dec 21 '15 at 12:52

2 Answers2

0
@ECHO OFF
SETLOCAL
SET "destdir=u:\test"
MD "%destdir%" 2>nul
FOR /f "tokens=1*delims=" %%a IN (
 'dir /b /a-d /o-d "%destdir%\NewsxpressInvoices_*.csv" ' ) DO (
  set "sourcefile=%%a"
  goto havefile)

:havefile
FOR /f "delims=" %%a IN (%destdir%\%sourcefile%) DO (
 FOR /f "tokens=1delims=," %%x IN ("%%a") DO (
 >>"%destdir%\inv%%x.csv" ECHO(%%a
 )
)

GOTO :EOF

You would need to change the setting of destdir to suit your circumstances.

I used a file named q34388332.txt containing your data for my testing.

Produces u:*invoicenumber*.csv

[Modified to include find-latest-NewsxpressInvoices_*.csv-file as sourcefile in place of testfile q34388332.txt]

[edit again to include explicit directoryname

suggestion: use different variables for the source and destination directorynames, just in case you want to change them later. ]

Magoo
  • 77,302
  • 8
  • 62
  • 84
  • Hi, this is good! Thank you! However, I date stamp my CSV files (it's a daily export) for example, the file name today would be NewsxpressInvoices_22_12_2015.csv - with your above script I tried to amend the file name to: NewsxpressInvoices*.csv but this did not work. Do you have any thoughts on this? It would be much appreciated. Regards Rick – Rick Dec 21 '15 at 21:54
  • Hi, the modified script now doesn't do anything when I run it. This is what I have: @ECHO OFF SETLOCAL SET "destdir=\\serveraddress\NEWSXPRESS\" MD "%destdir%" 2>nul FOR /f "tokens=1*delims=" %%a IN ( 'dir /b /a-d /o-d "\\serveraddress\NEWSXPRESS\NewsxpressInvoices_*.csv" ' ) DO ( set "sourcefile=%%a" goto havefile) :havefile FOR /f "delims=" %%a IN (%sourcefile%) DO ( FOR /f "tokens=1delims=," %%x IN ("%%a") DO ( >>"%destdir%\TEST_%%x.csv" ECHO(%%a ) ) GOTO :EOF – Rick Dec 22 '15 at 02:48
  • This is great! Thank you. Quick question though - can you please explain how you would go about changing the column that it looks for the values? At the moment it is using column A - but let's say I wanted to search in column B? – Rick Dec 22 '15 at 22:16
  • Change the `1` in `"tokens=1delims=,"` to 2 for column B, etc - but be careful. If you select a column containing invalid filename characters (like `/` for instance), the result may not be what you expect. – Magoo Dec 23 '15 at 02:36
0

This solution assumes that the input file is ordered by Invoice #:

@echo off
setlocal EnableDelayedExpansion

set /A n=0, invoice=0
for /F "tokens=1* delims=," %%a in (input.csv) do (

   rem Get the number of output file based on Invoice #
   set /A "n+=(invoice-%%a>>31)&1, invoice=%%a"

   rem Output this line to proper file
   >> output!n!.csv echo %%a,%%b

)
Aacini
  • 65,180
  • 12
  • 72
  • 108
  • Hi Aacini, Thanks for your effort. However this did not do anything for me - this is what I am running in my .bat file: @echo off setlocal EnableDelayedExpansion set /A n=0, invoice=0 for /F "tokens=1* delims=," %%a in (\\serveraddress\NewsxpressInvoices*.csv) do ( rem Get the number of output file based on Invoice # set /A "n+=(invoice-%%a>>31)&1, invoice=%%a" rem Output this line to proper file >> output!n!.csv echo %%a,%%b ) – Rick Dec 21 '15 at 22:08
  • Actually, sorry yes it does create multiple csv's - I just need the capacity to specify the output destination. Would I enter in the server address after the word output? When I initially commented - I tried to use the file name NewsxpressInvoices*.csv as I date stamp my CSV files as they are a daily export. Do you have any advice on the above? Your help thus far has been appreciated. – Rick Dec 21 '15 at 22:12