EasyPopulate 4.0 Support Thread

[GAM]

I was using the same example for Excel; however, hadn't tried to upload the file yet. Glad you pointed that out. The "program" doesn't modify the data presented specifically for dates. Therefore, it would seem that you would need to apply a custom date format to the excel cell to force a YYYY-MM-DD and appropriate time format as part of the text export. Might be possible to do a test of the cell and if it is a date and time format to then translate to the correct format; however, wonder what other "numbers" could be turned into a date time based on what is input.

Hi MC
Yeah, I looked into the code and can't see anywhere that is playing with the dates so it must be one of those Excel side-effects ;) My 'good' method for using Excel is:
1. Ensure all fields/columns are formatted correctly e.g., YYYY-MM-DD for dates, decimal places for monetary values, etc.
2. Save As .csv
3. Delete surrounding cells (Excel likes to put in trailing records)
4. Open .csv in Notepad++
5. 'Convert to UTF-8'
6. Save or Save As xxx_.csv
7. Upload
8. Import ...and notice everything in order and as expected i.e., correct dates, correct values, special characters intact and displayed as expected, etc.

This process seems flawless apart from remembering the open/convert step in Notepad++, which is a pain in the rear... especially if I forget. ;)

Although the macro does not appear to change the dates, it does seem to be allowing Excel to do its usual thing of being 'clever' for us... and changing the dates to system defaults.

On a side note, although I didn't notice any issues with special characters when I was testing the macro with one particular file, I have since seen character errors in my full products download that I hadn't noticed before, so I'm not sure if the macro/EP4 combo had other flaws or if these character issues are from a previous upload/testing task.

For the time being I'm sticking with the trusty Excel .csv/Notepad++ method.

Cheers
GAM

[mc12345678]

Hi MC
Yeah, I looked into the code and can't see anywhere that is playing with the dates so it must be one of those Excel side-effects ;) My 'good' method for using Excel is:
1. Ensure all fields/columns are formatted correctly e.g., YYYY-MM-DD for dates, decimal places for monetary values, etc.
2. Save As .csv
3. Delete surrounding cells (Excel likes to put in trailing records)
4. Open .csv in Notepad++
5. 'Convert to UTF-8'
6. Save or Save As xxx_.csv
7. Upload
8. Import ...and notice everything in order and as expected i.e., correct dates, correct values, special characters intact and displayed as expected, etc.

This process seems flawless apart from remembering the open/convert step in Notepad++, which is a pain in the rear... especially if I forget. ;)

Although the macro does not appear to change the dates, it does seem to be allowing Excel to do its usual thing of being 'clever' for us... and changing the dates to system defaults.

On a side note, although I didn't notice any issues with special characters when I was testing the macro with one particular file, I have since seen character errors in my full products download that I hadn't noticed before, so I'm not sure if the macro/EP4 combo had other flaws or if these character issues are from a previous upload/testing task.

For the time being I'm sticking with the trusty Excel .csv/Notepad++ method.

Cheers
GAM

Hey GAM,

You know though that if you are able to use the recommended Open Office that you won't have to think, remember, worry about those things. Supposedly it is able to save as UTF-8 and well, it saves the CSV files correctly. Seems that if you are able/allowed to install notepad++ then you should be able to install OO.

[GAM]

Hey MC, appreciate the suggestion but just not feasible (desirable) for me as I know Excel like the back of my hand and already have 10's to 100's of spreadsheets that I use to manage and manipulate my products and use extensively to import/export/update between MYOB and my Zen-Cart store as well as reporting and analysis.

FWIW, the older I get, the more I loath to upgrade/install/learn new software It's been bad enough adapting my processes and related files to work with EP4 from EP 2.5.4.WhateverItWas and Zen-Cart 1.3.7. Loads of new features on my store/back-office now with Zen 5.1 and new add-ons, which are great, but often frustrating when you're under the pump trying to keep on top of other day-to-day tasks.

Again, appreciate the assistance and suggestions MC. I'll report back when/as I discover new things and/or have something else to report.

Cheers
GAM

[BlessIsaacola]

Okay, I need help with two issues I am experiencing. One is easier to explain and the other I will explain and if you need more clarification to make sense of it please ask questions.

1. The first issue is with upc field. We have some products with upc code starting with zeros. Something like, 000768251143. When I generate an export file. The csv file that is generated drops the leading zeros in the upc column. 000768251143 becomes 768251143. Is there a way to protect this field during file generation to treat it as a text field so the numbers are not messed up? I did not have this issue with previous easy populate that uses text file instead of csv file. I will greatly appreciate any help in solving this issue as this is a show stopper.

2. The second problem is when I export a file, some of the description fields are empty even though they have content. There's nothing unique about the products that are missing descriptions so I am not sure how tell you how to duplicate this problem. However, it seems that the seem products that are dropping description fields on export remains the same (which makes me think there's something common between them even if I can see it).

I will appreciate any help to get to the bottom of these two issues.

Thanks!

[GAM]

1. I'm not sure what program you're using for your source data but assuming something like Excel, try specifying 'custom' field/column formatting in your spreadsheet program e.g., "000000000000#", to force display of leading zeros.
2. Open your CSV file in something like Notepad (or something better like Notepad++ [free]) and check those descriptions closely. You might have field and/or value delimiters that are either missing or being misinterpreted. Quite likely something in those descriptions is being interpreted as a delimiter and omitting everything thereafter... or something like that.

Best of luck.

Cheers
GAM

[mc12345678]

Okay, I need help with two issues I am experiencing. One is easier to explain and the other I will explain and if you need more clarification to make sense of it please ask questions.

1. The first issue is with upc field. We have some products with upc code starting with zeros. Something like, 000768251143. When I generate an export file. The csv file that is generated drops the leading zeros in the upc column. 000768251143 becomes 768251143. Is there a way to protect this field during file generation to treat it as a text field so the numbers are not messed up? I did not have this issue with previous easy populate that uses text file instead of csv file. I will greatly appreciate any help in solving this issue as this is a show stopper.

2. The second problem is when I export a file, some of the description fields are empty even though they have content. There's nothing unique about the products that are missing descriptions so I am not sure how tell you how to duplicate this problem. However, it seems that the seem products that are dropping description fields on export remains the same (which makes me think there's something common between them even if I can see it).

I will appreciate any help to get to the bottom of these two issues.

Thanks!

1. I'm not sure what program you're using for your source data but assuming something like Excel, try specifying 'custom' field/column formatting in your spreadsheet program e.g., "000000000000#", to force display of leading zeros.
2. Open your CSV file in something like Notepad (or something better like Notepad++ [free]) and check those descriptions closely. You might have field and/or value delimiters that are either missing or being misinterpreted. Quite likely something in those descriptions is being interpreted as a delimiter and omitting everything thereafter... or something like that.

Best of luck.

Cheers
GAM

Totally agree with 1. That's a relatively quick fix and should be available in any spreadsheet program. (Haven't tried the recommended Open Office yet)
2. Deals with export of the file and while there might be an interprelation problem from the text file to the spreadsheet program as suggested, more information is definitely needed.

So assuming that there are three products and four fields of concern, does the folloing "table" represent a possible scenario?

"Field1","Field2","Field3","Field4"
"Data11","Data12",,"Data14"
"Data21","Data22","Data23",
"Data31","Data32","Data33","Data34"

Where DataXY represents the data for item X in column (field) Y or is a complete field not shown?
As far as us duplicating the problem there's a number of ways I can think of but would involve either exporting/downloading your database (or portions of) or providing access to areas of your store. Please do not publicly state your admin directory location/path. Or could do as GAM suggested and evaluate the output of EP before it is "processed" any further. I've seen where a field would have some sort of obnoxious character and cause errors in ZC, so I wouldn't be surprised if there is some errant "special" character(s) in your fields causing the problem.

[BlessIsaacola]

1. I'm not sure what program you're using for your source data but assuming something like Excel, try specifying 'custom' field/column formatting in your spreadsheet program e.g., "000000000000#", to force display of leading zeros.
2. Open your CSV file in something like Notepad (or something better like Notepad++ [free]) and check those descriptions closely. You might have field and/or value delimiters that are either missing or being misinterpreted. Quite likely something in those descriptions is being interpreted as a delimiter and omitting everything thereafter... or something like that.

Best of luck.

Cheers
GAM

I am not sure I explained myself well (even with the first part). The file that's generated from Easy Populate excludes the leading zeros in the csv file. In other words, the leading zeros are gone before I even open the file in Open Office. I know I can add the leading zero's back but that defeats the purpose because it will require me to go through 1,000s of record and add the leading zeros back. Some upc have 1 leading zero, others two leading zero and some three leading zeros. It's not even as simple as just formatting the cell to add leading zeros (because they are different). What I am asking is if it's possible to protect the source data when exporting from our site so Easy Populate does not remove the leading zero upon export?

Thanks!

[mc12345678]

Okay, I need help with two issues I am experiencing. One is easier to explain and the other I will explain and if you need more clarification to make sense of it please ask questions.

1. The first issue is with upc field. We have some products with upc code starting with zeros. Something like, 000768251143. When I generate an export file. The csv file that is generated drops the leading zeros in the upc column. 000768251143 becomes 768251143. Is there a way to protect this field during file generation to treat it as a text field so the numbers are not messed up? I did not have this issue with previous easy populate that uses text file instead of csv file. I will greatly appreciate any help in solving this issue as this is a show stopper.

2. The second problem is when I export a file, some of the description fields are empty even though they have content. There's nothing unique about the products that are missing descriptions so I am not sure how tell you how to duplicate this problem. However, it seems that the seem products that are dropping description fields on export remains the same (which makes me think there's something common between them even if I can see it).

I will appreciate any help to get to the bottom of these two issues.

Thanks!

1. I'm not sure what program you're using for your source data but assuming something like Excel, try specifying 'custom' field/column formatting in your spreadsheet program e.g., "000000000000#", to force display of leading zeros.
2. Open your CSV file in something like Notepad (or something better like Notepad++ [free]) and check those descriptions closely. You might have field and/or value delimiters that are either missing or being misinterpreted. Quite likely something in those descriptions is being interpreted as a delimiter and omitting everything thereafter... or something like that.

Best of luck.

Cheers
GAM

I am not sure I explained myself well (even with the first part). The file that's generated from Easy Populate excludes the leading zeros in the csv file. In other words, the leading zeros are gone before I even open the file in Open Office. I know I can add the leading zero's back but that defeats the purpose because it will require me to go through 1,000s of record and add the leading zeros back. Some upc have 1 leading zero, others two leading zero and some three leading zeros. It's not even as simple as just formatting the cell to add leading zeros (because they are different). What I am asking is if it's possible to protect the source data when exporting from our site so Easy Populate does not remove the leading zero upon export?

Thanks!

I think you missed the point, though I say that before knowing the following detail. Before you open the csv file in the spreadsheet application of choice, when you open the csv file in a text editor, are the zero's there in the quoted text?

The description of forcing the zero's above is one that indicates that the spreadsheet removes the zero's on "import" and that the command would force zero's to be placed not requiring you to find and type them.

If the zero's are in the csv file, EP4 is not the case of them disappearing later, but instead the spreadsheet/import process to the spreadsheet. (In some spreadsheet apps, it is possible to identify a column to be imported as a text field that would keep the zero's in place.)

[mc12345678]

Okay, I need help with two issues I am experiencing. One is easier to explain and the other I will explain and if you need more clarification to make sense of it please ask questions.

1. The first issue is with upc field. We have some products with upc code starting with zeros. Something like, 000768251143. When I generate an export file. The csv file that is generated drops the leading zeros in the upc column. 000768251143 becomes 768251143. Is there a way to protect this field during file generation to treat it as a text field so the numbers are not messed up? I did not have this issue with previous easy populate that uses text file instead of csv file. I will greatly appreciate any help in solving this issue as this is a show stopper.

2. The second problem is when I export a file, some of the description fields are empty even though they have content. There's nothing unique about the products that are missing descriptions so I am not sure how tell you how to duplicate this problem. However, it seems that the seem products that are dropping description fields on export remains the same (which makes me think there's something common between them even if I can see it).

I will appreciate any help to get to the bottom of these two issues.

Thanks!

1. I'm not sure what program you're using for your source data but assuming something like Excel, try specifying 'custom' field/column formatting in your spreadsheet program e.g., "000000000000#", to force display of leading zeros.
2. Open your CSV file in something like Notepad (or something better like Notepad++ [free]) and check those descriptions closely. You might have field and/or value delimiters that are either missing or being misinterpreted. Quite likely something in those descriptions is being interpreted as a delimiter and omitting everything thereafter... or something like that.

Best of luck.

Cheers
GAM

I think you missed the point, though I say that before knowing the following detail. Before you open the csv file in the spreadsheet application of choice, when you open the csv file in a text editor, are the zero's there in the quoted text?

The description of forcing the zero's above is one that indicates that the spreadsheet removes the zero's on "import" and that the command would force zero's to be placed not requiring you to find and type them.

If the zero's are in the csv file, EP4 is not the case of them disappearing later, but instead the spreadsheet/import process to the spreadsheet. (In some spreadsheet apps, it is possible to identify a column to be imported as a text field that would keep the zero's in place.)

Ugh, my apologies. Now that I have reread your response for like the third time, I realize my response would not fix your problem.

Sorry.

[BlessIsaacola]

I think you missed the point, though I say that before knowing the following detail. Before you open the csv file in the spreadsheet application of choice, when you open the csv file in a text editor, are the zero's there in the quoted text?

The description of forcing the zero's above is one that indicates that the spreadsheet removes the zero's on "import" and that the command would force zero's to be placed not requiring you to find and type them.

If the zero's are in the csv file, EP4 is not the case of them disappearing later, but instead the spreadsheet/import process to the spreadsheet. (In some spreadsheet apps, it is possible to identify a column to be imported as a text field that would keep the zero's in place.)

Thank you so much for the clarification. As suggested, I tested the downloaded file in my text editor and everything looks as expected. This is good and it means there's no problem with Easy Populate with regards to the file that's generated. As you rightly suggested, I noticed that Open Office have a function to indicate the column type and once I changed it to text for the upc column everything worked as expected with the leading zeros preserved. This means that issue is resolved with regards to the leading zeros. Thank you so much for your time and troubleshooting with me.

With regards to the description field, I am going to play with it a bit and report back my findings. The strange thing is even though when I open the file in Open Office, some product description are missing in the field, if I close the file and open it in the text editor, the descriptions are there just not showing when I open it Open Office. Strange I know (unless there's a maximum length for a cell in Open Office). I will play around and see what I find out.

Thanks!