ERPNext unable to read my .csv file

I have tried to upload data from a CSV file but the system is not reading the data as it should.
When I download the templeta I get a file like this:

“Data Import Template”
“Table:”,“Item Price”
“”
“”
“Notes:”
“Please do not change the template headings.”
“First data column must be blank.”
“If you are uploading new records, leave the ““name”” (ID) column blank.”
“If you are uploading new records, ““Naming Series”” becomes mandatory, if present.”
“Only mandatory fields are necessary for new records. You can delete non-mandatory columns if you wish.”
“For updating, you can update only selective columns.”
“You can only upload upto 5000 records in one go. (may be less in some cases)”
“”
“DocType:”,“Item Price”,“”,“”,“”,“”,“”,“”,“”,“”
“Column Labels:”,“ID”,“Price List”,“Item Code”,“Rate”,“Buying”,“Selling”,“Currency”,“Item Name”,“Item Description”
“Column Name:”,“name”,“price_list”,“item_code”,“price_list_rate”,“buying”,“selling”,“currency”,“item_name”,“item_description”
“Mandatory:”,“Yes”,“Yes”,“Yes”,“Yes”,“No”,“No”,“No”,“No”,“No”
“Type:”,“Data (text)”,“Link”,“Link”,“Currency”,“Check”,“Check”,“Link”,“Data”,“Text”
“Info:”,“”,“Valid Price List”,“Valid Item”,“”,“0 or 1”,“0 or 1”,“Valid Currency”,“”,“”
“Start entering data below this line”

I can take than an separate everything in colums in Excel. I save the file and the file then takes this form:

Data Import Template;;;;;;;;;
Table:;Item Price;;;;;;;;
;;;;;;;;;
;;;;;;;;;
Notes:;;;;;;;;;
Please do not change the template headings.;;;;;;;;;
First data column must be blank.;;;;;;;;;
“If you are uploading new records, leave the ““name”” (ID) column blank.”;;;;;;;;;
“If you are uploading new records, ““Naming Series”” becomes mandatory, if present.”;;;;;;;;;
Only mandatory fields are necessary for new records. You can delete non-mandatory columns if you wish.;;;;;;;;;
For updating, you can update only selective columns.;;;;;;;;;
You can only upload upto 5000 records in one go. (may be less in some cases);;;;;;;;;
;;;;;;;;;
DocType:;Item Price;;;;;;;;
Column Labels:;ID;Price List;Item Code;Rate;Buying;Selling;Currency;Item Name;Item Description
Column Name:;name;price_list;item_code;price_list_rate;buying;selling;currency;item_name;item_description
Mandatory:;Yes;Yes;Yes;Yes;No;No;No;No;No
Type:;Data (text);Link;Link;Currency;Check;Check;Link;Data;Text
Info:;;Valid Price List;Valid Item;;0 or 1;0 or 1;Valid Currency;;
Start entering data below this line;;;;;;;;;

I can see that the default separator in my computer is ; instead of , and I could change that, don’t want to but it’s do-able (hopefully the system could recognize this difference). But the main issue is that my computer is changing all the blank spaces [,“”,] from the original file to a blank space [;;] and I think this is the reason why the system can not recognize the data im uploading. Luckily I dont have masive data files, so I can change the information manually with little effor but of course this brings the possibility for many errors to be commited.
Is there a fix for this?

What format are you saving in? There are multiple .csv formats in Excel. You want to save it as the highlighted below:

I tried saving the item price template in all three csv formats, and I was unable to replicate your results. What version of excel & what operating system are you using?

I am using windows 10 and excel 2016. I think it might be sue to my regional configuration. It works on Google sheets but I’ve tried everything in excel and I can’t get it to work

The language for both softwares is Spanish and if I recall correctly, the keyboard I’m put is set to Spanish Latin America or Chile

please try using openoffice … or libre office… windows csv has different format as i recall

I’m also using windows10 & excel 2016, so your regional settings must be the ticket.

Here is my Regional Config:

Decimal Symbol “.”
Decimal Digits “2”
Thousand Separator “,” → Dont know if thats the accurate translation
Number of Digits in Groups “123,456,789”
Negative Symbol “-”
Negative Number Format “-1.1”
Column Separator “;”
etc.

I believe it’s the column separator that’s your problem. adding
sep=,

to the first line of the .csv file should solve your problem for now. I will open an issue on github to ask the team to add this to the first line of all .csv files.

https://github.com/frappe/erpnext/issues/5239

@pugalde @ahmed_deabs Try opening these files in excel, and tell me what happens:

Original Item Template:

https://drive.google.com/open?id=0BzSl2jbxUUahYW5WeDh0RFA5WDA

Item Template with sep=;

https://drive.google.com/open?id=0BzSl2jbxUUahMDM1Uk9MRi1HSnM

Item Template with sep=,

https://drive.google.com/open?id=0BzSl2jbxUUahMUhHVmZLNVQwTVE

The last one SHOULD work, I think.

with sep=;

With sep=,

That thing worked. As soon as I open the file I get the text in the columns they should be in. Then I saved the file and it goes like this when I open in notepad:

Data Import Template;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Table:;Item;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Notes:;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Please do not change the template headings.;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
First data column must be blank.;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
“If you are uploading new records, leave the ““name”” (ID) column blank.”;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
“If you are uploading new records, ““Naming Series”” becomes mandatory, if present.”;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Only mandatory fields are necessary for new records. You can delete non-mandatory columns if you wish.;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
For updating, you can update only selective columns.;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
You can only upload upto 5000 records in one go. (may be less in some cases);;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
DocType:;Item;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;~;Item Reorder;reorder_levels;;;;~;Item Variant Attribute;attributes;;;;;;~;UOM Conversion Detail;uoms;;~;Item Supplier;supplier_items;;~;Item Customer Detail;customer_items;;~;Item Tax;taxes;;~;Item Quality Inspection Parameter;quality_parameters;;~;Website Item Group;website_item_groups;~;Item Website Specification;website_specifications;
Column Labels:;ID;Item Code;Item Name;Item Group;Default Unit of Measure;Description;Variant Of;Maintain Stock;Disabled;Image;Brand;Barcode;Default Warehouse;End of Life;Has Batch No;Has Serial No;Serial Number Series;Is Fixed Asset Item;Allow over delivery or receipt upto this percent;Valuation Method;Warranty Period (in days);Net Weight;Weight UOM;Has Variants;Is Purchase Item;Minimum Order Qty;Lead Time in days;Default Buying Cost Center;Default Expense Account;Last Purchase Rate;Default Supplier;Delivered by Supplier (Drop Ship);Manufacturer;Manufacturer Part Number;Is Sales Item;Is Service Item;Default Income Account;Default Selling Cost Center;Max Discount (%);Inspection Required;Allow Production Order;Supply Raw Materials for Purchase;Default BOM;Show in Website;Page Name;Weightage;Slideshow;Image;Thumbnail;Website Warehouse;Website Description;Parent Website Route;;ID;Warehouse;Re-order Level;Material Request Type;Re-order Qty;;ID;Attribute;Attribute Value;Numeric Values;From Range;Increment;To Range;;ID;UOM;Conversion Factor;;ID;Supplier;Supplier Part Number;;ID;Customer Name;Ref Code;;ID;Tax;Tax Rate;;ID;Parameter;Acceptance Criteria;;ID;Item Group;;ID;Label;Description
Column Name:;name;item_code;item_name;item_group;stock_uom;description;variant_of;is_stock_item;disabled;image;brand;barcode;default_warehouse;end_of_life;has_batch_no;has_serial_no;serial_no_series;is_asset_item;tolerance;valuation_method;warranty_period;net_weight;weight_uom;has_variants;is_purchase_item;min_order_qty;lead_time_days;buying_cost_center;expense_account;last_purchase_rate;default_supplier;delivered_by_supplier;manufacturer;manufacturer_part_no;is_sales_item;is_service_item;income_account;selling_cost_center;max_discount;inspection_required;is_pro_applicable;is_sub_contracted_item;default_bom;show_in_website;page_name;weightage;slideshow;website_image;thumbnail;website_warehouse;web_long_description;parent_website_route;~;name;warehouse;warehouse_reorder_level;material_request_type;warehouse_reorder_qty;~;name;attribute;attribute_value;numeric_values;from_range;increment;to_range;~;name;uom;conversion_factor;~;name;supplier;supplier_part_no;~;name;customer_name;ref_code;~;name;tax_type;tax_rate;~;name;specification;value;~;name;item_group;~;name;label;description
Mandatory:;Yes;Yes;Yes;Yes;Yes;Yes;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;No;;Yes;Yes;Yes;Yes;No;;Yes;Yes;No;No;No;No;No;;Yes;No;No;;Yes;No;No;;Yes;Yes;Yes;;Yes;Yes;No;;Yes;Yes;No;;Yes;Yes;;Yes;No;No
Type:;Data (text);Data;Data;Link;Link;Text Editor;Link;Check;Check;Attach;Link;Data;Link;Date;Check;Check;Data;Check;Float;Select;Data;Float;Link;Check;Check;Float;Int;Link;Link;Float;Link;Check;Link;Data;Check;Check;Link;Link;Float;Check;Check;Check;Link;Check;Data;Int;Link;Attach;Data;Link;Text Editor;Read Only;;Data;Link;Float;Select;Float;;Data;Link;Data;Check;Float;Float;Float;;Data;Link;Float;;Data;Link;Data;;Data;Link;Data;;Data;Link;Float;;Data;Data;Data;;Data;Link;;Data;Data;Text Editor
Info:;;;;Valid Item Group;Valid UOM;;Valid Item;0 or 1;0 or 1;;Valid Brand;;Valid Warehouse;;0 or 1;0 or 1;;0 or 1;;One of: FIFO, Moving Average;;;Valid UOM;0 or 1;0 or 1;;Integer;Valid Cost Center;Valid Account;;Valid Supplier;0 or 1;Valid Manufacturer;;0 or 1;0 or 1;Valid Account;Valid Cost Center;;0 or 1;0 or 1;0 or 1;Valid BOM;0 or 1;;Integer;Valid Website Slideshow;;;Valid Warehouse;;;;Leave blank for new records;Valid Warehouse;;One of: Purchase, Transfer;;;Leave blank for new records;Valid Item Attribute;;0 or 1;;;;;Leave blank for new records;Valid UOM;;;Leave blank for new records;Valid Supplier;;;Leave blank for new records;Valid Customer;;;Leave blank for new records;Valid Account;;;Leave blank for new records;;;;Leave blank for new records;Valid Item Group;;Leave blank for new records;;
Start entering data below this line;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The sep=, line at the beggining solved how the software open the file right after the double click. I didnt have to separate the line with the “Text to Colums” button on Excel but it didn’t solve the problem when I save the file with the information I add to it.

I dont know if is possible to make a bit more flexible the way ERPNext read and deals with the information contained in the csv file. Maybe there is a solution for every configuration.

As long as you save in excel and upload without changing in another program, it should work with the sep=, workaround. Although it won’t work for import until the Frappe team changes the .csv files on their end.

Sadly it didnt work.
I tried to upload the file and nothing happend.
This is what I do. Open the file, everything is as supose to be. Fill in the information requested in the form. Save it. Thats it. I dont do anything else. Here is the screenshot:

try changing the delimiter in your regional settings to comma and see if that helps

Changed the delimiter and it worked. Tried with the sep=, file and with the file I get from the system and both work.
Its weird that if you use a delimiter to open the file, excel arbitrarly changes to whatever it wants.
Anyway, thank you very much for the help. It has been very educative hahaha.
Cheers

No problem! Glad it’s working!