www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Import multiple CSV files

  1. #1
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    82

    resolved [RESOLVED] Import multiple CSV files

    All,

    With the help of many on this site, we have been able to pull data and save it as a CSV file. We have 100+ computers that will be running this script locally and the uploading their respective CSV files to my server. The script will run every hour at :00. Every hour at :30, I will have a PHP script run that will input all the data from the CSV files into a MySQL database. This is where I need help.

    I have attached the script I am using to import the information from the CSV into the MySQL databasehttp://images.intellitxt.com/ast/adTypes/icon1.png. My goals:


    • Get the file name for entry into the database (I can pass this in the URL if needed)
    • Remove the erroneous three rows at the top of each CSV file (two blank rows and one header row)
    • Have the script cycle through and execute for all CSV's in the directory
    • Delete the CSV's after being imported



    If it makes life easier, I can have the VBS remove the three lines. If I do so, will I need to remove the header row, or is that needed for input into the database?

    My thought is that the individual laptops will run the script to get the data from stalist.cgi and upload the CSV every hour. Then, half-past every hour, this script will run that imports all CSV files into the database. This gives the computershttp://images.intellitxt.com/ast/adTypes/icon1.png time to process and upload and ensure I have the most accurate data possible.

    Here is the script I have started with. As you can tell, I am specifying one particular CSV file to be read. I haven't figured out a way to have the script execute for all files. TIMTACULAR is the name of my computer, so you can see that the computer name is used the filename. It makes no difference to be whether the file name, or computer name (via URL) is used.

    PHP Code:
    $link_id mysql_connect("localhost""**""***") or die("Could not connect."); if(!mysql_select_db("speedtest",$link_id)) die("database was not selected.");$file_handle fopen("resultupload/TIMTACULAR.csv""r");while (($line_of_data fgetcsv($file_handle1000",")) !== FALSE) { $line_import_query="INSERT into bridges(MAC,DeviceName,SignalNoiseDBM,Distance,TxRxMbps,CCQ,ConnectionTime,LastIP) values('$line_of_data[0]','$line_of_data[1]','$line_of_data[2]','$line_of_data[3]','$line_of_data[4]','$line_of_data[5]','$line_of_data[6]','$line_of_data[7]')"mysql_query($line_import_query) or die(mysql_error()); } 
    http://timweitzel.net/wasp/TIMTACULAR.csv - Link to the actual CSV file being saved.
    http://timweitzel.net/wasp/rxbridge.PNG - List of Columns in the database

  2. #2
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    82
    Not sure why there are erroneous links to a PNG file. Sorry folks.

  3. #3
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    82
    I found my solution. Instead of trying to accomplish all items in one script, I broke it down to a file-by-file method. I used the isset method to get the computer name as a variable. The computer name is also the name of the CSV file. The script pulls the isset value, opens the CSV, puts the data into MySQL, closes and deletes the file. Script below. Thank you to all who helped me along this path.

    PHP Code:
    if(isset($_GET['compname'])) {      $compname $_GET['compname'];  }  else {  echo Error;    }
    $link_id mysql_connect("localhost""***""***") or die("Could not connect."); if(!mysql_select_db("speedtest",$link_id)) die("database was not selected.");

    $file_handle fopen("resultupload/$compname.csv""r");

    while ((
    $line_of_data fgetcsv($file_handle1000",")) !== FALSE) { $line_import_query="INSERT into bridges(MAC,DeviceName,SignalNoiseDBM,Distance,TxRxMbps,CCQ,ConnectionTime,LastIP,ComputerName) values('$line_of_data[0]','$line_of_data[1]','$line_of_data[2]','$line_of_data[3]','$line_of_data[4]','$line_of_data[5]','$line_of_data[6]','$line_of_data[7]','$compname')"mysql_query($line_import_query) or die(mysql_error()); }
    $file = ("resultupload/$compname.csv");fclose($file_handle);unlink($file); 

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
  •  
HTML5 Development Center



Recent Articles