Solved

Writing to MySql DB from ascii file

Posted on 2007-11-14
2
488 Views
Last Modified: 2008-02-01
I need the sql command for mysql (database info at end of question) for reading the contents of an ascii file and inserting them into a table.  Below is the necessary information to write the query.

File name: c:\test\AD.asc
File contents:

06/29/2007,08:20:00,0.8484
06/29/2007,09:20:00,0.8488
06/29/2007,10:20:00,0.8484
06/29/2007,11:20:00,0.8473
06/29/2007,12:20:00,0.8477
06/29/2007,13:20:00,0.8470
06/29/2007,14:20:00,0.8469

I could delimit the file ascii file to read using spaces or tabs if necessary.

The table to which I would like to add the contents of the file is called:  'datasetsSpecific'.  Below I have outlined the table column names followed by their format and what it should be filled in with.  In advance, thank you very much.

collectedDataID (INT) - FILLED IN WITH A ONE FOR EVERY ENTRY
period (DATETIME) - FILLED IN WITH THE FIRST TWO COLUMNS OF THE FILE CONCATENATED
open (decimal) - FILLED IN WITH COLUMN #3
flatTick (INT) - NOT FILLED IN


Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables
0
Comment
Question by:jmokrauer
2 Comments
 
LVL 17

Accepted Solution

by:
leannonn earned 500 total points
ID: 20288252
The SQL command you need is `LOAD DATA INFILE`. The syntax is here: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
0
 
LVL 6

Expert Comment

by:baev
ID: 20288986
It seems to me that this question is already answered here:
http://www.experts-exchange.com/Database/MySQL/Q_22961535.html
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question