Re: mysqlimport csv file import problem
On Mon, 2006-08-28 at 13:47 +0200, Thomas Spahni wrote:
On Sun, 27 Aug 2006, Jim Seymour wrote:
Hi,
I download a csv file from Yahoo in this format:
ABIAX
20.63
2006-08-3
ACEIX
8.78
2006-08-3
CIGAX
10.08
2006-08-3
FSCTX
22.25
2006-08-3
GGOAX
20.55
2006-08-3
HWLAX
23.3
2006-08-3
HWMAX
28.74
2006-08-3
MLEIX
96.37
2006-08-3
NBPBX
18.98
2006-08-3
PSVIX
32.43
2006-08-3
PTRAX
10.3
2006-08-3
RGACX
30.89
2006-08-3
^DJI
11242.6
2006-08-3
^IXIC
2092.34
2006-08-3
^GSPC
1280.27
2006-08-3
My table for this data is in this format
| 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 |
96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 |
Is there a way to get mysqlimport to pull the data from specific
column/row to insert into a specified field? Trying to find an easier
way than typing all of the data into a text file for import.
Thanks,
Hi Jim,
that needs some preprocessing, but 'sed' is your friend. You could use
some shell script doing the work for you:
#!/bin/sh
BLANK=' '
TAB=' '
WHITESPACE=${BLANK}${TAB}
DATE=$(cat mydatafile.csv | sed \
-e 3 !d \
-e s/^[$WHITESPACE]*// \
-e s/.*/''/)
DATA=$(cat mydatafile.csv | sed \
-e /[A-Z]/ d \
-e /-/ d \
-e s/^[$WHITESPACE]*// \
-e s/.*/''/ | tr '\012' ',' | sed \
-e s/,*$//)
echo INSERT INTO mytable VALUES(${DATE},$DATA);
exit 0
# end of shell script
The resulting queries can be piped into the mysql client.
Thanks Thomas,
From what I had read it looked like I would have to do something like
this. This will save me a lot of time.
Thanks Again,
--
Jim Seymour [EMAIL PROTECTED]
signature.asc
Description: This is a digitally signed message part
mysqlimport csv file import problem
Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, -- Jim Seymour [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
ALTER TABLE - how to fix truncated data?
My bad. I was renaming some columns in a table. I incorrectly set the type to decimal(4,2) and the data was truncated/hosed. Is there a way to recover the data. I tried a system backup from yesterday. That changed nothing. I have already set the column type back to the correct settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box. TIA, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Financial return calculations help please
On Wed, Oct 05, 2005 at 11:23:00AM -0700, Mike Wexler wrote: Jim Seymour wrote: I have researched repeatedly and cannot find an answer to the following. I need to do something like the following (There is probably an easier way). end_date - start_date = diff / start_date = return for period The table contains 401k investment values. Ideas, pointers, etc.? I am using mysql v5.0.12 on Debian Linux. TIA, Jim Depending on how fancy you want to get, you would calculate either an IRR (Internal Rate of Return) that basically says, what interest rate would I need to get on the funds to end up with the same results I actually achived. Note that calculating an interest rate is going to require an iterative approximation. Thanks Mike, What I was really looking for is how to structure a query in mysql to arrive at the return on the investment. I know I completely left that out of my first post. Is it even possible? Thanks, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Financial return calculations help please
I have researched repeatedly and cannot find an answer to the following. I need to do something like the following (There is probably an easier way). end_date - start_date = diff / start_date = return for period The table contains 401k investment values. Ideas, pointers, etc.? I am using mysql v5.0.12 on Debian Linux. TIA, Jim -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures MS SQL Server to MySQL
On Sun, Sep 25, 2005 at 03:56:46PM -0500, Peter Brawley wrote: Jim, If you really want to return the result in a variable, declare a user var in the client, declare an OUT var in the SP, have the SP SELECT INTO it, and pass the user var to the SP in the call: SET @x=0; SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers ( OUT count INT ) BEGIN SELECT COUNT(*) INTO count FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(@x); SELECT @x; Thanks Peter, Since she was teaching us about using a variable I am trying your second example. Thanks again, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedures MS SQL Server to MySQL
Taking on online class for SQL and am down to the last two classes and cannot make the following work. This is a MS SQL Server query that I have not been able to solve through the MySQL Documentation: CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL Print @count I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. TIA, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
