64902

How can I load many files into mysql automatically add date information?

Question:

I have many files, for example: 20170319, 20170320 ... For each file I have 2 columns, one for username and the other was data. I've created a table

create table A(user varchar(35), date date, data varchar(35), primary key(user, date));

Then, I want to load those files into database, and use filename as specific date in date field.

Can I still use sth like:

Load data infile '20170320' into table A

Answer1:

The answer is that you cannot do this in MySQL alone, you need to use an external program or script that builds the load data infile statements with the appropriate <a href="https://dev.mysql.com/doc/refman/5.7/en/load-data.html" rel="nofollow">SET clause</a> derived from the name of the file:

<blockquote>

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP; </blockquote>

The reason for this is that:

<ol><li>load data infile cannot use the file name as an input variable</li> <li>neither MySQL prepared statements using the <a href="https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html" rel="nofollow">prepare statement</a>, nor <a href="https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html" rel="nofollow">stored procedures</a> are allowed to use the load data infile statement.</li> </ol>

Recommend

  • How to insert text after a particular line of a file
  • Parsing Strings in SuperCSV
  • Difference between optional values in swift?
  • App isn't responding because of Service START_CONTINUATION_MASK
  • (Python 3) How to pass binary file as text without saving first
  • Eclipse new plugin Project: Not choosable
  • rsa encryption decryption using c
  • Is it ok to submit a build to the app store for testing when a previous build is waiting for review?
  • ObjectMaterialize in EF not firing on first level query
  • Multiple versions of iTunesArtwork in one project?
  • How do I shift the decimal place in Python?
  • how do i write assembly code from c#?
  • Function calls are not supported. Consider replacing the function or lambda with a reference to an e
  • Memory error in python- how to use more memory
  • C# program and C++ DLL compiled for 32-bit system crash on 64-bit system
  • Differences in dis-assembled C code of GCC and Borland?
  • how to upload multiple files in c# windows application
  • Loading .coffee files via a view in Rails
  • Create DicomImage from scratch using Dcmtk
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Handling un-mapped Rest path
  • Algorithm for a smudge tool?
  • DomPDF {PAGE_NUM} not on first page
  • Retrieving value from sql ExecuteScalar()
  • Javascript simulate pressing enter in input box
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Importing jscolor library in angular 2
  • Matrix multiplication with MKL
  • A cron job substitute?
  • KeystoneJS: Relationships in Admin UI not updating
  • CSS Applying specific rule for a specific monitor resolution with only CSS is posible?
  • Hits per day in Google Big Query
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Checking variable from a different class in C#
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • reshape alternating columns in less time and using less memory
  • Converting MP3 duration time
  • How to load view controller without button in storyboard?