Knowledge Base |
|
|
| TID | 622 ( s622 ) |
| QuickLink | http://www.stone-ware.com/swql.jsp?kb=s622 |
| Category | WebPages |
| Summary | Import data into webPages database |
| Details | Customer used a csv file to import data into webpages and had a cr and lf in the data. This data then caused webpages to not display the top menubar. It gives a javascript error when the page loads. |
| Resolution | For a database like mysql, when you import the data, you may need to either make
sure your text file has the proper termination or run the import file through a converter to strip off the extra <cr> You can also specify a paramter like "LINES TERMINATED BY '\r\n' Refer to http://dev.mysql.com/doc/refman/5.0/en/load-data.html for more information. When you do import data into the webpages database, use the following examples. If the database field is looking for a DN, then you either need to provide it in the import, or go back and craft a sql update to fix them prior to webpages working. =========================================== The teacher data file should look like : (ownerdn, title, firstname, lastname, school, email, office, home, cell, fax, photolocation, message, homepage) "CN=JBumgardner,OU=STAFF,DC=anytown,DC=COM",Ms.,,Bumgardner,Anytown High School,JBumgardner@myschool.edu,(555) 123-0066,,,,,,standard =========================================== =========================================== The students data file should look like : (studentdn, firstname, lastname) AmyW10,Amy,Wallace =========================================== =========================================== Enrollment data file should look like : (studentdn, teacherdn, class) AmyW10,DCassat,Basic Alg 2 AmyW10,MSlowik,French AmyW10,KKuhn,Theo II AmyW10,DCassat,World Lit =========================================== =========================================== Classes data file should look like : (ownerdn, classname, homepage) JBumgardner,Intro Speech,standard =========================================== Sample scripts to import into mysql. rem *** This script will delete everything in the classes table in the webPagesDB database rem *** It will then load classes data from the classes.csv file in c:\stoneware\sqlstuff rem *** If you want to remove the password from this file then you will have to leave -p with a space after it rem *** YOu will then be prompted for a password on each step c: cd c:\stoneware\sqlstuff rem ***This is the delete step - Everything in the classes table is lost mysql -u root -pmysqlpass -D webPagesDB -e "DELETE from classes;" rem *** This step loads the file classes.csv - the file should not have a header row rem *** The first field is ownerdn (the file should be populated with teacher cn (common name) which is the rem *** name that shows up in the OU or container in AD rem *** The second field is classname rem *** The third field is homepage which should read - standard - for all classes mysql -u root -pmysqlpass -D webPagesDB -e "LOAD DATA LOCAL INFILE 'classes.csv' into table classes fields terminated by ',' enclosed by '\"' lines terminated by '\n' (ownerdn, classname, homepage);" rem *** This step manipulates the data and turns the ownerdn field in the previous step from CN to DN rem *** DN is the distinguished name or full LDAP name rem *** this step also converts that info to upper case mysql -u root -pmysqlpass -D webPagesDB -e "UPDATE classes set ownerdn = upper(concat(\"CN=\",ownerdn,\",OU=Staff,DC=anytown,DC=com\"));" rem *** This step shows all rows from the classes table to verify that the load worked mysql -u root -pmysqlpass -D webPagesDB -e "select * from classes;" |