Re: Import from Excel file

[randy--- via 4D_Tech]

Ferdinando,

Export it to CSV in Excel and then import the text file.

> On Apr 22, 2024, at 3:36 AM, stardata.info via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Hi all,
> 
> I use 4D V19, i need to import data from some excell files without use a form 
> with a 4D View Pro area.
> 
> Someone know how i can do this?
> 
> Thanks
> 
> Ferdinando
> 
> **
> 4D Internet Users Group (4D iNUG)
> New Forum: https://discuss.4D.com
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

Randy Kaempen
Intellex Corporation

**
4D Internet Users Group (4D iNUG)
New Forum: https://discuss.4D.com
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Import from Excel file

[stardata.info via 4D_Tech]

Hi all,

I use 4D V19, i need to import data from some excell files without use a 
form with a 4D View Pro area.


Someone know how i can do this?

Thanks

Ferdinando

**
4D Internet Users Group (4D iNUG)
New Forum: https://discuss.4D.com
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Jeremy French via 4D_Tech]

Hi Tim,

Just tried the Tech Note using v16 without success.

Apparently PHPExcel is deprecated. It’s successor is PHPSpreadsheet at:

https://github.com/PHPOffice 

Could you possibly do a new edition of your Tech Note using PHPSpreadsheet and 
v16?

Best regards,
Jeremy


> On Mar 7, 2018, at 12:15 PM, Timothy Penner via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> I am not sure if it still works, but there is this tech note available:
> 
> Tech Note: PHPExcel Library with 4D v12
> Published On: April 15, 2011
> http://kb.4d.com/assetid=76312

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Keith Goebel via 4D_Tech]

Pat,
> Yes ... We wrote a procedure to import csv files. My point was that you can't 
> just use the basic 4D Import option to import such files.

We don’t use 4D’s import, so I can’t comment on whether it works correctly or 
not.

Chip,
> 
>> the problem I have found with csv is that different software creates 
>> different formats.
>> sometimes, everything is quoted, sometimes only certain values are quoted, 
>> and similar to the problem of a tab delimited text file, if the text 
>> contains a equation mark(s) everything gets screwed.

The exporting software is supposed to enclose strings and text inside “.
The importing software is supposed to be able to handle anything in a string or 
text cell, provided it is inside the enclosing ".

Agreed - sometimes Excel does not follow the cvs rules properly when it 
exports. 
But what should we expect? It’s not like they’re a large company with multiple 
programmers at their disposal… bless their little cotton socks!
Cheers, Keith
There are 3 kinds of mathematician. Those who can count, and those who can't.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Chip Scheide via 4D_Tech]

the problem I have found with csv is that different software creates different 
formats.
sometimes, everything is quoted, sometimes only certain values are quoted, and 
similar to the problem of a tab delimited text file, if the text contains a 
equation mark(s) everything gets screwed.

In my Txtutl component, 
(https://www.dropbox.com/s/wj3w55p3gvzp5h5/Txtutl_Component_v1.0.0.zip?dl=0) 
there is an option export records to text, and a corresponding import, 
where returns, line feeds, and tabs which maybe embedded in the data are 
replaced with the following actual characters:
, , and 
this eliminates the issue of embedded field or line markers.

There is another way, slightly more generic way to resolve the issue of 
embedded delimiter characters, replace the default delimiters!

I did a bit of google-fu, and came across these ideas for saving excel files as 
cdv with custom delimiters:
https://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/
TLDR;
go to control panel/system preference change the 'List separator' character.
I imagine it would not be hard to write an applescript, or batch file or VBA 
script (embedded in the excel file(s)) to commands to do this change and then 
switch it back to default.

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/e1a75bbc-82ab-4afb-a082-731dacb04a3e/export-to-csv-but-with-a-custom-delimiter?forum=ITCG
TLDR;
Run the script given in the thread

Chip


> Pat,
> As I understand it, in a cvs file, all string values should be 
> enclosed inside “.
> This means that a CR is simply part of the cell’s string.
> If you open the exported file with a text editor, you should see the 
> CR enclosed inside “.
> If it isn’t, the Excel export is not working correctly.
> If it is enclosed, I would be looking at the code doing the import - 
> it is not working correctly.
> Cheers, Keith
> 
>> Keith - if you export from Excel to csv, you still have the problem with
>> embedded Return characters.
> 
>>> Sometimes an Excel csv export doesn’t comply completely with the cvs
>>> rules, but mostly it does.
> 
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

Hell is other people 
 Jean-Paul Sartre
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Keith Goebel via 4D_Tech]

Pat,
As I understand it, in a cvs file, all string values should be enclosed inside 
“.
This means that a CR is simply part of the cell’s string.
If you open the exported file with a text editor, you should see the CR 
enclosed inside “.
If it isn’t, the Excel export is not working correctly.
If it is enclosed, I would be looking at the code doing the import - it is not 
working correctly.
Cheers, Keith

> Keith - if you export from Excel to csv, you still have the problem with
> embedded Return characters.

>> Sometimes an Excel csv export doesn’t comply completely with the cvs
>> rules, but mostly it does.

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Keisuke Miyako via 4D_Tech]

F.Y.I.

I've posted a "Match regex" solution to process CSV (read and write).
quotes, carriage returns, missing columns and other perks are handled.

https://github.com/miyako/4d-component-csv

in general, it is easier if you could use TSV instead of CSV,
as the format is more compatible with the standard 4D dialogs,
except for vertical tabs (which corresponds to in-cell carriage returns).

I am surprised to hear that Numbers does not export TSV,
as far as I know, it does import and display TSV properly, even vertical tabs.



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Dani Beaubien via 4D_Tech]

When exporting to csv from Excel, the cells that contain commas, double quotes 
and/or carriage returns will be wrapped by double quotes. The routine that is 
used to import csv file should be paying attention to the surrounding quotes 
and handling embedded returns. Any internal double quotes are escaped as double 
double quotes (pulling that from memory).

The following is a sample of a 3 row csv file exported from Excel.:
ROW 1,a,b,c
ROW2, 1,"2
3",4
ROW 3,d,e,f

Them 3rd cell in ROW 2 has a carriage return between the 2 and 3.

It is more of a challenge to import CSV because you can’t assume that the EOL 
character is truly the EOL. It might be embedded within a cell. if you are 
using CSV files you need to be able to handle this scenario.

Hopefully that helps a bit.

Dani


> On Mar 7, 2018, at 2:20 PM, Pat Bensky via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Keith - if you export from Excel to csv, you still have the problem with
> embedded Return characters.
> 
> Pat
> 
> 
> On 7 March 2018 at 20:48, Keith Goebel via 4D_Tech <4d_tech@lists.4d.com>
> wrote:
> 
>> Ferdinando ,
>> If you convert the Excel file to csv, you shouldn't have any problems with
>> cells containing carriage returns/line feeds when you import into 4D.
>> I assume 4D’s built-in import can handle csv files - I haven’t checked -
>> we use special import and export code to handle csv so we don’t use 4D’s
>> built-in stuff.
>> 
>> Sometimes an Excel csv export doesn’t comply completely with the cvs
>> rules, but mostly it does.
>> HTH, Keith
>> **
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
>> 
> 
> 
> 
> -- 
> *
> CatBase - Top Dog in Data Publishing
> tel: +44 (0) 207 118 7889
> w: http://www.catbase.com
> skype: pat.bensky
> *
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Import from Excel

[Keith Goebel via 4D_Tech]

Ferdinando ,
If you convert the Excel file to csv, you shouldn't have any problems with 
cells containing carriage returns/line feeds when you import into 4D.
I assume 4D’s built-in import can handle csv files - I haven’t checked - we use 
special import and export code to handle csv so we don’t use 4D’s built-in 
stuff.

Sometimes an Excel csv export doesn’t comply completely with the cvs rules, but 
mostly it does.
HTH, Keith
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[stardata.info via 4D_Tech]

Thanks

/Ferdinando/


Il 07/03/2018 18:15, Timothy Penner ha scritto:

I am not sure if it still works, but there is this tech note available:

Tech Note: PHPExcel Library with 4D v12
Published On: April 15, 2011
http://kb.4d.com/assetid=76312

-Tim PENNER


Timothy Penner
Senior Technical Services Engineer

4D Inc
95 S. Market Street, Suite #240
CA 95113 San Jose
United States

Telephone : +1-408-557-4600
Standard :  +1-408-557-4600
Fax :   +1-408-271-5080
Email : tpen...@4d.com
Web :   www.4D.com




**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Douglas Cryer via 4D_Tech]

Another vote for Pluggers Excel plugin.
https://www.pluggers.nl/product/xl-plugin/ 


You will not regret it for creating and reading excel files it is simply the 
best, just as you would expect from Pluggers.

For my usage it has paid for itself over and over and just keeps giving…

Regards, Dougie


telekinetix Limited- J. Douglas Cryer
Phone : 01234 761759  Mobile : 07973 675 218
2nd Floor Broadway House, 4-6 The Broadway, Bedford MK40 2TE
Email : jdcr...@telekinetix.com   Web : 
http://www.telekinetix.com 


**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Import from Excel

[Timothy Penner via 4D_Tech]

I am not sure if it still works, but there is this tech note available:

Tech Note: PHPExcel Library with 4D v12
Published On: April 15, 2011
http://kb.4d.com/assetid=76312

-Tim PENNER



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[John Baughman via 4D_Tech]

If you are using Numbers on a Mac, there is no way to export to a tab delimited 
text file, only CSV. Many have complained, but Apple does not listen.

The work around is to select the content to be exported in Numbers and copy it 
to the clipboard. Then past into a plain text TextEdit document. The TextEdit 
document must have been converted to plain text before pasting the content. Be 
sure that there is CR after the last line and save the document.

Hope this saves someone some time. I do this all the time as I have grown to 
really like Numbers, but using one of the suggested plugins is really the way 
to go.

John

On Mar 7, 2018, at 5:55 AM, Chuck Miller via 4D_Tech <4d_tech@lists.4d.com> 
wrote:

> On Mar 7, 2018, at 5:36 AM, Koen Van Hooreweghe via 4D_Tech 
> <4d_tech@lists.4d.com > wrote:
> 
> Hi Ferdinando,
> 
> Export from Excel to text file (comma delimited, tab delimited). And then use 
> the 4D import tool or write your own code (receive packet,…)
> 
> HTH
> Koen

John Baughman
Kailua, Hawaii
(808) 262-0328
john...@hawaii.rr.com





**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Chuck Miller via 4D_Tech]

No need to export. Save as a text file and you can import that

Regards

Chuck

 Chuck Miller Voice: (617) 739-0306
 Informed Solutions, Inc. Fax: (617) 232-1064   
 mailto:cjmillerinformed-solutions.com 
 Brookline, MA 02446 USA Registered 4D Developer
   Providers of 4D and Sybase connectivity
  http://www.informed-solutions.com  

This message and any attached documents contain information which may be 
confidential, subject to privilege or exempt from disclosure under applicable 
law.  These materials are intended only for the use of the intended recipient. 
If you are not the intended recipient of this transmission, you are hereby 
notified that any distribution, disclosure, printing, copying, storage, 
modification or the taking of any action in reliance upon this transmission is 
strictly prohibited.  Delivery of this message to any person other than the 
intended recipient shall not compromise or waive such confidentiality, 
privilege or exemption from disclosure as to this communication. 

> On Mar 7, 2018, at 5:36 AM, Koen Van Hooreweghe via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> Hi Ferdinando,
> 
> Export from Excel to text file (comma delimited, tab delimited). And then use 
> the 4D import tool or write your own code (receive packet,…)
> 
> HTH
> Koen

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Import from Excel

[Randy Engle via 4D_Tech]

Hi Ferdinando,

I second Pat Bensky's suggestions of Rob Laveaux's Pluggers EXCEL Plugin.
http://www.pluggers.nl/product/xl-plugin

There is a reasonable cost for this plugin, but it provides serious 
capabilities to work with Excel files.
This is what we use exclusively.

As well there are some free Excel plugins from Miyako :  
https://github.com/miyako
These are great for some quick Excel features.

But if you need real control, Pluggers is it.

Randy Engle, Director
XC2 Software LLC – XC2LIVE!



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Pat Bensky via 4D_Tech]

We use Pluggers Excel plugin - works really well for both importing and
creating Excel documents:
https://www.pluggers.nl/product/xl-plugin/

The problem with exporting form Excel to a text file is if any of the cells
contain return or line feed characters, that will mess everything up as 4D
will then think that each of those returns or line feeds represents the end
of a row (record).

No problem if you don't have any text fields like that in your spreadsheet
:)

Pat

On 7 March 2018 at 10:36, Koen Van Hooreweghe via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> Hi Ferdinando,
>
> Export from Excel to text file (comma delimited, tab delimited). And then
> use the 4D import tool or write your own code (receive packet,…)
>
> HTH
> Koen
>
> > Op 7 mrt. 2018, om 11:06 heeft stardata.info via 4D_Tech <
> 4d_tech@lists.4d.com> het volgende geschreven:
> >
> > I use 4D V15 or V16 someone know how i can to import from excel?
>
>
>
> 
> Compass bvba
> Koen Van Hooreweghe
> Kloosterstraat 65
> 9910 Knesselare
> Belgium
> tel +32 495 511.653
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
>



-- 
*
CatBase - Top Dog in Data Publishing
tel: +44 (0) 207 118 7889
w: http://www.catbase.com
skype: pat.bensky
*
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Import from Excel

[Koen Van Hooreweghe via 4D_Tech]

Hi Ferdinando,

Export from Excel to text file (comma delimited, tab delimited). And then use 
the 4D import tool or write your own code (receive packet,…)

HTH
Koen
 
> Op 7 mrt. 2018, om 11:06 heeft stardata.info via 4D_Tech 
> <4d_tech@lists.4d.com> het volgende geschreven:
> 
> I use 4D V15 or V16 someone know how i can to import from excel?




Compass bvba
Koen Van Hooreweghe
Kloosterstraat 65
9910 Knesselare
Belgium
tel +32 495 511.653

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Import from Excel

[stardata.info via 4D_Tech]

Hi all,

I use 4D V15 or V16 someone know how i can to import from excel?

Thanks
Ferdinando
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**