Re: Strange database format import
This WebDNA talk-list message is from 2000
It keeps the original formatting.
numero = 27755
interpreted = N
texte = Dennis - As long as that carriage return after the first line is not present in the data, you have classic quote/comma delimited data there. Are you using a HDR file so WebCatalog knows how many records there are? It looks like WebCat is _not_ recognizing the embedded comma as such, and parsing the rest of the columns as a single field.My immediate reaction is to write a quick Perl program to parse it out as tab delimited. Do you have access to an environment where Perl will run (pretty much anything above a toaster at this point)? Save the following code in a program called parse.pl for example: use Text::ParseWords; use English; $OUTPUT_FIELD_SEPARATOR = \t; while ( <> ) { print STDOUT quotewords(,,0,$ARG); }and run it like this: perl parse.pl < infile.txt > outfile.dbOr you could try the following single line (remove any e-mail CR):perl -MText::ParseWords -ne print join qq{\t},quotewords(',',0,$_); infile.txt > outfile.dbText::ParseWords and English are part of the standard Perl distribution.Hope this helpsJohn Peacock ____________________Reply Separator____________________ Subject: Strange database format import Author:
(WebCatalog Talk) Date: 2/17/00 11:29 PMI have a customer that is requesting that I setup a specific database for her, that can be imported, and added to her site. However, the database appaears to be in an inconsistant format. If it were as simple as making a one-time change to the database, it would be an easy fix. However, she is going to receive an updated copy of this database every week. And, of course, she wants me to automate as much of the import as possible.WebCat seems to do okay with it up until a point. The database is a comma-delimited database, and the first several columns are denoted by quotes and seperated by commas. Even when it gets to a point where commas appear within the quotes, WebCat does not have a problem. But, when it gets to the last several colums of the database, which have no quotes, and are separated only by commas, WebCat chokes on the database. Here is an entry from within this database:CANADA,01/01/00 - 12/31/00,2-A,SME,Wool Suits, M&B,5016780,20330,0.40524,0.064045,0.06754,0.,01/13/00The entries should be as follows:CANADA 01/01/00 - 12/31/00 2-A SME Wool Suits, M&B 5016780 20330 0.40524 0.064045 0.06754 0. 01/13/00Instead, the entries come out as:CANADA 01/01/00 - 12/31/00 2-A SME Wool Suits, M&B,5016780,20330,0.40524,0.064045,0.06754,0.,01/13/00CANADA(Notice in the last line, it starts reading the next record).Is there any way that webcat can be instructed to use this database? Or, is there any way to use WebCat to convert the database? Unfortunately, I have already asked the producer of the database if they could send me the database in a more consistant format (preferably tab delimited). They refused, on grounds that the database is sent to numerous others every week as well (although I am probably the only one importing it into a website).Any solutions will be appreciated.Thanks,Dennis ------------------------------------------------------------- Brought to you by CommuniGate Pro - The Buzz Word Compliant Messaging Server. To end your Mail problems go to .This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to
Associated Messages, from the most recent to the oldest:
Dennis - As long as that carriage return after the first line is not present in the data, you have classic quote/comma delimited data there. Are you using a HDR file so WebCatalog knows how many records there are? It looks like WebCat is _not_ recognizing the embedded comma as such, and parsing the rest of the columns as a single field.My immediate reaction is to write a quick Perl program to parse it out as tab delimited. Do you have access to an environment where Perl will run (pretty much anything above a toaster at this point)? Save the following code in a program called parse.pl for example: use Text::ParseWords; use English; $OUTPUT_FIELD_SEPARATOR = \t; while ( <> ) { print STDOUT quotewords(,,0,$ARG); }and run it like this: perl parse.pl < infile.txt > outfile.dbOr you could try the following single line (remove any e-mail CR):perl -MText::ParseWords -ne print join qq{\t},quotewords(',',0,$_); infile.txt > outfile.dbText::ParseWords and English are part of the standard Perl distribution.Hope this helpsJohn Peacock ____________________Reply Separator____________________ Subject: Strange Database format import Author: (WebCatalog Talk) Date: 2/17/00 11:29 PMI have a customer that is requesting that I setup a specific database for her, that can be imported, and added to her site. However, the database appaears to be in an inconsistant format. If it were as simple as making a one-time change to the database, it would be an easy fix. However, she is going to receive an updated copy of this database every week. And, of course, she wants me to automate as much of the import as possible.WebCat seems to do okay with it up until a point. The database is a comma-delimited database, and the first several columns are denoted by quotes and seperated by commas. Even when it gets to a point where commas appear within the quotes, WebCat does not have a problem. But, when it gets to the last several colums of the database, which have no quotes, and are separated only by commas, WebCat chokes on the database. Here is an entry from within this database:CANADA,01/01/00 - 12/31/00,2-A,SME,Wool Suits, M&B,5016780,20330,0.40524,0.064045,0.06754,0.,01/13/00The entries should be as follows:CANADA 01/01/00 - 12/31/00 2-A SME Wool Suits, M&B 5016780 20330 0.40524 0.064045 0.06754 0. 01/13/00Instead, the entries come out as:CANADA 01/01/00 - 12/31/00 2-A SME Wool Suits, M&B,5016780,20330,0.40524,0.064045,0.06754,0.,01/13/00CANADA(Notice in the last line, it starts reading the next record).Is there any way that webcat can be instructed to use this database? Or, is there any way to use WebCat to convert the database? Unfortunately, I have already asked the producer of the database if they could send me the database in a more consistant format (preferably tab delimited). They refused, on grounds that the database is sent to numerous others every week as well (although I am probably the only one importing it into a website).Any solutions will be appreciated.Thanks,Dennis ------------------------------------------------------------- Brought to you by CommuniGate Pro - The Buzz Word Compliant Messaging Server. To end your Mail problems go to .This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to
jpeacock@univpress.com
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
CyberCash book/ship model (2000)
OT-Load balance (2004)
WebDNA on Red Hat Enterprise Linux 4 x64 (2006)
Allow-Deny: Solved with [Getchars] (1998)
Fwd: more ! testing (1997)
Probably a very simple question? (1997)
Speeding up my [showif] performance (1999)
shipcost (1997)
Include a big block of text (1997)
TaxTotal Problem (1997)
accountnum using [listwords] (2001)
frames & carts (1997)
[SearchString] usage (1997)
WebCat2b13MacPlugin - nested [xxx] contexts (1997)
WebCatalog can't find database (1997)
Re:How to Display text in empty fields (1997)
Using [detete] (1998)
New Guy (2000)
Sendmail doesn't (was Queertrons?) (1997)
Credit card arrangement (2005)