Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/15 00:30 +0100, Mogens Melander I guess I'm sill learning. Does that mean that, if the last column in a load blabla. is a -00-00 terminated by ^n it might error ? Or are we talking ODBC ? Find it under LOAD DATA If an empty field is parsed for a NOT NULL DATE or DATETIME, instead of reporting error as in strict mode, the parser makes the date -00-00--Maybe in ODBC, too, which I do not know, but certainly from character input. (I regularly use CSV files with MySQL, although the match is not perfect.) This is a particular case of "zero" (see LOAD DATA) for any NOT NULL type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
6:40 AM: Sorry, didn't mean to be rude. It's in there. On Thu, November 15, 2012 00:23, Mogens Melander wrote: > Dude, which part of RTFM did yoy miss? > > http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html > > On Wed, November 14, 2012 13:57, sagar bs wrote: >> Hi, >> >> >> There are four columns in my table named like account_name, c1, c2 and >> c3. >> Account name is the primary key and c1, c2 contain two different dates >> and >> in the column c2 there are few fields showing /00/00, now i need >> to >> get the date different(in days) >> between the dates present in the c1 and c2. That days should be shown in >> the c3. please help me out. >> >> On Wed, Nov 14, 2012 at 3:46 PM, wrote: >> >>> 2012/11/14 10:26 +0530, sagar bs >>> As i have the data with some 25 variables in csv file and i need to >>> import >>> to mysql. >>> The issue is that the date format in csv file is dd/mm/ and mysql >>> takes >>> the date format like /mm/dd. >>> The number of variables in the csv file are same in the table in >>> database >>> of mysql. >>> Please help me out. >>> >>> Use LOAD DATA s feature of in the same SQL statement importing into a >>> user >>> variable and using it with SET, using the function STR_TO_DATE: >>> >>> load data ... >>> (..., @dait, ...) ... >>> SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') >>> >>> . It is not important how many decimal digits match each pattern, but >>> it >>> cannot match variation in the separators. >>> >>> It is required that NULLs in the file take the form '\N' or 'NULL', >>> depending on escape-option. >>> >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe:http://lists.mysql.com/mysql >>> >>> >> >> >> -- >> Regards >> >> SAGAR B S >> >> -- >> This message has been scanned for viruses and >> dangerous content by MailScanner, and is >> believed to be clean. >> >> > > > -- > Mogens Melander > +66 8701 33224 > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
I guess I'm sill learning. Does that mean that, if the last column in a load blabla. is a -00-00 terminated by ^n it might error ? Or are we talking ODBC ? On Wed, November 14, 2012 18:58, h...@tbbs.net wrote: > 2012/11/14 18:27 +0530, sagar bs > There are four columns in my table named like account_name, c1, c2 and > c3. Account name is the primary key and c1, c2 contain two different dates > and in the column c2 there are few fields showing /00/00, now i need > to get the date different(in days) > between the dates present in the c1 and c2. That days should be shown in > the c3. please help me out. > > Try DATEDIFF. > > As for date /00/00, MySQL s treatment of NULLs in CSV files is > peculiar: it wants the escape NULL or \N, separator right after separator > is not NULL, but empty string. Consider those NULL. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Dude, which part of RTFM did yoy miss? http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html On Wed, November 14, 2012 13:57, sagar bs wrote: > Hi, > > > There are four columns in my table named like account_name, c1, c2 and > c3. > Account name is the primary key and c1, c2 contain two different dates and > in the column c2 there are few fields showing /00/00, now i need to > get the date different(in days) > between the dates present in the c1 and c2. That days should be shown in > the c3. please help me out. > > On Wed, Nov 14, 2012 at 3:46 PM, wrote: > >> 2012/11/14 10:26 +0530, sagar bs >> As i have the data with some 25 variables in csv file and i need to >> import >> to mysql. >> The issue is that the date format in csv file is dd/mm/ and mysql >> takes >> the date format like /mm/dd. >> The number of variables in the csv file are same in the table in >> database >> of mysql. >> Please help me out. >> >> Use LOAD DATA s feature of in the same SQL statement importing into a >> user >> variable and using it with SET, using the function STR_TO_DATE: >> >> load data ... >> (..., @dait, ...) ... >> SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') >> >> . It is not important how many decimal digits match each pattern, but it >> cannot match variation in the separators. >> >> It is required that NULLs in the file take the form '\N' or 'NULL', >> depending on escape-option. >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> >> > > > -- > Regards > > SAGAR B S > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/14 18:27 +0530, sagar bs There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. Try DATEDIFF. As for date /00/00, MySQL s treatment of NULLs in CSV files is peculiar: it wants the escape NULL or \N, separator right after separator is not NULL, but empty string. Consider those NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Hi, There are four columns in my table named like account_name, c1, c2 and c3. Account name is the primary key and c1, c2 contain two different dates and in the column c2 there are few fields showing /00/00, now i need to get the date different(in days) between the dates present in the c1 and c2. That days should be shown in the c3. please help me out. On Wed, Nov 14, 2012 at 3:46 PM, wrote: > 2012/11/14 10:26 +0530, sagar bs > As i have the data with some 25 variables in csv file and i need to import > to mysql. > The issue is that the date format in csv file is dd/mm/ and mysql takes > the date format like /mm/dd. > The number of variables in the csv file are same in the table in database > of mysql. > Please help me out. > > Use LOAD DATA s feature of in the same SQL statement importing into a user > variable and using it with SET, using the function STR_TO_DATE: > > load data ... > (..., @dait, ...) ... > SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') > > . It is not important how many decimal digits match each pattern, but it > cannot match variation in the separators. > > It is required that NULLs in the file take the form '\N' or 'NULL', > depending on escape-option. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Regards SAGAR B S
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/14 10:26 +0530, sagar bs As i have the data with some 25 variables in csv file and i need to import to mysql. The issue is that the date format in csv file is dd/mm/ and mysql takes the date format like /mm/dd. The number of variables in the csv file are same in the table in database of mysql. Please help me out. Use LOAD DATA s feature of in the same SQL statement importing into a user variable and using it with SET, using the function STR_TO_DATE: load data ... (..., @dait, ...) ... SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y') . It is not important how many decimal digits match each pattern, but it cannot match variation in the separators. It is required that NULLs in the file take the form '\N' or 'NULL', depending on escape-option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Did you change the target column to varchar before import ?
On Wed, November 14, 2012 10:23, sagar bs wrote:
> tried to import data as text, but its showing Operation failed with
> exitcode 1
>
> On Wed, Nov 14, 2012 at 1:12 PM, Mogens Melander
> wrote:
>
>> Or you could import the date as text and convert dates using:
>>
>> mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
>> -> '2004-04-31'
>>
>>
>> On Wed, November 14, 2012 06:13, Larry Martell wrote:
>> > On Tue, Nov 13, 2012 at 9:56 PM, sagar bs wrote:
>> >> Hi,
>> >>
>> >> As i have the data with some 25 variables in csv file and i need to
>> >> import
>> >> to mysql.
>> >> The issue is that the date format in csv file is dd/mm/ and mysql
>> >> takes
>> >> the date format like /mm/dd.
>> >> The number of variables in the csv file are same in the table in
>> >> database
>> >> of mysql.
>> >> Please help me out.
>> >
>> > Convert the data in your CSV file into the format you need. So many
>> > ways to do that, e.g.: bring in into excel and change the column's
>> > format, use sed, use python, use vi,
>>
>>
>> --
>> Mogens Melander
>> +66 8701 33224
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>>
>
>
> --
> Regards
>
> SAGAR B S
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
tried to import data as text, but its showing Operation failed with
exitcode 1
On Wed, Nov 14, 2012 at 1:12 PM, Mogens Melander wrote:
> Or you could import the date as text and convert dates using:
>
> mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
> -> '2004-04-31'
>
>
> On Wed, November 14, 2012 06:13, Larry Martell wrote:
> > On Tue, Nov 13, 2012 at 9:56 PM, sagar bs wrote:
> >> Hi,
> >>
> >> As i have the data with some 25 variables in csv file and i need to
> >> import
> >> to mysql.
> >> The issue is that the date format in csv file is dd/mm/ and mysql
> >> takes
> >> the date format like /mm/dd.
> >> The number of variables in the csv file are same in the table in
> >> database
> >> of mysql.
> >> Please help me out.
> >
> > Convert the data in your CSV file into the format you need. So many
> > ways to do that, e.g.: bring in into excel and change the column's
> > format, use sed, use python, use vi,
>
>
> --
> Mogens Melander
> +66 8701 33224
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>
--
Regards
SAGAR B S
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
Or you could import the date as text and convert dates using:
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'
On Wed, November 14, 2012 06:13, Larry Martell wrote:
> On Tue, Nov 13, 2012 at 9:56 PM, sagar bs wrote:
>> Hi,
>>
>> As i have the data with some 25 variables in csv file and i need to
>> import
>> to mysql.
>> The issue is that the date format in csv file is dd/mm/ and mysql
>> takes
>> the date format like /mm/dd.
>> The number of variables in the csv file are same in the table in
>> database
>> of mysql.
>> Please help me out.
>
> Convert the data in your CSV file into the format you need. So many
> ways to do that, e.g.: bring in into excel and change the column's
> format, use sed, use python, use vi,
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
On Tue, Nov 13, 2012 at 9:56 PM, sagar bs wrote: > Hi, > > As i have the data with some 25 variables in csv file and i need to import > to mysql. > The issue is that the date format in csv file is dd/mm/ and mysql takes > the date format like /mm/dd. > The number of variables in the csv file are same in the table in database > of mysql. > Please help me out. Convert the data in your CSV file into the format you need. So many ways to do that, e.g.: bring in into excel and change the column's format, use sed, use python, use vi, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
