xlsx to csv (issue with format)

[Akakaboto]

Hello,

I have an excel file where I use PQ to append several SAP reports into one file.

In order to get the data into PP I have had to save the file as .csv as the data set are very large and it seems PP likes csv files more (does not crash everytime i try to refresh the data).

Now to the problem. I cleaned som data from the source file and now when I update PP some columns are blank (only a few cells populated)

In the xlxs file everything looks good, all 3 columns below are formatted as text.

Parma_Pdoc_Item Order Reference Currency
102137201413840E540 800047452 EUR
105950200610144B50 450086919200010 SEK
185880201313616B10 450086920200010 EUR
185880201313616B10 450086920500010 EUR
100072201313619A60 B14047929593 EUR
100072201313619A60 B14047929582 EUR
100072201313619A60 B14047929604 EUR
100072200911918C10 B14047929615 EUR
399965201413858C370 B14047941165 EUR
399965201413858D370 B14047941353 EUR
464599999999910 4645_EUR_ICE_1501 EUR
1008999999999910 10089_USD_ICE_1501 USD
1248299999999910 12482_SEK_ICE_1501 SEK
1248299999999910 12482_USD_ICE_1501 USD
1248299999999910 12482_EUR_ICE_1501 EUR
1248299999999910 12482_AUD_ICE_1501 AUD
1248299999999910 12482_HKD_ICE_1501 HKD
2337199999999910 23371_USD_ICE_1501 USD
2337199999999910 23371_JPY_ICE_1501 JPY

the problem occurs when the .xlsx is saved as .csv where format will change to:


Parma_Pdoc_Item Order Reference Currency
102137201413840E540 800047452 EUR
105950200610144B50 4,50087E+14 SEK
185880201313616B10 4,50087E+14 EUR
185880201313616B10 4,50087E+14 EUR
100072201313619A60 B14047929593 EUR
100072201313619A60 B14047929582 EUR
100072201313619A60 B14047929604 EUR
100072200911918C10 B14047929615 EUR
399965201413858C370 B14047941165 EUR
399965201413858D370 B14047941353 EUR
4,646E+14 4645_EUR_ICE_1501 EUR
1,009E+15 10089_USD_ICE_1501 USD
1,2483E+15 12482_SEK_ICE_1501 SEK
1,2483E+15 12482_USD_ICE_1501 USD
1,2483E+15 12482_EUR_ICE_1501 EUR

I haven't had any issues when uploading the csv file to PP before even though the file looks as looks above. but this morning, after I cleaned some data I got the issue where column A and B are being blanked out in PP.

I've tried creating a schema.ini file to have it read the first 10000 rows but it doesnt help.

Is there a solution where I can keep the same formatting in the csv file as I have in the xlsx file?

this is a really important report so I need to find a way to solve it.

Thanks in advance!

 

[Akakaboto]

Just opened the file with notepad and everything looks good there.....why doesn't PP then read these rows?

Parma_Pdoc_Item;Order Reference;Currency;Net Price;Total Invoiced Quantity;Total Invoiced Qty Price Unit;Total Amount Local Currency;Total Amount Document Currency;PO Document;PO Item;Create Date;Created By;Vendor;Purchasing Organization;Purchasing Group;Material;Material Group;Contract Number;Contract Item;Unit;Price Date;Booked Volume M3;Booked Gross Weight;Booked Pay Weight
102137201413840E540;800047452;EUR;408,96;15491;15491;2518,08;292,8;4500840622;10;2014-11-14;LES_SC_Y10;102137;F440;A08;4000021602;96406000;201413840E;540;KG;2015-02-25;46,518;6700;15490,494
105950200610144B50;450086919200010;SEK;6754,27;1;1;6754,27;6754,27;4500869192;10;2014-12-08;A039065;105950;F440;A52;4000022771;96403000;200610144B;50;EA;2015-01-19;78,4;33040;0
185880201313616B10;450086920200010;EUR;720;1;1;6192;720;4500869202;10;2014-12-08;A039065;185880;F440;A08;4000020568;96403000;201313616B;10;EA;2015-01-13;54,9;23128;0
185880201313616B10;450086920500010;EUR;720;1;1;6192;720;4500869205;10;2014-12-08;A039065;185880;F440;A08;4000020568;96403000;201313616B;10;EA;2015-01-07;54,9;23128;0
100072201313619A60;B14047929593;EUR;1310;1;1;12287,39;1310;4500873435;10;2014-12-11;ATLAS;100072;F410;A08;4000018495;96403000;201313619A;60;EA;2015-01-08;12,176;22256;0
100072201313619A60;B14047929582;EUR;1310;1;1;12287,39;1310;4500873436;10;2014-12-11;ATLAS;100072;F410;A08;4000018495;96403000;201313619A;60;EA;2015-01-07;4,687;8590;0
100072201313619A60;B14047929604;EUR;1310;1;1;12287,39;1310;4500873444;10;2014-12-11;ATLAS;100072;F410;A08;4000018495;96403000;201313619A;60;EA;2015-01-09;4,869;9330;0
100072200911918C10;B14047929615;EUR;1962,5;0;0;0;0;4500873445;10;2014-12-11;ATLAS;100072;F410;A19;4000001774;96406000;200911918C;10;KG;2015-01-09;17,084;21785;21785
399965201413858C370;B14047941165;EUR;365,4;6330;6330;3142,44;365,4;4500873827;10;2014-12-11;ATLAS;399965;F410;A08;4000019676;96406000;201413858C;370;KG;2015-01-07;7,7;6330;6330
399965201413858D370;B14047941353;EUR;230,57;1598;1598;1989,52;231,34;4500873829;10;2014-12-11;ATLAS;399965;F410;A08;4000019715;96406000;201413858D;370;KG;2015-01-07;2,2;1508;1508
464599999999910;4645_EUR_ICE_1501;EUR;1;0;0;0;0;4500876409;10;2014-12-15;A078339;4645;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1008999999999910;10089_USD_ICE_1501;USD;1;0;0;0;0;4500876412;10;2014-12-15;A078339;10089;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1248299999999910;12482_SEK_ICE_1501;SEK;1491;1490,76;1490,76;1490,76;1490,76;4500876413;10;2014-12-15;A078339;12482;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1248299999999910;12482_USD_ICE_1501;USD;29960;26348,34;26348,34;173899,05;26348,34;4500876414;10;2014-12-15;A078339;12482;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1248299999999910;12482_EUR_ICE_1501;EUR;795;794,35;794,35;6831,41;794,35;4500876416;10;2014-12-15;A078339;12482;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1248299999999910;12482_AUD_ICE_1501;AUD;1;0;0;0;0;4500876420;10;2014-12-15;A078339;12482;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
1248299999999910;12482_HKD_ICE_1501;HKD;1;0;0;0;0;4500876421;10;2014-12-15;A078339;12482;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0
2337199999999910;23371_USD_ICE_1501;USD;1;0;0;0;0;4500876422;10;2014-12-15;A078339;23371;F410;A14;4800000495;96501000;999999999;10;EA;2015-01-01;0;0;0

AHHHHHHHH....sometimes Excel is really your enemy.

 

[Akakaboto]

just solved it.

I created a schema.ini file with:

[ConsolidationFile.csv]
Format=Delimited( ; ) "no spaces betwen () and ;"
MaxScanRows=1

and made sure the first row had the format I wanted it.