Solved

Writing to MySql DB from ascii file

Posted on 2007-11-14
2
480 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now