Databases
StackOverflow2013
Postsdbo
POstore data from txt files in table mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POstore data from txt files in table mysql
    primarykey
    data
    text
    <p>I am trying to make a program where I would read data from txt files and store them in tables. The user would give the directory of the files and then would create the table with specific fields.</p> <p>Then I have to fill the each table with the data that it has. Does anyone could help me how I would do this? The .txt files have data as these that are shown in the image:<img src="https://i.stack.imgur.com/qTksU.png" alt="enter image description here"><br> The two columns are seperated by tab. I had tried the code below but I get error: </p> <pre><code>java.sql.SQLException:at part1.importData(part1.java:31) at part1.main(part1.java:16) </code></pre> <p>The code that I have tried is:</p> <pre><code>public class notepad { public static void main(String args[]) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection( "jdbc:mysql://localhost:3300/mydb", "root", "root"); String dirpath = ""; Scanner scanner1 = new Scanner(System.in); while (true) { System.out.println("Please give the directory:"); dirpath = scanner1.nextLine(); File fl = new File(dirpath); if (fl.canRead()) break; System.out.println("Error:Directory does not exists"); } try { String files; File folder = new File(dirpath); File[] listOfFiles = folder.listFiles(); for (int i = 0; i &lt; listOfFiles.length; i++) { if (listOfFiles[i].isFile()) { files = listOfFiles[i].getName(); if (files.endsWith(".txt") || files.endsWith(".TXT")) { List&lt;File&gt; txtFiles = new ArrayList&lt;File&gt;(); txtFiles.add(listOfFiles[i]); String[] parts = files.split("\\."); String tablename = parts[0]; for (File txtFile : txtFiles) { List sheetData = new ArrayList(); try { FileReader in = new FileReader(txtFile); BufferedReader br = new BufferedReader(in); String line = br.readLine(); while (line != null) { System.out.println(line); line = br.readLine(); } in.close(); } catch (Exception e) { System.err.println("Error: " + e.getMessage()); } showExcelData(sheetData); getCreateTable1(con, tablename); importData(con, txtFile); } } } } } catch (Exception e) { System.out.println(); } } private static String getCreateTable1(Connection con, String tablename) { try { Class.forName("com.mysql.jdbc.Driver"); Statement stmt = con.createStatement(); String createtable = "CREATE TABLE " + tablename + " ( text VARCHAR(255), price int )"; System.out.println("Create a new table in the database"); stmt.executeUpdate(createtable); } catch (Exception e) { System.out.println(((SQLException) e).getSQLState()); System.out.println(e.getMessage()); e.printStackTrace(); } return null; } private static void importData(Connection con, File txtFile) { Statement stmt; String query; try { stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); query = "LOAD DATA INFILE '" + txtFile + "' INTO TABLE tablename (text,price);"; stmt.executeUpdate(query); } catch (Exception e) { e.printStackTrace(); stmt = null; } } </code></pre> <p>Could anyone help me how I would do it? My difficulty is when I am tring to import the data.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USdedmar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POstore data from txt files in table mysql
      1. USu449355
      1. VTFavorite
    1. COPlease edit your post. You have error in SQL query, that is specific to SQL. It has nothing to do with Java or JDBC. Remote java code, leave only query and retag to MySQL. First try to invoke command from mysql console to get more precise SQL error code.
      singulars
      1. POstore data from txt files in table mysql
      1. USDanubian Sailor
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload