EasyPopulate 4.0 Support Thread

[GAM]

For those that cannot or wish not to stop using Excel and have experienced formatting, character encoding and field delimiter problems with save as 'CSV' from Excel, check out this link to a VBA macro that just might solve your issues and/or make existing frequent conversion to UTF-8 tasks a lot more efficient and reliable...

http://vba-corner.livejournal.com/5733.html


I've trialled this a little today and it seems to work exactly as expected. It converts the fields in each record to that expected by EP4 (comma separated, quote enclosed) and appears to retain the UTF-8 encoding (not tested with foreign characters, only 'special' characters).


For reference and background, I previously had Excel output and import via EP4 working fine but I had to open and 'convert to utf-8' via Notepad++ before uploading to ensure I didn't lose formatting/special characters in my 'descriptions'. Although the file wasn't delimited correctly for EP4, i.e., no quotes around values, it did still work.

Hoping this might others.

Cheers
GAM

[GAM]

For those that cannot or wish not to stop using Excel and have experienced formatting, character encoding and field delimiter problems with save as 'CSV' from Excel, check out this link to a VBA macro that just might solve your issues and/or make existing frequent conversion to UTF-8 tasks a lot more efficient and reliable...

http://vba-corner.livejournal.com/5733.html


I've trialled this a little today and it seems to work exactly as expected. It converts the fields in each record to that expected by EP4 (comma separated, quote enclosed) and appears to retain the UTF-8 encoding (not tested with foreign characters, only 'special' characters).


For reference and background, I previously had Excel output and import via EP4 working fine but I had to open and 'convert to utf-8' via Notepad++ before uploading to ensure I didn't lose formatting/special characters in my 'descriptions'. Although the file wasn't delimited correctly for EP4, i.e., no quotes around values, it did still work.

Hoping this might others.

Cheers
GAM

STOP! BEWARE!
I've realised that the above solution for me is not quite so perfect after all... there is a flaw.

Date formats are changed to dd/mm/yyyy, which is not good at all.


I'll post back if I find a solution to the date issue.

Cheers
GAM

[mc12345678]

STOP! BEWARE!
I've realised that the above solution for me is not quite so perfect after all... there is a flaw.

Date formats are changed to dd/mm/yyyy, which is not good at all.


I'll post back if I find a solution to the date issue.

Cheers
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.

[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