Memory Import Errors

[Controls Guy]

That IS cool!   

[PLCwannabe]

Yup - all those extra double quotes written by EXCEL mucked up the IMPORT of Designer.

I "tweaked" the Excel export file in a TEXT editor.

I basically re-formatted the doube quote delimiters done by Excel.

Excel changed a simple start/end double quote of the data to
" ""<data>"""

So in my favorite text editor, I replaced
double-quote space double-quote double-quote
that is at the beginning of every cell with
double-quote
In otherwords, I replaced
" ""
with
"

Then I replaced the three double quotes at the end of every cell with just one double-quote.
In otherwords, I replaced
"""
with
"

I was able to import that file via File-Import->Memory Data
(see attached Memory View of my SS block)

Could you run the attached .txt file through your favorite text editor and repost? I'm not getting anywhere with this.

[franji1]

Could you run the attached .txt file through your favorite text editor and repost? I'm not getting anywhere with this.

Start with a file that has the commas.  Don't export with TAB separator, export with a COMMA separator.  That will probably help you figure out what to do.

First, what version of Excel are you using?
Please provide the exact steps you are doing in Excel
1. to open the .CSV file
2. to save the .CSV file (some kind of export dialog shows up?)

Do you have Notepad++?  I recommend that text editor.  I can definitely help you with that.  Even if it's not Notepad++, it's not that hard.

We can probably figure this out.

[PLCwannabe]

Office Home & student 2019.
 1    I just double-click to open.
2    I click on the save icon to save.

I was able to download Notepad ++  and eliminate all the double-quotes as you described. Which allowed me to import correctly.

But why is Domore exporting as Excel files, if Excel can't format the data properly?

[franji1]

But why is Domore exporting as Excel files, if Excel can't format the data properly?

It's just .CSV (Comma Separated Variable) text file, which can work with Excel, Notepad, even database software.  It's a very common and simple format for import/export of tables.

Excel can - it's just that I guess they decided to make it more complicated than it used to be.

[Controls Guy]

Yeah, its Excel's problem.  CSVs aren't really "Excel files" because they've been in use for stuff like this since before Excel existed, but since its a file format for tabular data, it ends up associated with your spreadsheet.  Excel created this problem by phoning it in. You can override the association in Windows if you want (like to Notepad++, which I also use and like), but just use free Planmaker or Soft Office or Libre Office.   I don't think any of them do this.

[franji1]

Yeah, its Excel's problem.  CSVs aren't really "Excel files" because they've been in use for stuff like this since before Excel existed, but since its a file format for tabular data, it ends up associated with your spreadsheet.  Excel created this problem by phoning it in. You can override the association in Windows if you want (like to Notepad++, which I also use and like), but just use free Planmaker or Soft Office or Libre Office.   I don't think any of them do this.

I just "duckduckwent" best csv editor and one of the ones that popped up in this article (https://techcult.com/best-csv-editor-for-windows/) was Quattro Pro!  It was the first GUI based spreadsheet (before Excel for Windows!) in the late 80's by Borland!

Still not sure what's best CSV editor, but anything is better than what Excel is currently doing.  I could not even find an Export Wizard in Excel (they have an import/convert wizard - why not an export/convert wizard?).  They used to.  For example, if none of your text contains commas, then offer the option to NOT wrap text cells in double quotes!  Or let me choose single quote (over double quote).  Or use semicolon (or TAB?) instead of comma.  Or format date fields as text (yyyy-mm-dd or mm-dd-yyyy or mm-dd-yy or ?).  Sad, sad, sad.

[Bolt]

In Excel, under the Data tab, there is button called Text to Columns. The allows you to define the delimiters (tab, semicolon, comma, space, etc) and preview the effects before implementing. I also think that the next time you open a "text" file, it will use the same delimiter setup as previously used, so that's probably your issue.

If you right click the file in Windows Explorer, and go to Open with... you can select a different program, and even make it permanent. If you have Notepad++ installed, it should even list Edit with Notepad++ right in the drop down menu already.

[Bolt]

I just "duckduckwent" best csv editor and one of the ones that popped up in this article (https://techcult.com/best-csv-editor-for-windows/) was Quattro Pro!  It was the first GUI based spreadsheet (before Excel for Windows!) in the late 80's by Borland!

That website is a joke, they list Windows Notepad but not Notepad++? I'm not saying Notepad++ is the be all, end all csv tool, but it's miles ahead of Notepad...

[franji1]

That website is a joke, they list Windows Notepad but not Notepad++? I'm not saying Notepad++ is the be all, end all csv tool, but it's miles ahead of Notepad...

Yeah, I saw that too.  Caveat Emptor.  My primary purpose was to point out Quattro Pro.

Trivia - the reason why Borland called it Quattro Pro was because Excel was NOT the #1 spreadsheet at the time.  It was Lotus Uno-Dos-Tres 

[Controls Guy]

I had Quattro.  I remember that as being the program that made the 3D UI popular (in DOS!). Borland did a lot of cool stuff back then.  Update - Oh, just read through your previous post and saw you already covered that!   

I also knew a guy that used 1-2-3 for everything.   Spreadsheets, letters, whatever!

[Controls Guy]

Seriously, use Planmaker.  Years ago it could read XLS & XLSX files, but could only write XLS for Excel formats.  For about 5 years now, it can both read and write XLSX, and you can even set that as the default file format instead of the native PMDX.  I have the paid (like $50) version cause I need some of the features, but most would be fine with the free version.  I like the WP from Soft Office (Write?) better than Textmaker, though.