[ic] EXCEL

[cfm@maine.com]

On Sun, Apr 15, 2001 at 11:06:46PM -0700, Dan B wrote:
> At 11:47 PM 4/15/2001 +0800, you wrote:
> >Hello Jason,
> >
> >It seems that the line
> >
> >     Database products EXCEL 1
> >
> >only works for catalogs running the internal IC db. How about those of
> >us who utilise MySQL? Can we strip all the extra stuff that Excel ADDs
> >in?
> >
> >How irritating MS can be!

The methods below are not really good enough.  They will remove
valid " marks as well.  Look for adjexcel script on this list;
it repeats every year or so.  Maybe you can find it at google?

I'm thinking that the perl DBD::CSV also handles them
correctly.  (Slap me hard if I'm dreaming.)

Excel will also **drop** trailing, null fields so your
column count will be off.

I wonder what that room looks like where Microsoft people
sit down and figure out just how they can "extend" anything
remotely standard.  :->  Does it have padded walls?

cfm
> 
> Here's some ways to remove the quotes from a file.
> ------------------------------------------
> tr
> ------------------------------------------
>          #cat products.txt | tr -d '"' > products_no_quotes.txt
> ------------------------------------------
>  From inside vi:
> ------------------------------------------
>          :%s/"//g
> ------------------------------------------
> Perl script:
> ------------------------------------------
> #!/usr/bin/perl -w
> 
> 
> # $file is the file you want to strip the '"' from.
> $file = /path/to/filename;
> 
> 
> # $tmp is the file we write w/o the '"'s
> $tmp = /path/to/tmp;
> 
> 
> # If you want to get $file and $tmp from the command line, remove the
> # previous two lines ($file... and $tmp...), and uncomment the next two
> # lines:
> # $file = $ARGV[0];
> # $tmp = $ARGV[1];
> # I would also include a check to make sure that the file exists before
> # attempting to open it (but that's me), like so:
> 
> 
> # if (-e $file) {
> #       if (-e $tmp) {
> 
> 
> # Open $file for reading
> open FILE, "<$file" || die "\nDid not open $file\n";
> # Open $tmp for writing
> open TMP, ">$tmp" || die "\nDid not open $tmp\n";
> # For each line of $file...
> while (<FILE>) {
>          # Substitute any and all '"'s with <NULL>.
>          $line = s/\"//g;
>          # Write the line to $tmp
>          pint TMP $line;
> }  #endwhile
> 
> 
> # And ALWAYS close your file handles!
> close FILE;
> close TMP;
> 
> 
> # If you want, you can move $tmp to $file, overwriting the original...To do so,
> # Include the following line:
> system ("mv $tmp $file");
> 
> 
> #       }  #endif
> # }  #endif
> # END of script
> 
> 
> 
> 
> 
> 
> 
> 
> >--
> >Best regards,
> >  Integricity                            mailto:support@integricity.com
> >
> >Saturday, April 14, 2001, 1:34:48 PM, you wrote:
> >
> >JT> Here it comes again - the EXCEL issue...
> >
> >JT> In several posts, I have seen:
> >
> >JT> Microsoft Excel insists on surrounding any exported field that contains a
> >JT> comma with quotes, even for TAB-delimited exports. Set the EXCEL attribute
> >JT> to 1 to fix this on import:
> >
> >JT>     Database products EXCEL 1
> >
> >JT> However, in 4.6.4, I do not see anything like this, and in fact one poster
> >JT> mentioned that he tried adding it anyway and it caused errors...
> >
> >JT> For 4.6.4, what is the BEST way to get data from EXCEL to an 
> >acceptable tab
> >JT> delimited text file?
> >
> >JT> Thanks in advance...
> >
> >JT> Jason
> >
> >
> >JT> _______________________________________________
> >JT> Interchange-users mailing list
> >JT> Interchange-users@lists.akopia.com
> >JT> http://lists.akopia.com/mailman/listinfo/interchange-users
> >
> >
> >
> >_______________________________________________
> >Interchange-users mailing list
> >Interchange-users@lists.akopia.com
> >http://lists.akopia.com/mailman/listinfo/interchange-users
> 
> Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
> 
> 
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

-- 

Christopher F. Miller, Publisher                             cfm@maine.com
MaineStreet Communications, Inc         208 Portland Road, Gray, ME  04039
1.207.657.5078                                       http://www.maine.com/
Content management, electronic commerce, internet integration, Debian linux