[Solved] Input.csv to Output.csv

[Tony Fay]

Hi All ,
I have a new daily task to do . I trying to perform an automatic extraction of data from an input.csv file to an output.csv file .
any help would be very much apprciated. regards Tony

This is the origional INPUT.CSV file before any modifications to the values;


INPUT.CSV FILE
Cust Code,"Inv Date","Inv Num","Type","Unit Price","Line Disc","Qty","Net Amt","Cost Amt","Margin","%","Market Code","",
ZBJBOLB01 - BIG J BOLT BOX TYPE 1,"","","","","","","","","","","","",
ZMONA01,"02/10/2017",543357,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",
ZNBAL01,"03/10/2017",543369,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",
ZMONALISA0,"03/10/2017",543372,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",3.00,70.50,"",70.50,"100.00%","","",
,"","","","","","","","","","","","",
ZBJBOLT02 - BIG J BOLT BOX TYPE 2,"","","","","","","","","","","","",
ZFERN01,"02/10/2017",543325,"I",23.00,"",1.00,23.00,"",23.00,"100.00%","DEFAULT","",
ZBROTHER01,"02/10/2017",543326,"I",23.00,"",2.00,46.00,"",46.00,"100.00%","DEFAULT","",
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",3.00,69.00,"",69.00,"100.00%","","",
,"","","","","","","","","","","","",
ZBCCTA - BIG C Clip Type A,"","","","","","","","","","","","",
ZNIC01,"12/10/2017",543829,"I",33.00,"",1.00,33.00,"",33.00,"100.00%","DEFAULT","",
ZFERN01,"16/10/2017",543925,"I",33.00,"",1.00,33.00,"",33.00,"100.00%","DEFAULT","",
ZAL01,"07/11/2017",544884,"I",33.00,"",1.00,33.00,"",33.00,"100.00%","DEFAULT","",
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",5.00,164.00,"",164.00,"100.00%","","",
,"","","","","","","","","","","","",
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Report Totals:",11.00,203.50,"",203.50,"100.00%","","",



The only fields I need are Cust Code, Inv Date, Inv Num , Qty , Net Amt.
I also need the values from Row 2 which contains my Prodcode and my ProdDiscription values in my output.csv file
i.e "ZBJBOLB01" = Prodcode and "- BIG J BOLT BOX TYPE 1" = ProdDiscription
"ZBJBOLT02" = Prodcode and "- BIG J BOLT BOX TYPE 2" = ProdDiscription
"ZBCCTA" = Prodcode and "- BIG C Clip Type A" = ProdDiscription etc. etc.

Also
Disgard the rows with the following values;
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",3.00,70.50,"",70.50,"100.00%","","",
,"","","","","","","","","","","","",

And
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",3.00,69.00,"",69.00,"100.00%","","",
,"","","","","","","","","","","","",

and
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Product Totals:",5.00,164.00,"",164.00,"100.00%","","",
,"","","","","","","","","","","","",
,"","","","","","––––––","–––––––––","–––––––––","–––––––––","–––––––","","",
,"","","","","Report Totals:",11.00,203.50,"",203.50,"100.00%","","",

0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0



This is the format of the OUTPUT.CSV file I require

DeviceID,PosTxnID,MobileNumber,Timestamp,PosID,PosUserID,ShopID,Prodcode,ProdDescription,ProdDept,ProdGroup1,ProdGroup2,Quantity,Value
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,


OUTPUT.CSV INPUT.CSV
---------- -----------
DeviceID = Cust Code
PosTxnID = Inv Num
MobileNumber = ,
Timestamp = Inv Date (The output.csv format required is "YYYY-MM-DD hh:mm" Where hh:mm is always 00:00)
PosID = ,
PosUserID = ,
ShopID = "S12345" (static string value for all rows with data, no source from input.csv file)
Prodcode = "ZBJ????" code at begining of Row2 and at other variable occurences within the csv file
ProdDiscription = "- text string " after the ZBJ??? Prodcode in Row 2 and at other variable occurences within the csv file
ProdDept = ,
ProdGroup1 = ,
ProdGroup2 = ,
Quantity = QTY
Value = Net Amt



The Final output.csv file should be as below

DeviceID,PosTxnID,MobileNumber,Timestamp,PosID,PosUserID,ShopID,Prodcode,ProdDescription,ProdDept,ProdGroup1,ProdGroup2,Qty,Value ,,,,
ZMONA01,543357,,2017-02-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNBAL01,543369,,2017-03-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNIC01,544702,,2017-02-11 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
,,,,,,,,,,,,,,,,,
ZFERN01,543325,,2017-02-10 00:00,,,,ZBJBOLT02,- BIG J BOLT BOX TYPE 2,,,,1,23,,,,
ZBROTHER01,543326,,2017-02-10 00:00,,,,ZBJBOLT02,- BIG J BOLT BOX TYPE 2,,,,2,46,,,,
,,,,,,,,,,,,,,,,,
ZNIC01,543829,,2017-10-12 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,
ZFERN01,543925,,2017-10-16 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,
ZAL01,544884,,2017-07-11 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,

[Villeroy]

Put the file in a dedicated directory.
File>New>Database
[X] Connect to existing database
Type: Text
Specify the directory, delimiters etc.
[X] yes, register the database
Save the database.
Have a look at the table(s). Each similar file in the directory is represented by one database table. Nothing has been converted or copied. The data are still in the text files.
Go to the queries section and create a new query in design view.
Add one table
Double click the columns you want and specify the alias names (column labels).
Add criteria like <>'' which means "not having empty string" (two single quotes) or Not Empty. I'm not sure which one works in this context.
Save the query.
Save the database.

Now you can hit F4 in Writer and Calc and drag the query into a document.

[Tony Fay]

Thankyou so much for your quick reply Villeroy. posting.php?mode=reply&f=5&sid=d0001100 ... b4&t=91255#

I have followed your guide above and while in the most part I had no troubles except when trying to
Add criteria like <>'' which means "not having empty string" (two single quotes) or Not Empty.
I was presented with Santex error and no output from running the query in design mode.

I have used the empty field C13 in inputfile.csv table as the source for the empty fields required in my output file.

SELECT "Cust Code" AS "DeviceID", "Inv Num" AS "PosTxnID", "C13" AS "MobileNumber", "Inv Date" AS "Timestamp", "C13" AS "PosID", "C13" AS "PosUserID", "C13" AS "ShopID", "C13" AS "Prodcode", "C13" AS "ProdDiscription", "C13" AS "ProdDept", "C13" AS "ProdGroup1", "C13" AS "ProdGroup2", "Qty" AS "Quantity", "Net Amt" AS "Value" FROM "INPUTFILE" WHERE "Cust Code" LIKE 'Z%'

I did use the LIKE 'Z*' criteria in Column 1 Custcode, alias DeviceID to remove any Blank lines in that field from the OUTPUT file which also removed the Total Values and Total lines ––––––.

I still have the issue of extracting the following values from INPUTFILE Table Cust Code Field in Column 1, Row 2
This contains the data in one cell that I require for my Prodcode and my ProdDiscription values for my output.csv file
i.e "ZBJBOLB01" = Prodcode and "- BIG J BOLT BOX TYPE 1" = ProdDiscription (These values should also be copied to all the rows that are for that Prodcode)
then furthed down a Second Product
"ZBJBOLT02" = Prodcode and "- BIG J BOLT BOX TYPE 2" = ProdDiscription (These values should also be copied to all the rows that are for that Prodcode)

And again further down a third product
"ZBCCTA" = Prodcode and "- BIG C Clip Type A" = ProdDiscription etc. etc. " (These values should also be copied to all the rows that are for that Prodcode

Also;
I have tried criteria in TimeStamp field '{ts''YYYY-MM-DD HH:MI:SS''}' but this gives me no output in design view.
I have not done any DB work in many years and Im lost as to where to go now with this.

Sorry for Being so longwinded with my post.posting.php?mode=reply&f=5&sid=d0001100 ... b4&t=91255#
Again Many thanks Villeroy for your assistance .

[Villeroy]

The "not equal" operator is !=
I forgot that <> does not work in this context.

These values should also be copied to all the rows that are for that Prodcode

You have one description per code and the other descriptions are blank?
What if there are two different descriptions for the same code? Which one would be the right one?
I'd think that completeness is the duty of the database program which exports the csv.

This context (native Base driver for text files) is very limited. You can not lookup or complement data.
With this driver all you can query is

Code: Select all

SELECT (DISTINCT) <field list> with aliases
FROM <single table> 
WHERE <conditions>
ORDER BY <fields> ASC|DESC

You could append
ORDER BY "ProdCode" ASC, "ProdDescription" ASC
to your query, import that query into Calc and then use a clever method to fill the blanks with their predecessors: Re: copying cell content into empty cells
When saving the spreadsheet, choose export format "Text (csv)" and select option "Edit filter settings". You will be prompted for delimiters, encoding and path name.

[MrProgrammer]

Hi, and welcome to the forum.

I have a new daily task to do. I trying to perform an autmatic extract of data from an input.csv file to an output,csv file.

OpenOffice is the wrong tool for this job. CSV files are just text. It makes no sense to import the text to Base or to Calc, run SQL or Calc formulas to reformat the data, and use Calc's File → Save As → Text CSV dialog to create another text file. What you want is Perl, a tool well suited to working with text files, including those in CSV format. Perl typically comes pre-loaded on good operating systems (Linux, Mac, and Unix). You have dreadful Windoze, but if Perl isn't pre-loaded you can install it, just as you did with OpenOffice. Here's a Perl program to reformat your data. This isn't a Perl forum so I will decline to answer any questions about Perl (How to I install? How do I run the program? How can I change it to do XYZ? etc.)

use Text::CSV;
my ($in, $out, $csvi, $csvo, $ar, $col, $hri, $hro, $yyyy, $mm, $dd, $code, $desc);
my @hdr = qw(DeviceID PosTxnID MobileNumber Timestamp PosID PosUserID ShopID
             Prodcode ProdDescription ProdDept ProdGroup1 ProdGroup2 Qty Value);
my @e14 = ('','','','','','','','','','','','','',''); my $spacer = 0;
open $in, "<input.csv" or die "input.csv: $!\n";
$csvi = Text::CSV->new( { binary=>1 } );
open $out, ">output.csv" or die "output.csv: $!\n";
$csvo = Text::CSV->new( { binary=>1, eol=>$/ } );
$ar = $csvi->getline($in); $col = 0;
foreach (@{$ar}) { $_ = "Col$col" if $col>7; s/ //; $col++; }
$csvi->column_names($ar); $csvo->column_names(@hdr); $csvo->print($out,\@hdr);
while ($hri = $csvi->getline_hr($in)) {
   if ($hri->{InvDate}) {
      ($dd,$mm,$yyyy) = split("/",$hri->{InvDate});
      $hro = { DeviceID=>$hri->{CustCode}, PosTxnID=>$hri->{InvNum}, MobileNumber=>'',
               Timestamp=>$yyyy.'-'.$mm.'-'.$dd.' 00:00', PosID=>'', PosUserID=>'',
               ShopID=>'S12345', Prodcode=>$code, ProdDescription=>$desc, ProdDept=>'',
               ProdGroup1=>'', ProdGroup2=>'', Qty=>$hri->{Qty}, Value=>$hri->{NetAmt} };
      if ($spacer) { $csvo->print($out,\@e14); $spacer = 0; }
      $csvo->print_hr($out,$hro);
      }
   elsif ($hri->{CustCode}) { ($code,$desc) = split(' - ',$hri->{CustCode}); }
   elsif ($hri->{LineDisc}) { $spacer = 1; }
   }

However you've asked this question in an OpenOffice forum, so I will tell you how to do it there, even though this is inapt. Open the attachment. The sheet "Input" is your source data. I loaded it with Edit → Paste Special → Unformatted Text and the Text Import dialog, taking care to mark the inventory date as DMY format, but you could load it from Base, as previously explained. Sheet "Temp" uses a few formulas to analyze how the data is organized. Sheet "Output" uses the formula results to build the format you want. Sheet "Goal" is a way for me to check that the output format is what's desired. So you load sheet Input, select sheet Output, and use the File → Save As → Text CSV dialog to create your CSV file. But this is far more work than running the Perl program.

201711291705.ods

(13.64 KiB) Downloaded 199 times

I still have the issue of extracting the following values from INPUTFILE Table Cust Code Field in Column 1, Row 2

Both the Perl program [($code,$desc) = split] and my attachment [columns C, D, E, F, and I in Temp] handle that.

INPUT.CSV FILE
Cust Code,"Inv Date","Inv Num","Type","Unit Price","Line Disc","Qty","Net Amt","Cost Amt","Margin","%","Market Code","",
ZBJBOLB01 - BIG J BOLT BOX TYPE 1,"","","","","","","","","","","","",
ZMONA01,"02/10/2017",543357,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",
ZNBAL01,"03/10/2017",543369,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",
ZMONALISA0,"03/10/2017",543372,"I",23.50,"",1.00,23.50,"",23.50,"100.00%","DEFAULT","",

The Final output.csv file should be as below
DeviceID,PosTxnID,MobileNumber,Timestamp,PosID,PosUserID,ShopID,Prodcode,ProdDescription,ProdDept,ProdGroup1,ProdGroup2,Qty,Value ,,,,
ZMONA01,543357,,2017-02-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNBAL01,543369,,2017-03-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNIC01,544702,,2017-02-11 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,

Your sample output data doesn't match your input. I ignored your sample output except to match the layout.

ZMONA01,543357,,2017-02-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNBAL01,543369,,2017-03-10 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZNIC01,544702,,2017-02-11 00:00,,,,ZBJBOLB01, - BIG J BOLT BOX TYPE 1,,,,1,23.5,,,,
ZFERN01,543325,,2017-02-10 00:00,,,,ZBJBOLT02,- BIG J BOLT BOX TYPE 2,,,,1,23,,,,
ZBROTHER01,543326,,2017-02-10 00:00,,,,ZBJBOLT02,- BIG J BOLT BOX TYPE 2,,,,2,46,,,,
ZNIC01,543829,,2017-10-12 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,
ZFERN01,543925,,2017-10-16 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,
ZAL01,544884,,2017-07-11 00:00,,,,ZBCCTA, - BIG C Clip Type A,,,,1,33,,,,

Most, but not all, of the dates in your sample output are formatted incorrectly as bogus YYYY-DD-MM (green). Please be more careful when posting. I tend to ignore sloppy posts.

,"","","","","Product Totals:",3.00,70.50,"",70.50,"100.00%","","",
,"","","","","Product Totals:",3.00,69.00,"",69.00,"100.00%","","",
,"","","","","Product Totals:",5.00,164.00,"",164.00,"100.00%","","",
,"","","","","Report Totals:",11.00,203.50,"",203.50,"100.00%","","",

Your report totals don't match either. 70.50+69+164 is 303.50 not 203.50. What a mess! I hope you aren't making business decisions from this report.

[Tony Fay]

Sorry for the long delay in getting back to you, A very Happy New Year to you all

To Villeroy , Many thanks for your input and thoughts in resolving my request .

To Mr Programmer, Thank You for your submitted file above 201711291705.ods. This did give me the result I required in Open Office.

I apologise for the errors in my sample data in my first post , this was caused by my cuting and pasting and trying to sanitize the data for posting on this site. I will endeavour to be more careful with my posts in future.

I am now labelling this subject as SOLVED

Mr Programmer you did say

OpenOffice is the wrong tool for this job. CSV files are just text.

What you want is Perl, a tool well suited to working with text files, including those in CSV format.

I have had no prior knowledge of Perl before. so after some research, I have downloaded and installed Strawberry Perl (64-bit) 5.26.1.1-64bit for Windows on my Windows 10 Platform .

I ran the Perl script you submitted above and it did indeed give me the result I was looking for in my Output.csv file far easier than doing it in OpenOffice.
Your Perl script requires some minor editing to give me the polished end result I require.
So Thank you for the introduction to Perl and I will be using PERL to do my daily task with these csv files.

I do wish to understand your script more and also to add some additional function,
Like to prompt the user to enter the filename for the input.csv file and also that the output file would have the input filename as part of it.
e.g. The User enters 1-6-2018.csv when prompted for input file name, then the output filename would be 1-6-2018-output.csv .

You have already stated that this is an OpenOffice forum and not a PERL forum, and I respect that and I do not want to hog it seeking answers to my Perl requests, so would you recommend a PERL site / forum that I could ask these questions.

Many Thanks,
Tony

[MrProgrammer]

I am now labeling this subject as SOLVED

Thanks. This helps others with similar problems understand that these ideas may help them also.

would you recommend a PERL site / forum that I could ask these questions.

I have not personally used any, so I can't offer recommendations. However I would start with a look at the Community resources on the Perl webpage. You'll note that there are Learn resources there which you may find helpful. Another good resource might be StackOverflow·com. There are more than 50K questions tagged with [perl].

prompt the user to enter the filename for the input.csv file and also that the output file would have the input filename

As guidance for learning about the language or asking in a forum, you could consider omitting the file names from the program and using STDIN/STDOUT, or retrieving the file name from the command line with @ARGV.

1-6-2018.csv

If appropriate, another idea would be to have the program choose the input and output file names based on the current date.

I do wish to understand your script more

You'll want to learn how to use the Perl debugger so you can put breakpoints in the program and examine how individual statements are processed.