[WebDNA] Re: csv import and mer format

This WebDNA talk-list message is from

2010


It keeps the original formatting.
numero = 105499
interpreted = N
texte = --000325559af2669be4048abb258a Content-Type: text/plain; charset=UTF-8 Hi all, I have been following some of the recent threads on CSV import and using .mer format in WebDNA. It's something I'm going to have to deal with a lot with a tool we are building so figured I had better jump in and see what could be done. Firstly I have no problem working with .mer directly in WebDNA - the only amendment required was to change the suffix from .mer to .txt (or .db). In my case it is a 6000 record export from FileMaker auto ftp'd up to the server each night. Secondly on CSV files - using the explanation of the CSV format on this page: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm I have coded up the bit of code below which results in a file that can be handled by WebDNA - albeit without valid fieldnames. That's something I'll work on next. But I just wanted to get this onto the list to see if others could test the conversion on different and larger CSV files. - Tom [!] [/!] [writefile file=test.csv]John,Doe,120 jefferson st.,Riverside, NJ, 08075 Jack,McGinnis,220 hobo Av.,Phila, PA,09119 "John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075 Stephen,Tyler,"7452 Terrace ""At the Plaza"", somewhere road",SomeTown,SD, 91234 ,Blankman,,SomeTown, SD, 00298 "Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123 [/writefile] [table name=clearRestrictedChars&fields=from,to] | ~ [/table] [table name=val3replace&fields=from,to] %2C | %0A %0D %0B ~ [/table] [table name=val4replace&fields=from,to] %2C %09 | %2C [/table] [text show=T]value1=[convertchars table=clearRestrictedChars][grep search=""&replace=%26quot;][include file=test.csv][/grep][/convertchars][/text]

[text show=T]value2=[grep search="([^"]*)"&replace=|~\1~|][value1][/grep][/text]

[text show=T]value3=[listwords words=[url][value2][/url]&delimiters=|][!] [/!][hideif [url][word][/url]~[url]~[/url]][word][/hideif][!] [/!][showif [url][word][/url]~[url]~[/url]][convertchars table=val3replace][word][/convertchars][/showif][!] [/!][/listwords][/text]

[text show=T]value4=[listwords words=[url][value3][/url]&delimiters=%0D%0A][convertchars table=val4replace][word][/convertchars] [/listwords][/text] [writefile file=csvconverted.db][value4][/writefile] --000325559af2669be4048abb258a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi all,

I have been following some of the recent threads on CSV= import and using .mer format in WebDNA. =C2=A0It's something I'm g= oing to have to deal with a lot with a tool we are building so figured I ha= d better jump in and see what could be done.

Firstly I have no problem working with .mer directly in= WebDNA - the only amendment required was to change the suffix from .mer to= .txt (or .db). =C2=A0 In my case it is a 6000 record export from FileMaker= auto ftp'd up to the server each night.

Secondly on CSV files - using the explanation of the CS= V format on this page:

=C2=A0=C2=A0 =C2=A0http://www.creati= vyst.com/Doc/Articles/CSV/CSV01.htm

I have coded up the bit of code below which results in = a file that can be handled by WebDNA - albeit without valid fieldnames. =C2= =A0 That's something I'll work on next. =C2=A0But I just wanted to = get this onto the list to see if others could test the conversion on differ= ent and larger CSV files.

- Tom



[!]<!-- HAS_WEBDNA_TAGS -->
[/!]

<= /div>
[writefile file=3Dtest.csv]John,Doe,120 jefferson st.,Riverside, = NJ, 08075
Jack,McGinnis,220 hobo Av.,Phila, PA,09119
"John "= "Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075=
Stephen,Tyler,"7452 Terrace ""At the Plaza"&= quot;,
somewhere road",SomeTown,SD, 91234
,Blankman,,SomeTown,= SD, 00298
"Joan ""the bone"", Anne"= ;,Jet,"9th, at Terrace plc",Desert City,CO,00123
[/writ= efile]

[table name=3DclearRestrictedChars&fields=3Dfrom,to= ]
|
~
[/table]

[table name=3Dval3replace&fields= =3Dfrom,to]
%2C|
%0A
%0D%0= B
~
[/table]

[table name=3Dval4replace&a= mp;fields=3Dfrom,to]
%2C%0= 9
|%2C
[/table]


[text sho= w=3DT]value1=3D[convertchars table=3DclearRestrictedChars][grep search=3D&q= uot;"&replace=3D%26quot;][include file=3Dtest.csv][/grep][/convert= chars][/text]<br /><br />

[text show=3DT]value2=3D[grep search=3D"([^"]= *)"&replace=3D|~\1~|][value1][/grep][/text]<br /><br />= ;

[text show=3DT]value3=3D[listwords words=3D[url]= [value2][/url]&delimiters=3D|][!]

[/!][hideif [url][word][/url]~[url]~[/url]][word][/hide= if][!]
[/!][showif [url][word][/url]~[url]~[/url]][convertchars t= able=3Dval3replace][word][/convertchars][/showif][!]

[/!][/listwords][/text]<br /><br />

[text show=3DT]value4=3D[listwords words=3D[url][value3][/url]&delim= iters=3D%0D%0A][convertchars table=3Dval4replace][word][/convertchars]
[/listwords][/text]

[writefile file=3Dcsvconverted= .db][value4][/writefile]

--000325559af2669be4048abb258a-- Associated Messages, from the most recent to the oldest:

    
--000325559af2669be4048abb258a Content-Type: text/plain; charset=UTF-8 Hi all, I have been following some of the recent threads on CSV import and using .mer format in WebDNA. It's something I'm going to have to deal with a lot with a tool we are building so figured I had better jump in and see what could be done. Firstly I have no problem working with .mer directly in WebDNA - the only amendment required was to change the suffix from .mer to .txt (or .db). In my case it is a 6000 record export from FileMaker auto ftp'd up to the server each night. Secondly on CSV files - using the explanation of the CSV format on this page: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm I have coded up the bit of code below which results in a file that can be handled by WebDNA - albeit without valid fieldnames. That's something I'll work on next. But I just wanted to get this onto the list to see if others could test the conversion on different and larger CSV files. - Tom [!] [/!] [writefile file=test.csv]John,Doe,120 jefferson st.,Riverside, NJ, 08075 Jack,McGinnis,220 hobo Av.,Phila, PA,09119 "John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075 Stephen,Tyler,"7452 Terrace ""At the Plaza"", somewhere road",SomeTown,SD, 91234 ,Blankman,,SomeTown, SD, 00298 "Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123 [/writefile] [table name=clearRestrictedChars&fields=from,to] | ~ [/table] [table name=val3replace&fields=from,to] %2C | %0A %0D %0B ~ [/table] [table name=val4replace&fields=from,to] %2C %09 | %2C [/table] [text show=T]value1=[convertchars table=clearRestrictedChars][grep search=""&replace=%26quot;][include file=test.csv][/grep][/convertchars][/text]

[text show=T]value2=[grep search="([^"]*)"&replace=|~\1~|][value1][/grep][/text]

[text show=T]value3=[listwords words=[url][value2][/url]&delimiters=|][!] [/!][hideif [url][word][/url]~[url]~[/url]][word][/hideif][!] [/!][showif [url][word][/url]~[url]~[/url]][convertchars table=val3replace][word][/convertchars][/showif][!] [/!][/listwords][/text]

[text show=T]value4=[listwords words=[url][value3][/url]&delimiters=%0D%0A][convertchars table=val4replace][word][/convertchars] [/listwords][/text] [writefile file=csvconverted.db][value4][/writefile] --000325559af2669be4048abb258a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi all,

I have been following some of the recent threads on CSV= import and using .mer format in WebDNA. =C2=A0It's something I'm g= oing to have to deal with a lot with a tool we are building so figured I ha= d better jump in and see what could be done.

Firstly I have no problem working with .mer directly in= WebDNA - the only amendment required was to change the suffix from .mer to= .txt (or .db). =C2=A0 In my case it is a 6000 record export from FileMaker= auto ftp'd up to the server each night.

Secondly on CSV files - using the explanation of the CS= V format on this page:

=C2=A0=C2=A0 =C2=A0http://www.creati= vyst.com/Doc/Articles/CSV/CSV01.htm

I have coded up the bit of code below which results in = a file that can be handled by WebDNA - albeit without valid fieldnames. =C2= =A0 That's something I'll work on next. =C2=A0But I just wanted to = get this onto the list to see if others could test the conversion on differ= ent and larger CSV files.

- Tom



[!]<!-- HAS_WEBDNA_TAGS -->
[/!]

<= /div>
[writefile file=3Dtest.csv]John,Doe,120 jefferson st.,Riverside, = NJ, 08075
Jack,McGinnis,220 hobo Av.,Phila, PA,09119
"John "= "Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075=
Stephen,Tyler,"7452 Terrace ""At the Plaza"&= quot;,
somewhere road",SomeTown,SD, 91234
,Blankman,,SomeTown,= SD, 00298
"Joan ""the bone"", Anne"= ;,Jet,"9th, at Terrace plc",Desert City,CO,00123
[/writ= efile]

[table name=3DclearRestrictedChars&fields=3Dfrom,to= ]
|
~
[/table]

[table name=3Dval3replace&fields= =3Dfrom,to]
%2C|
%0A
%0D%0= B
~
[/table]

[table name=3Dval4replace&a= mp;fields=3Dfrom,to]
%2C%0= 9
|%2C
[/table]


[text sho= w=3DT]value1=3D[convertchars table=3DclearRestrictedChars][grep search=3D&q= uot;"&replace=3D%26quot;][include file=3Dtest.csv][/grep][/convert= chars][/text]<br /><br />

[text show=3DT]value2=3D[grep search=3D"([^"]= *)"&replace=3D|~\1~|][value1][/grep][/text]<br /><br />= ;

[text show=3DT]value3=3D[listwords words=3D[url]= [value2][/url]&delimiters=3D|][!]

[/!][hideif [url][word][/url]~[url]~[/url]][word][/hide= if][!]
[/!][showif [url][word][/url]~[url]~[/url]][convertchars t= able=3Dval3replace][word][/convertchars][/showif][!]

[/!][/listwords][/text]<br /><br />

[text show=3DT]value4=3D[listwords words=3D[url][value3][/url]&delim= iters=3D%0D%0A][convertchars table=3Dval4replace][word][/convertchars]
[/listwords][/text]

[writefile file=3Dcsvconverted= .db][value4][/writefile]

--000325559af2669be4048abb258a-- Tom Duke

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:

Merchant and WebCat 3.0 (1998) OFF TOPIC: Sendmail problem (2003) Checking two required fields (1998) form variables (2000) [WebDNA] Error installing WebDNA on Ubuntu (2016) PCS Frames (1997) Quiestion (1997) carriage returns in data (1997) Online reference (1997) Trouble with formula.db (1997) en/decrypt problem (1999) Next X hits (1996) WebCat2b13 Mac plugin - [sendmail] and checkboxes (1997) Re:Emailer and encryption (1997) What is WebDNA (1997) Getting Emailer to send mail (1997) Emailer (WebCat2) (1997) Mac/network guy needed in Seattle (2004) Translator. (1998) WebCommerce: Folder organization ? (1997)