[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:

validatin on NT (sorry for the dups and etc) (1998) Emailer & WebStar Mail and 551 Error (2000) NT installation (2000) Add a Blog to your site. (2002) Reverse dating (1999) showif and cart (1997) Converting spaces to + in results list (2000) POSTing to a secure server ... (2000) Spawn doesn't work as advertised ... (2000) Generating Report Totals (1997) Page Breaks (1999) Problem (1997) Summary layout (1997) Banner DNA (1997) Comments in db? (1997) Options for http uploading of files (1999) WebCat2 - [format thousands] (1997) WebMerchant/MacAuthorize (1998) Shopping carts and reloading pages (1997) [protect] on NT? (1997)