ForumsQuestionsImport CSV to Excel


Import CSV to Excel
Author Message
sandro.krumbein

Posted: May 09, 2011
Score: 0 Reference
Hello!

I use the CSV export do get my completed tasks into Excel to analyse them. The problem is that Excel doesn't naturally use a comma as a separator, but rather the semicolon.

Long story short: I have tried all different ways, but when I have a task, that contains a multiline note, it gets all messed up, because Excel seems to ignore the quotation marks. My current workaround is to convert the CSV file to an XLS file via zamzar.com and use that one

Could it maybe implemented to actually export as a CSV file with ;-separated columns?

Or does anyone know the trick to make Excel do what I want it to do?

Or does anyone at least know of a freeware CSV-2-ExcelCSV or CSV-2-XLS converter I could use?

Thank you very much!

Sandro
Salgud

Posted: May 09, 2011
Score: 0 Reference
Haven't done this, just a shot in the dark. Can you open the CSV file with a text editor, do a Find-and-Replace and change the commas to colons, then open in XL?
Jake

Toodledo Founder
Posted: May 10, 2011
Score: 0 Reference
Well, CSV does stand for "comma separated values", so I think we are doing it right. There should be an option when importing into Excel to change the separator to whatever you want. I have done this many times. You can also change the columns during import via the same import wizard.
sandro.krumbein

Posted: May 10, 2011
Score: 0 Reference
Hey guys and thanks for your replies!

True, by definition, you are doing it right and Microsoft is wrong. I know I can change the separator to a comma during the import process. But Excel ignores the quotation marks around the values of the columns and then tries to separate those, that contain commas, into columns as well

For the same reason the replace wouldn't work either. The semicolons would be considered separators as well and Excel would try to split the text into columns

The actual problem is, that Excel ignores the quoation marks, I guess...

Sandro
offsky - test

Posted: May 10, 2011
Score: 0 Reference
When I use the CSV import wizard, I get to pick my delimiter (tab is the default, so I pick comma), then I am also given the choice to pick the "text qualifier". Double quote is the default, which I leave selected. I then complete the import and my notes are imported correctly with imbedded commas in place. I think maybe you have the wrong value selected for the "text qualifier" option.
sandro.krumbein

Posted: May 10, 2011
Score: 0 Reference
I liked that idea...

But I have checked that again and it's still the same issue. Doesn't recognize what is in quotation marks as one column

Thank you!

Sandro
Jake

Toodledo Founder
Posted: May 10, 2011
Score: 0 Reference
Maybe you have an old copy of Excel, because I just tested this and it worked for me using the steps that offsky1 listed.
piyush_soni

Posted: May 10, 2011
Score: 0 Reference
I really don't understand this. I never changed the default separator, and when I open any CSV file in my MS-Excel 2010, it opens up fine and I'm pretty sure it was the case in Excel 2007. Semi-colons are not considered as separators in my copy.

Are you just double clicking the CSV file to open that in Excel? That's what I'm doing - not importing anything.
piyush_soni

Posted: May 10, 2011
Score: 0 Reference
Also, just tried exporting my tasks as CSV from ToodleDo. Double clicked downloaded file, opens up perfectly fine in my Excel.

(Edit: Didn't see Toodledo's last reply before posting mine)


This message was edited May 10, 2011.
sandro.krumbein

Posted: May 11, 2011
Score: 0 Reference
Have the 2007 version of Excel. And I have tried either: just double clicking, opening from the inside or import from text... None works

Here might be the kicker though and please don't hate me for mentioning it so late. I am German. The Office version is actually English (showing the English menu and stuff), but the operating System (Windows 7) is German. Might that be it? For example there is a difference in the decimal mark between the German and the English version. It's the comma for us and the dot for you, isn't it?

Anyway, thanks for all the effort! I have finally found a solution I am happy with. Here goes: software is called Exportizer, which is able to import and export all kinds of table data. CSV and XLS are amonst them. Now I have created a batch file, which does this for me (part was done by the software). Now the conversion is done by simply dragging the downloaded CSV onto my batch file. Voila, there is my CSV file, which is now compatible with my Excel version

Here is the batch code, for those, who would wonder how to get the drag and drop going:
"C:\Program Files (x86)\Exportizer\exptizer.exe" /ado /export /ExportType=CSV /ExportMode=CREATE_OR_REPLACE+INSERT /separator=; /textSchema=0 /IncludeColNames /DelimitAllFields /CloseWhenDone /minimize /OpenDestination /LineTerminator=WINDOWS /IncludeMemo %1 C:\Users\Administrator\Documents\Lokalim\Auswertung\Zeit\toodledo_completed.csv

The %1 is the placeholder for the name of the dropped file

Thank you!
Sandro
You cannot reply yet

U Back to topic home

R Post a reply

To participate in these forums, you must be signed in.