Problem exporting and importing edited CSV File

Questions and Answers about the POS Pizza 6 and newer Database
Post Reply
Hometown Pizzeria
Active Member
Active Member
Posts: 85
Joined: Mon Dec 17, 2012 5:29 pm

Problem exporting and importing edited CSV File

Post by Hometown Pizzeria »

Guys,

I exported the complete customer database and then imported it into excel and made my changes then did a file save as .csv file but it will not import correctly back into V6. The file exported from V6 when viewed shows " marks surrounding every file of date but when it exports from excel it no longer has the " marks only the , separators. I know how to aline the data using the separator field and it still when you verify the Last Name, on the right with the Last Name, on the left they are both aligned correctly but import totally different data. Is this caused by the missing, " " if so is there another program to leave the " " marks or how do you get around this.

Example of exported data as viewed from V6 file:
Last Name,First Name,Customer ID,House,Street,House + Street,Address 2,City,State,Zip Code,Primary Phone,Email,Date Entered,Date Entered(Unix),Credits Amount,Acct/Tab Amount,Tab Limit,Total Spent,Tot Orders,Last Ord Date,LO Date(Unix),Extra Data Name,Bad Check,Cash Only,Mgr Define 1,Mgr Define 2,Don't Email,Exclude Rpts,Allow -Bal,Extra Data Required,Free Delivery,Do Not Use Loyalty Points,Points Amt
"Cross","Roxanne","ROXCRO1","14270","route 85","14270 route 85","","Mannsville","NY","13661","(315) 232-4591","","12/29/2012","20121229","$0.00","$0.00","$0.00","$82.38","2","08/24/2013","20130824","","NO","NO","NO","NO","NO","NO","NO","NO","NO","NO","5003"

Example of file after edits and saved as a CSV file ready for import into V6
Last Name,First Name,Customer ID,House,Street,House + Street,Address 2,City,State,Zip Code,Primary Phone,Email,Date Entered,Date Entered(Unix),Credits Amount,Acct/Tab Amount,Tab Limit,Total Spent,Tot Orders,Last Ord Date,LO Date(Unix),Extra Data Name,Bad Check,Cash Only,Mgr Define 1,Mgr Define 2,Don't Email,Exclude Rpts,Allow -Bal,Extra Data Required,Free Delivery,Do Not Use Loyalty Points,Points Amt
Southwick,Beach,SOUTHW1,8119,Southwicks Place,98 8119 Southwicks Place,,Henderson,NY,13650,(315) 486-7488,,7/24/13,20130724,$0.00 ,$0.00 ,$0.00 ,$51.06 ,1,7/24/13,20130724,,NO,NO,NO,NO,NO,NO,NO,NO,NO,NO,3499

Thank You,
Keith
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Problem exporting and importing edited CSV File

Post by Scott »

The quotes are there because there are a lot of STRING type fields. If any of those fields contain a comma within the field, then there will be a misalignment for the record. A good example of this would be the address2 field. If it were "upstairs, apt. #6" everything after that would be shifted over by 1.

This may be happening with certain records in your database. The " marks are optional, but if a field has a comma then they are required. Try opening the file with the internal CSV editor and looking at individual records. Save the file using this internal editor and see if you get better results.

I know many people have used Excel to modify bulk customers and then imported them. I am not an Excel user, so I can't give any pointers on it. Maybe someone who knows how to have it enclose the fields properly can chime in.

Scott
User avatar
synpse
Veteran Member
Veteran Member
Posts: 244
Joined: Wed Oct 12, 2011 2:38 pm
Location: Pennsylvania
Contact:

Re: Problem exporting and importing edited CSV File

Post by synpse »

There should be a checkbox in Excel for "Delimited CSV" which is the format with the " " marks around the field. I'm pretty sure Excel doesn't use the delimited CSV by default.



Programmer Note: Always escape your fields. those ' and " and , can ruin your experience.


We had an issue like that in the X-Charge plug-in. A customer's last name had an Apostrophe in it. "D'Angelo". That issue was fixed a long, long time ago.
Post Reply