+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
Scripts Thread, Search and Replace VBs problem in Coding and Web Development; Me again! How can i edit that so that it removes all " aswell as i need the file just ...
  1. #16

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    Me again!

    How can i edit that so that it removes all " aswell as i need the file just to import hello rather then "hello" but also i have other fields which are "\\server\share\folder" but also need to be \\server\share\folder. and Also "username@domain" to username@domain

    Cheers

  2. #17

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    You need to edit your SQL import script to tell it the fields are enclosed by speech marks, just change this line (this is the edited version of the line in the script you posted earlier):

    Code:
    mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY '"' LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());
    Last edited by LosOjos; 2nd February 2011 at 11:20 AM.

  3. #18

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    I tried this but i get an syntax error - unexpected T_CONSTANT_ENCAPSED_STRING

  4. #19

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Try escaping the ", like this:

    Code:
    mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());

  5. Thanks to LosOjos from:

    glennda (2nd February 2011)

  6. #20

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    Yes that works! Cheers

  7. #21


    Join Date
    Feb 2007
    Location
    51.405546, -0.510212
    Posts
    8,706
    Thank Post
    220
    Thanked 2,615 Times in 1,926 Posts
    Rep Power
    777
    Quote Originally Posted by glennda View Post
    How can I edit that so that it removes all " aswell as i need the file just to import hello rather then "hello"
    This will remove the double quotes around everything if you still need to do it.

    Code:
    $file = ".\exportusershomedir_new.csv"
    (gc $file) -replace('"','') | Out-File $file -Force
    You can either add the two lines above onto the end of the previous PowerShell script or create a new one specifically to remove the quotes.

  8. Thanks to Arthur from:

    glennda (2nd February 2011)

  9. #22

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    Cheers I'll bear that in mind as i have another use for the output rather then importing into sql.

    Cheers

  10. #23

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    @LosOjos

    Is there a way to change the import so that it either updates existing or imports new data as currently when the script is run is just adds all 1600 records on top of the records already in the database. So after a week i will have 5 duplicates of each item!

  11. #24

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by glennda View Post
    @LosOjos

    Is there a way to change the import so that it either updates existing or imports new data as currently when the script is run is just adds all 1600 records on top of the records already in the database. So after a week i will have 5 duplicates of each item!
    Yeah that's do-able just edit the same line as earlier to add the 'REPLACE' keyword to the import, like so:

    Code:
    mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" REPLACE INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());
    EDIT: a little more info

    The 'REPLACE' keyword tells the database that if it finds a record with a duplicate ID, it should replace it with the new version when importing. If you use the 'IGNORE' keyword instead, the database will skip entries that have the same ID (i.e. retain the old values)
    Last edited by LosOjos; 2nd February 2011 at 03:35 PM.

  12. #25

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Did you have any luck with that? I imagine not, as after I posted that I had a thought: there is no ID in your CSV file is there? In that case, the above method wouldn't work, and you'd have to look at deleting duplicates from the table - there are many different way to do this, the method you choose depends greatly on the size of the table - roughly how many records are you expecting it to hold?

  13. #26

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,784
    Thank Post
    272
    Thanked 1,129 Times in 1,025 Posts
    Rep Power
    348
    To be honest I was just thinking of adding to the script to Dump the contents of the table - then import the new one! So that it refreshes the content every day!

  14. #27

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    If the CSV will always contain all the data that you need (i.e. old records as well as new) then that will be the best way to do it for sure.

    If you need any more help just shout

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. [MS Office - 2003] Strange MSAccess printing problem with .vbs
    By RabbieBurns in forum Office Software
    Replies: 1
    Last Post: 31st January 2009, 07:36 PM
  2. Bat calling VBS problem
    By MK-2 in forum Scripts
    Replies: 5
    Last Post: 15th December 2008, 11:08 PM
  3. Search replace across multiple XML files
    By ajbritton in forum Windows
    Replies: 6
    Last Post: 6th April 2008, 11:07 PM
  4. SIMS Surname search problem
    By FN-GM in forum MIS Systems
    Replies: 5
    Last Post: 21st November 2007, 03:26 PM
  5. Mass search and replace
    By Fletcher_Bravo in forum Windows
    Replies: 2
    Last Post: 5th July 2006, 03:44 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •