Suggestions for Excel Csv Behavior?

[bmilton]

After I export my bulk sheet from Excel 2010 to csv format, I end up having to use a text editor to find and replace added characters.

I am using ^ as a separator.

Excel turns this:

<a href="http://mysite.com/">My Anchor Text</a>

Into this:

"<a href=""http://mysite.com/"">My Anchor Text</a>"

I am thinking this is default Excel behavior. Should ImportX account for this or is it just the way it is to clean up the exported csv before import?

Thanks.

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[Mark-H]

It's probably thinking the double quotes are used to keep a longer string together, like your multi line FR in #23

Excel is only one way to create CSV format (another one I can imagine being used often with ImportX is exporting a database to CSV using PhpMyAdmin), so I'm reluctant to creating a fix for something like this..

How are you trying to use ImportX? lol. Perhaps there's a better way to achieve what you need with an addon designed to do that.. if I know what you're looking for perhaps I know something better.

[bmilton]

I'm migrating a bunch of static HTML pages into ModX. I've used a variety of crawling/scraping tools and manual copy and paste to create an Excel bulk sheet that has each page on a separate row with all of the necessary content and tv fields needed for ModX.

ImportX is saving me a lot of time when it comes to getting these into ModX as actual resources.

While doing this, I realized that it is a lot easier to maintain a hard copy of all your resources and field values in Excel than it is ModX. There is the "Batcher" add-in for ModX which allows some global resource changes, but it doesn't support all of the built-in document fields such as changing the content_type to "HTM" for example.

I've been using Batcher to delete all of the resources that are using my new template and then re-importing them again from the bulk sheet if I need to make a global change across all of those resources. Batcher could do some of this for me, but doesn't support all fields and there is a lot to be said for having all the content in one place as a master copy that can easily be updated with find and replace or copy and paste same values across a column.

Thanks Mark.

[lithiumlab]

Empty cells from Excel generates errors on import. As this columns are being omitted by row. Even filling empty cells with spaces doesn't produce desired results.

How is importX supposed to understand a field is being left empty on purpose?

[Mark-H]

@lithiumlab Sorry, seems I missed your reply somehow.

If a field is empty it still needs the separators. This should be valid:

 pagetitle;longtitle;tv4;content
 My Title;This is My Title;;This is the content of my imported resource. How wicked is that!

If Excel drops empty cells completely, how does it make sense of the csv when importing it back in? There has to be some way it says "hey, there's empty cells here"?

[Mark-H]

It seems like Excel and PhpMyAdmin like to wrap data in quotes, I suppose that could be a legit functionality to add.

[lithiumlab]

Hi Mark;

Thanks for taking time to see suggestions. I've found importX is very useful for a bunch of situations, i'm really happy having this tool at hand. :)

Take a look at this awesome script by @shancarter for bringing data from Excel using only a direct copy paste. It works pretty good, seems the double quotes issue is solved and also can convert to XML, JSON, even PHP Arrays. I see some handy features that can help for future versions of ImporX / (exportX, maybe? :)

https://github.com/shancarter/Mr-Data-Converter