Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sorting and grouping data in one file csv 1

Status
Not open for further replies.
DD56

DD56

IS-IT--Management
Feb 9, 2012
2
FR
Hello,
Sorry for my bad english...I want to generate a file CSV for sorting and grouping data in only one file csv according to the example below :

Input CSV File
***************************************
NUMCDE;REFERENCE;QTY
CDE111111; AAAAAAAAAAAAA;1
CDE111111; BBBBBBBBBBBBB;2
CDE111111; CCCCCCCCCCCCC;3
CDE222222; DDDDDDDDDDDDD;1
CDE222222; EEEEEEEEEEEEE;2
CDE222222; FFFFFFFFFFFFFFF;3
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;2
CDE333333; KKKKKKKKKKKKKK;3

Outpout CSV File
*****************************************
NUMCDE; QTY & REFERENCE
CDE111111; 1 x AAAAAAAAAAAAA 2 x BBBBBBBBBBBBB 3 x CCCCCCCCCCCCC
CDE222222; 1 x DDDDDDDDDDDDD 2 x EEEEEEEEEEEEE 3 x FFFFFFFFFFFFFF
CDE333333; 1 x GGGGGGGGGGGG 2 x HHHHHHHHHHHHH 3 x KKKKKKKKKKKKK

*********************************************
I took as a starting point the following script. but I do not find the solution but I do not find the solution for sorting, grouping and to write the final file…

Const ForReading = 1
'Create the file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
'open the data file
Set oTextStream = oFSO.OpenTextFile("I:\VP5\test_array1.csv", ForReading)
'make an array from the data file
CSVArray = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close
'Loop through the records
For Each ArticleLine In CSVArray
'Now looping through each agent line
ArticleArray = Split(ArticleLine,";")
NUMC = ArticleArray(0)
CODEART = ArticleArray(1)
DESIGN = ArticleArray(2)
NOMFRN = ArticleArray(3)
QTEART = ArticleArray(4)

'Bufferise dernier n° CDE - Désignation et quantité
PREC_NUMC = ArticleArray(0)
PREC_DESIGN= ArticleArray(2)
PREC_QTE= ArticleArray(4)


Next


Thank you in advance for your assistance
 
Sort by date Sort by votes
Assuming, the input file is sorted by first column, this script does the job:

control_break.vbs
Code: 
[COLOR=#0000ff]'-----------------------------------------------------------------------[/color]
[COLOR=#0000ff]' Writing summary report from sorted file using [/color]
[COLOR=#0000ff]' Single level control break technique[/color]
[COLOR=#0000ff]'-----------------------------------------------------------------------[/color]
[COLOR=#0000ff]'[/color]
[COLOR=#0000ff]'------------------------- main program --------------------------------[/color]
[COLOR=#0000ff]' initialize global variables[/color]
nr_records [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
current_line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
numcde [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
numcde_save [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
reference [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
qty [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
input_file [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"input.csv"[/color]
output_file [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"output.csv"[/color]

[COLOR=#0000ff]' create Dictionary object[/color]
[COLOR=#804040][b]set[/b][/color] hash [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Scripting.Dictionary"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff]' create File System object[/color]
[COLOR=#804040][b]set[/b][/color] Fso [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Scripting.FileSystemObject"[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#804040][b]const[/b][/color] ForReading [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]1[/color][COLOR=#804040][b],[/b][/color] ForWriting [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b],[/b][/color] ForAppending [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]8[/color] 
[COLOR=#0000ff]'open the input file for reading[/color]
[COLOR=#804040][b]set[/b][/color] oInFile [COLOR=#804040][b]=[/b][/color] fso[COLOR=#804040][b].[/b][/color]OpenTextFile[COLOR=#804040][b]([/b][/color]input_file[COLOR=#804040][b],[/b][/color] ForReading[COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff]'open the output file for writing[/color]
[COLOR=#804040][b]set[/b][/color] oOutFile [COLOR=#804040][b]=[/b][/color] fso[COLOR=#804040][b].[/b][/color]OpenTextFile[COLOR=#804040][b]([/b][/color]output_file[COLOR=#804040][b],[/b][/color] ForWriting[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]True[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]'for each line in the input file[/color]
wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"processing file: "[/color] [COLOR=#804040][b]&[/b][/color] input_file
[COLOR=#804040][b]do[/b][/color] [COLOR=#804040][b]while[/b][/color] [COLOR=#804040][b]not[/b][/color] oInFile[COLOR=#804040][b].[/b][/color]AtEndOfStream
  process_line 
  save_keys
[COLOR=#804040][b]loop[/b][/color]

[COLOR=#0000ff]'at end print summary[/color]
print_summary
[COLOR=#0000ff]'number of lines (without header)[/color]
wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"Number of records processed: "[/color] [COLOR=#804040][b]&[/b][/color] nr_records [COLOR=#804040][b]-[/b][/color] [COLOR=#ff00ff]1[/color] [COLOR=#804040][b]& _[/b][/color]
             [COLOR=#ff00ff]", output written to: "[/color] [COLOR=#804040][b]&[/b][/color] output_file 
[COLOR=#0000ff]'close the input file[/color]
oInFile[COLOR=#804040][b].[/b][/color][COLOR=#804040][b]close[/b][/color]
[COLOR=#0000ff]'close the output file[/color]
oOutFile[COLOR=#804040][b].[/b][/color][COLOR=#804040][b]close[/b][/color]

[COLOR=#0000ff]'release objects from memory[/color]
[COLOR=#804040][b]set[/b][/color] hash [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]nothing[/b][/color]
[COLOR=#804040][b]set[/b][/color] fso [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]nothing[/b][/color]

[COLOR=#0000ff]'----------------------------- subroutines -----------------------------[/color]
[COLOR=#804040][b]sub[/b][/color] process_line
  nr_records [COLOR=#804040][b]=[/b][/color] nr_records [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]

 [COLOR=#0000ff] 'read the line[/color]
  current_line [COLOR=#804040][b]=[/b][/color] oInFile[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]ReadLine[/color][COLOR=#804040][b]()[/b][/color]

  [COLOR=#804040][b]if[/b][/color] nr_records [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]1[/color] [COLOR=#804040][b]then[/b][/color]
   [COLOR=#0000ff] 'do not process 1.line which contains header[/color]
   [COLOR=#0000ff] 'write header ot the screen[/color]
    wscript[COLOR=#804040][b].[/b][/color]echo current_line
   [COLOR=#0000ff] 'write other header to the output file[/color]
    out_line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"NUMCDE; QTY & REFERENCE"[/color]
    oOutFile[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]WriteLine[/color][COLOR=#804040][b]([/b][/color]out_line[COLOR=#804040][b])[/b][/color]
    [COLOR=#804040][b]exit[/b][/color] [COLOR=#804040][b]sub[/b][/color]
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]

 [COLOR=#0000ff] 'get fields[/color]
  line_array [COLOR=#804040][b]=[/b][/color] [COLOR=#a020f0]split[/color][COLOR=#804040][b]([/b][/color]current_line[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]";"[/color][COLOR=#804040][b])[/b][/color]
  numcde [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]trim[/color][COLOR=#804040][b]([/b][/color]line_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]0[/color][COLOR=#804040][b]))[/b][/color]
  reference [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]trim[/color][COLOR=#804040][b]([/b][/color]line_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]1[/color][COLOR=#804040][b]))[/b][/color]
  qty [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]trim[/color][COLOR=#804040][b]([/b][/color]line_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]2[/color][COLOR=#804040][b]))[/b][/color] [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]0[/color]

 [COLOR=#0000ff] 'if not on first record[/color]
  [COLOR=#804040][b]if[/b][/color] numcde_save [COLOR=#804040][b]<>[/b][/color] [COLOR=#ff00ff]""[/color] [COLOR=#804040][b]then[/b][/color]
    process_control_break
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]

 [COLOR=#0000ff] 'print current line[/color]
   wscript[COLOR=#804040][b].[/b][/color]echo current_line
 [COLOR=#0000ff] 'wscript.echo "numcde = '" & numcde & "'; " & _[/color]
 [COLOR=#0000ff] '             "reference = '" & reference & "'; " & _[/color]
 [COLOR=#0000ff] '             "qty = '" & qty & "'"[/color]
               
 [COLOR=#0000ff] 'add elements to hash (i.e. dictionary)[/color]
  [COLOR=#804040][b]if[/b][/color] hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]exists[/color][COLOR=#804040][b]([/b][/color]reference[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
    hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]reference[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]reference[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]+[/b][/color] qty
  [COLOR=#804040][b]else[/b][/color]
    hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color]reference[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] qty
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]

[COLOR=#804040][b]sub[/b][/color] process_control_break
  [COLOR=#804040][b]if[/b][/color] numcde_save [COLOR=#804040][b]<>[/b][/color] numcde [COLOR=#804040][b]then[/b][/color]
    print_summary
  [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]

[COLOR=#804040][b]sub[/b][/color] save_keys
  numcde_save [COLOR=#804040][b]=[/b][/color] numcde
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]

[COLOR=#804040][b]sub[/b][/color] print_summary
    out_line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
    [COLOR=#a020f0]keys[/color] [COLOR=#804040][b]=[/b][/color] hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]keys[/color]
    [COLOR=#804040][b]for[/b][/color] i[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]0[/color] [COLOR=#804040][b]to[/b][/color] hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]count[/color][COLOR=#804040][b]-[/b][/color][COLOR=#ff00ff]1[/color]
      out_line [COLOR=#804040][b]=[/b][/color] out_line [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]" "[/color] [COLOR=#804040][b]&[/b][/color] hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]item[/color][COLOR=#804040][b]([/b][/color][COLOR=#a020f0]keys[/color][COLOR=#804040][b]([/b][/color]i[COLOR=#804040][b]))[/b][/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]" x "[/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#a020f0]keys[/color][COLOR=#804040][b]([/b][/color]i[COLOR=#804040][b])[/b][/color] 
    [COLOR=#804040][b]next[/b][/color]
    out_line [COLOR=#804040][b]=[/b][/color] numcde_save [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]";"[/color] [COLOR=#804040][b]&[/b][/color] out_line
   [COLOR=#0000ff] 'write to the output file[/color]
    oOutFile[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]WriteLine[/color][COLOR=#804040][b]([/b][/color]out_line[COLOR=#804040][b])[/b][/color]
   [COLOR=#0000ff] 'write to screen[/color]
    out_line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"* "[/color] [COLOR=#804040][b]&[/b][/color] out_line
    wscript[COLOR=#804040][b].[/b][/color]echo out_line
   [COLOR=#0000ff] 'remove all hash items[/color]
    hash[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]removeAll[/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]
Now, when we have the input file
input.csv
Code: 
NUMCDE;REFERENCE;QTY
CDE111111; AAAAAAAAAAAAA;1
CDE111111; BBBBBBBBBBBBB;2
CDE111111; CCCCCCCCCCCCC;3
CDE222222; DDDDDDDDDDDDD;1
CDE222222; EEEEEEEEEEEEE;2
CDE222222; FFFFFFFFFFFFFFF;3
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;2
CDE333333; KKKKKKKKKKKKKK;3
then after running he script
Code: 
c:\Work>cscript /NoLogo control_break.vbs
processing file: input.csv
NUMCDE;REFERENCE;QTY
CDE111111; AAAAAAAAAAAAA;1
CDE111111; BBBBBBBBBBBBB;2
CDE111111; CCCCCCCCCCCCC;3
* CDE111111; 1 x AAAAAAAAAAAAA 2 x BBBBBBBBBBBBB 3 x CCCCCCCCCCCCC
CDE222222; DDDDDDDDDDDDD;1
CDE222222; EEEEEEEEEEEEE;2
CDE222222; FFFFFFFFFFFFFFF;3
* CDE222222; 1 x DDDDDDDDDDDDD 2 x EEEEEEEEEEEEE 3 x FFFFFFFFFFFFFFF
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;2
CDE333333; KKKKKKKKKKKKKK;3
* CDE333333; 1 x GGGGGGGGGGGG 2 x HHHHHHHHHHHHHH 3 x KKKKKKKKKKKKKK
Number of records processed: 9, output written to: output.csv
it generates this output file
output.csv
Code: 
NUMCDE; QTY & REFERENCE
CDE111111; 1 x AAAAAAAAAAAAA 2 x BBBBBBBBBBBBB 3 x CCCCCCCCCCCCC
CDE222222; 1 x DDDDDDDDDDDDD 2 x EEEEEEEEEEEEE 3 x FFFFFFFFFFFFFFF
CDE333333; 1 x GGGGGGGGGGGG 2 x HHHHHHHHHHHHHH 3 x KKKKKKKKKKKKKK
But as I said above, I assumed that the file is sorted by 1.column i.e. NUMCDE, so I could use the technique called 'Control Break processing'.
In the case that it's not sorted, then this technique will not work.
Further, I used the data structure called 'Dictionary' (aka hash), which allow me very simple to add the QTYs for given REFERENCEs,
e.g. for this given file
input.csv
Code: 
NUMCDE;REFERENCE;QTY
CDE111111; AAAAAAAAAAAAA;1
CDE111111; BBBBBBBBBBBBB;2
CDE111111; CCCCCCCCCCCCC;3
CDE111111; AAAAAAAAAAAAA;4
CDE111111; BBBBBBBBBBBBB;5
CDE111111; CCCCCCCCCCCCC;6
CDE222222; DDDDDDDDDDDDD;1
CDE222222; EEEEEEEEEEEEE;2
CDE222222; EEEEEEEEEEEEE;2
CDE222222; FFFFFFFFFFFFFFF;3
CDE222222; FFFFFFFFFFFFFFF;3
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;2
CDE333333; KKKKKKKKKKKKKK;3
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;1
CDE333333; KKKKKKKKKKKKKK;1
after running the script
Code: 
c:\Work>cscript /NoLogo control_break.vbs
processing file: input.csv
NUMCDE;REFERENCE;QTY
CDE111111; AAAAAAAAAAAAA;1
CDE111111; BBBBBBBBBBBBB;2
CDE111111; CCCCCCCCCCCCC;3
CDE111111; AAAAAAAAAAAAA;4
CDE111111; BBBBBBBBBBBBB;5
CDE111111; CCCCCCCCCCCCC;6
* CDE111111; 5 x AAAAAAAAAAAAA 7 x BBBBBBBBBBBBB 9 x CCCCCCCCCCCCC
CDE222222; DDDDDDDDDDDDD;1
CDE222222; EEEEEEEEEEEEE;2
CDE222222; EEEEEEEEEEEEE;2
CDE222222; FFFFFFFFFFFFFFF;3
CDE222222; FFFFFFFFFFFFFFF;3
* CDE222222; 1 x DDDDDDDDDDDDD 4 x EEEEEEEEEEEEE 6 x FFFFFFFFFFFFFFF
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;2
CDE333333; KKKKKKKKKKKKKK;3
CDE333333; GGGGGGGGGGGG;1
CDE333333; HHHHHHHHHHHHHH;1
CDE333333; KKKKKKKKKKKKKK;1
* CDE333333; 2 x GGGGGGGGGGGG 3 x HHHHHHHHHHHHHH 4 x KKKKKKKKKKKKKK
Number of records processed: 17, output written to: output.csv
it produces this result
output.csv
Code: 
NUMCDE; QTY & REFERENCE
CDE111111; 5 x AAAAAAAAAAAAA 7 x BBBBBBBBBBBBB 9 x CCCCCCCCCCCCC
CDE222222; 1 x DDDDDDDDDDDDD 4 x EEEEEEEEEEEEE 6 x FFFFFFFFFFFFFFF
CDE333333; 2 x GGGGGGGGGGGG 3 x HHHHHHHHHHHHHH 4 x KKKKKKKKKKKKKK
 
Upvote 0 Downvote
Thank you so much mikrom...!!!. Now, my problem is solved.... Once again - many thanks.

Daniel of France
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rick_Stanich
  • Locked
  • Question
Save xls file to cvs file 1
Replies
3
Views
195
Rick_Stanich
Rick_Stanich
asel
  • Locked
  • Question
Copy cell data from excel and paste into portable software application
Replies
0
Views
128
asel
asel
TomFra
  • Locked
  • Question
OCR Zoning - Field data for auto-sorting is not being considered in 1 of 2 possible cases. 1
Replies
9
Views
207
TomFra
TomFra
Palito1916
  • Locked
  • Question
Disable multiple computer accounts using TXT file with computer names
Replies
2
Views
176
Palito1916
Palito1916
bdwilcox
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
801
SoftwareRT
SoftwareRT

Part and Inventory Search

Sponsor

Back
Top