Transform Date To Mysql Timestamp Via CSV Import
I have a CSV file (from excel) with contains personal data, like names, adress, and birth date. The birth date is in this format yyyy-mm-dd. I would like to import this CSV file into my MYsql database with the "import CSV file" function that is build in PHPmyadmin.
The problem is that I need UNIX timestamps in my database, and not the date in yyy-mm-dd format. Code:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Transform '07,01,06' Into A Date Format With Just A Query
I've been handed just about the worst HTML/PHP/MySQL site ever scrapped together. While we're in the process of rebuilding the site from scratch for our client, they desperately need some data pulled from their DB. Originally the data was dumped to files but due to attempted injection attacks, the table has grown so large it times out. So I'm trying to pull the data directly but I have a huge problem. I need to pull all data with a date >= July 1, 2006. The problem is the dipsh*t who built this has the date field with a datatype of varchar and the date in the format of གྷ,01,06'. Obviously I can't do a date comparision check with that so how can I transform this data into a correct date format using only a query? I do NOT want to go in and start coding in this site. It's a live site and I'm afaid anything I do will break it. It's MySQL 3.23.57 and all I have access to is PHPMyAdmin. PLEASE help me!!! Someone work a little query magic for me.
Timestamp Import
I need to import into mysql data from DB2. One of the DB2 table columns is of the TIMESTAMP type, which, unlike its MySQL counterpart, supports fractions of a second, up to 6 digits, i.e. it is of the form 2005-07-13-23:45:32.000000 ....whereas the MySQL timestamp type is of the form 2005-07-13 23:45:32 Has anybody done this before? How can I keep the fractions of a second when I do the import
Transform Excel To Mysql
I have a table in excel....wich i want to transform to mysql table...there are some programs that just do that, but i`ve also had a google and read that just saving it as a .csv and then import it to mysql would do the trick... is that true? and my second question, after that....how do i upload the whole mysql table to my website
Where The Date (timestamp) Is Greater Than Now
On my site I want to output all records in my database with two conditions Where the date (timestamp) is greater than now but within the next 7 days? Can anyone help? I've got a feeling I need to use interval, but not sure?
DATE / TIMESTAMP Default Values
im is having problems with auto inserting date in my table. I dont know what is the default value i will set if column type to DATE. It was succesful though if is use the TIMESTAMP type in the column and is using CURRENT_TIMESTAMP as the dafault value. mysql automatically inserts the current date and time to the database everytime a new record is inserted. The problem with using TIMESTAMP is that i only want the date and not the additional time. Is there a default value I can use for the DATE function instead of using TIMESTAMP? I already tried NOW(), CURDATE(), CURRENT_DATE() to no avail
Extract, Transform
We have an old legacy database at my company called CIMPRO, running on a SCO UNIX box. We're trying to find a way to connect to the CIMPRO database, grab the data, and send it to a Mysql database. I've been able to successfully connect to the database using PHP and ODBC to view the data in the tables. i'd like to find a way to run a Linux tool or script to connect to the database, do an initial copy of the table structure from the legacy database to Mysql (on the linux box) and then copy selected data. If that is doable I'd also like to be able to refresh the table data periodically (e.g., cron job) either by deleting and reimporting or by checking to see what has changed.
Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form: year | season | hits 2004 | Summer | 42 2004 | Autumn | 43 2005 | Spring | 51 2005 | Summer | 52 2005 | Autumn | 53 2006 | Spring | 61 I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form year | Spring | Summer | Autumn 2004 | ------ | ----42 | ----43 2005 | ----51 | ----52 | ----53 2006 | ----61 | ------ | ------ Could anyone suggest a mysql query to generate the required output?
Format Date And Decimal On Import
I will import on a regular basis a text file from PayPal Europe into a MySQL Db. Dates are provided in this format: DD/MM/YYYY Decimals in this format: XXX,XX (comma before decimal instead of period) How can I write an insert query that will automatically format the date to YYYY-MM-DD and the decimals to XXX.XX? I tried this for the date:
Format Date And Decimals On Import
I will import on a regular basis a text file from PayPal Europe into a MySQL Db. Dates are provided in this format: DD/MM/YYYY Decimals in this format: XXX,XX (comma before decimal instead of period) How can I write an insert query that will automatically format the date to YYYY-MM-DD and the decimals to XXX.XX? I tried this for the date: INSERT INTO paypal_eu( date_transac ) VALUES ( STR_TO_DATE( '14/06/2007', '%Y-%m-%d' ) )); but it doesn't work. How about the decimals? I'm using MySQL 3.23.58
Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment which uses a Sub-select and create on that uses an OUTER JOIN ....
Passing Date Via Form For Mysql Date Type
I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great. I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction. I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example). This script works for me as long as I perform my query with my condition formated as such:
MySQL Timestamp And PHP
I'm using MySQL in-built timestamp of 14 characters e.g., 20060713174545. Using PHP date() function, is it possible to get correct date out of it. Does MySQL timestamp depends on Linux/ Windows machines? Or I've to use PHP to build dates and manually insert into database.
Timestamp And Current_date/timestamp
Im stuck with a sql query. Basically I have a db that stored a timestamp off everyone who has a successful login. I want to be able to count all the people who have logged into today? How do I do that? I thought the following would work but it didn't: SELECT count(date) FROM nn_users_logs WHERE date = CURREN_TIMESTAMP
Timestamp - Only When Created Mysql
How can i add a timestamp that is only updated when the record is inserted, not when the record is updated. A kind of "date created" stamp. Can this be done by setting the field up in phpmyadmin - ie not in the script im using (php)
MySQL Compare A Timestamp
I would like to compare a timestampfield with an offset of days, which is specified in the database for each dataset an the current date. For example: timestamp 20040403120000 offset 7 so i would like to find all datasets which are before 20040411120000.
Can't Insert A Unix Timestamp Into Mysql
Just upgraded to 4.1.8 and when I create a field with a date value (field is trdate), it always has default of 0000-00-00. And when I insert values with unix timestamp values (which I prefer, and they come from php's strtotime function),the record does not insert properly. I tried to set, through phpmyadmin, unix/current timestamp and to no avail. If I use a strtotime php function to convert a date into a timestamp, what is the proven way to make sure that value is saved during inserts? I am on the mysql manual, searched this board, but nothing works.
CR-XI Using MySQL: Group By Not Working For Timestamp
I am trying to get the max(timestamp) and min(timestamp) for every grouped data but it is only showing the max and min for the whole table. The following SQL works fine in PHPMyAdmin using MySQL, however I need to get the same results using CR-XI. MySQL: select file_id, max(timestamp), min(timestamp) from 360_status_log group by file_id order by file_id
MySQL Generate UNIX Timestamp
Cam MySQL create a UNIX timestamp during an INSERT? I want to create a timestamp that is the number of seconds since January 1st 1970. Are there any inbuilt functions for doing so?
INSERT Non-MySQL Date Into MySQL Date Field
I have a non-MySQL database that has a date value that is an integer value (4 byte unsigned integer representing the number of days since 1904, ie 01/01/2000 = 35063) Do I need to re-format this to use in an SQL to INSERT it into a MySQL table? If I do need to reformat it, what is the best format to use.
Import XML To MYSQL
What is the easiest way to import an XML Datafeed from a URL into a MYSQL Database?
Mysql Import
when should I use mysqlimport, or load data and when should I write my own Perl parser to help load the table? What criteria would be needed to decide to read a file into a hash?
MySQL Import CSV
I've been going nuts trying to import a CSV that has dates. I've tried everything I can think of. Below are the formats I've tried. "Data 1", "Data 2", 12/26/2005, ..... "Data 1", "Data 2", "2005/12/26", ..... "Data 1", "Data 2", "2005-12-26", ..... How do you do it
Import XML Into MySQL
Sorry. I missed this discussion previously. Now I need the answer. Figures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw
Mysql Import
I use this query in a PHP script to import a csv file $result = " LOAD DATA INFILE '$uploadlocation' INTO TABLE $table2 FIELDS TERMINATED BY '$seperator' ENCLOSED BY '"' LINES TERMINATED BY ' ' "; It's been working fine for a long time until now alll of a sudden this error popped up: Can't get stat of '/home/xxxx/public_html/data/xxxx.csv' (Errcode: 13) The user has File_priv set to yes. I even tried importing with the mysql root account but that didn't work either. Using LOAD DATA LOCAL INFILE gives : 1148: The used command is not allowed with this MySQL version, so that's not an option
Import Into MySQL
I need to be able to automate the import of a .sql or .csv file into my MySQL database. I am currently having a file dropped on my server from a distribution program externally every 15 minutes, so preferably I would like to import the file every 15 minutes also. Is this possible using a cron job? Or using Perl or PHP?
Error Trying To Import To Mysql.
I exported a small database to my computer Desktop. A .sql file. I asked my new web host to help me import it my new web mysql. I uploaded it and they attempted it directly to mysql(no phpmyadmin) and they replied "the importing of the backuped file to your database gives errors, and overwrites existing settings" and then provided the foloowing information below. Can someone help me tell me what this means and explain what went wrong and how this can be remedied? .......
Mysql Import Trouble
I'm using the following code to import a .sql file. mysqlimport --local -u root -pCENSORED teraspot_db /home/default/teraspot_db.sql and I get this error mysqlimport: Error: Table 'teraspot.teraspot_db' doesn't exist, when using table: teraspot_db I got the .sql file from exporting the database in phpmyadmin on the old server. What am I doing wrong?
Error When Trying To Import MySQL
Here's the case: Im trying to move a Joomla installation from one server to another. Everything went fine, until i tried importing the Sql file to the new server through phpMyAdmin. Get an error saying this: Quote: Error SQL query: -- phpMyAdmin SQL Dump -- version 2.9.1.1 -- http://www.phpmyadmin.net -- -- Host: 192.168.1.3:3306 -- Generation Time: Feb 17, 2007 at 04:09 AM -- Server version: 4.1.15 -- PHP Version: 5.2.0-8 -- -- Database: `pspart` -- CREATE DATABASE `pspart` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; MySQL said: #1064 - Der er en fejl i SQL syntaksen nær 'DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci' pÃ¥ linje 1 The top of the Sql file looks like this: -- phpMyAdmin SQL Dump -- version 2.9.1.1 -- http://www.phpmyadmin.net -- -- Host: 192.168.1.3:3306 -- Generation Time: Feb 17, 2007 at 04:09 AM -- Server version: 4.1.15 -- PHP Version: 5.2.0-8 -- -- Database: `xxx` -- CREATE DATABASE `pspart` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `xxx`; -- -------------------------------------------------------- -- -- Table structure for table `jos_banner` -- CREATE TABLE `jos_banner` ( `bid` int(11) NOT NULL auto_increment, `cid` int(11) NOT NULL default Ɔ', Can anyone tell me how to fix this? Please use simple words, as I'm not used to dealing with these things, and English is not my primary language.
Mysql Import Tools
I am looking to convert my static HTML site to mysql, are there any tools that would make this an easier process than manually entering all the data?
Import From Excel To MySQL
I'm a relative newbie to MySql, and I've got a small project I'm working on and hopefully you folks can either point me in the proper direction or give me a little help. I have multiple spreadsheets in Excel format and in .csv format too, that I would like to possibly import to a mysql database. All of the excel and csv files have is one huge colum and they are only 1 cell on each line. An estimate of 7k-8k domain names I need to run a whois on. Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name.
Import Of Database In Mysql
I get this error when I am importing the database. What am I doing wrong? ERROR 1064 at line 625735: You have an error in your SQL syntax near ' begin_excerpt varchar(250) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM' at line 5
What Database Do You Use To Import Into MySQL
I am setting up a MySQL database and find phpmyadmin very slow to enter data, so I am thinking of importing all the data from Microsoft Access 2003 instead. Or I could create a php page to enter the data. What do you guys use as your main database to import into MySQL?
Import From Excel To MySQL
I have multiple spreadsheets in Excel format and in .csv format too, that I would like to possibly import to a mysql database. All of the excel and csv files have is one huge colum and they are only 1 cell on each line. An estimate of 7k-8k domain names I need to run a whois on. Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name.
Import From Excel To MySQL
I have multiple spreadsheets in Excel format and in .csv format too, that I would like to possibly import to a mysql database. All of the excel and csv files have is one huge colum and they are only 1 cell on each line. An estimate of 7k-8k domain names I need to run a whois on. Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name.
Import Txt-file To Mysql DB
On the one hand i have a mysql-table called "news". It has 4 fields, "uid" (with auto_increment attribute), "headline", "text", "author". On the other hand i have a txt-file in the following format. headline|text|author This file contains of several lines, all in the same format. Is it possible to import this file to my database? I need the "headline" from the txt-file in my "headline" field and so on.. The uid-field has the attribute auto-increment, so i think i must not care about it, right? I tried some things but they didn't work.
Paradox Import Into MySQL
I am trying to import some paradox .db files into my MySQL database, and I would like to know how to do this from the MySQL command line so I can create a batch file. Does anyone know the syntax on how to do this?
Import Users From Old Mysql Db?
I'm trying to import all of the info in the mysql database (ya know the db that keeps track of all the users and what they can and cannot do). I tried just a standard mysqldump from the old box. Copy the file over to the new box. And do a mysql -uroot -p < /tmp/mysqldb.sql. Lo and behold it doesn't work. Looks like there are more columns in some of the tables.
MySQL Export/import
I have installed MySQL on my Linux Mandrake 10 workstation; I didn't change any default values (except for the user-accounts). I used a CMS to create a website which I want to upload to my hosting provider. When I export my database (using PHPMyAdmin) from my workstation to a local file and then on the server (again using PHPMyAdmin) I import the database from the local file, some characters are 'fucked up'. My local install is MySQL 4.1.2-alpha-standard (from Mandrake RPM) and it seems that the default character set is utf8 with collation utf8_general_ci. But some columns in my tables (even within other databases) use collation latin1_swedish_ci (strange..). The server install is MySQL 4.0.15. Using mysqladmin with the 'variables' command, I found out that variable 'character_set' has value 'latin1' and the variable 'character_sets' has value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 The 'convert_character_set' variable seems to have no value. I tried creating a database on the server using mysqladmin with the --default-character-set=utf8, but that also doesn't help. (it doesn't give any error message, but utf8 is not listed in the 'character_sets' variable).
Import XML Dada Into MySQL
I want to import XML data into a MySQL database, preferable using a PHP backend. Has anyone experience of doing this and can give me a few pointers.
Import List To MySql
I have been manually inputting an alpabetical list into MySql via a PHP submit form. I have a lot of data to enter and doing it one by one is killing me! Is there a way to import the list without having to do it individually? Here's an example. Under my Sub category INSERT INTO `my_categories` VALUES (7715, 'Bala', 7711, NULL, 0.00); INSERT INTO `my_categories` VALUES (7716, 'Balmy', 7711, NULL, 0.00); etc, etc.
Import Data Into Mysql
is there a way around it, other than scrubbing the data ? currently, the dates are in mm/dd/yyyy format if i want to use in import file with fields that have this format, is there any way of applying a conversion to that field on the fly ?
MySQL Import On WAMP
I installed WAMP on XP to use a test server and can't figure out this silly windoze command line thing for MySQL. It seems my linux commands are Klingon to Windows. I try this.. mysql> username -p database < c:wampwwwfile.sql and I get a bunch of syntax errors. I tried dual slash and everything and it's not working. The file is 80MB so it has to be done at the command prompt. Is there anyone out there? Is there anyone listening? I've searched low and hi and there doesn't seem to be a page with how to do this. I've seen LOAD but that's just for a table.
Import Dump From Unix Mysql To Windows Mysql
I am trying to load a dump from a MySQL database on my ISP's server to my development environment on a Win 2K machine (running MySQL). When I run the mysql command to run the load script, MySQL gives the following complaint: "ERROR 1064 at line 21: You have an error in your SQL syntax. Check the manual t hat corresponds to your MySQL server version for the right syntax to use near 'D EFAULT CHARSET=latin1' at line 7" The dump from the ISP's server is created using mysqldump. The ISP is running MySQL 4.1.20. My development environment is MySQL 4.0.26. I have no problem loading dumps made from my development environment usng phpmyadmin.
Can't Import Dump Files In Mysql 5
I apologize upfront if this is common knowledge. I've searched for quite a while and found nothing concrete. Using phpmyadmin 2.6.4-pl4 MySQL 4.0.23-nt php 4.4.4 When I export and make a sql file, I cannot import the data using this setup: phpmyadmin 2.6.4-pl4 MySQL 5.0.24a-community-nt php 4.4.4 Get "No SQL query!" message through phpmyadmin when I import the .sql file. If I cut and paste out the create table or insert text it works, but that takes forever. If I copy the data files to the data folder everything works in mysql 5. The sql files I've tried have been from 2-4 mbs. My backup dumps are 80 mbs though and obviously worried about restoring backups. Is there something I missed when upgrading? I pretty much copied off my data, uninstalled mysql 4 and installed mysql 5. Not in strict mode. Dataserver is in another location so can't always go copy the files off manually. Thanks for any help.
|
TRACKER
