时间戳

首页 > 解决方案 > 在 CSV 文件上用 Java 编写 MS 访问表

java - 在 CSV 文件上用 Java 编写 MS 访问表

问题描述

我正在编写一个小软件，它会自动将 .mdb 文件的名为“Magazzino”的表导出到 csv 文件。一切正常，我使用“UCANACCESS”库编写了这段代码。

package it.simonesaleri.automation.software;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;

public class AutomationMain {

    public static void main(String[] args) throws IOException {

        ArrayList<String> arr = new ArrayList<String>();
        try {
            
            //OPENING CONNECTION TO DATABASE
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://eCommerce.mdb");
            FileWriter writer = new FileWriter("eCommerce.csv");
            System.out.println("Connection to Database");
            Statement s = conn.createStatement();
            
            //SQL INJECT CODE
            String sqlString = "SELECT * FROM Magazzino";
            ResultSet rs = s.executeQuery(sqlString);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i < columnsNumber; i++) {
                    System.out.println(rs.getString(i));
                    arr.add(rs.getString(i));
                }
            }
            //SAVING INTO THE FILE
            for(String str: arr)
            {
                writer.append(str);
                writer.append(";");
            }
            writer.flush();
            writer.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

我遇到了问题...我真的不明白如何在 .csv 文件上创建新行，同时在其上写入。我尝试了.append("\n")or .append("\r\n")，但结果是这样的：

1;244;300817; BA9S - 220R  LAMPADA LED 220VAC  ROSSA;;;;;;;;;;;;;;;;12.14;3.6420000000000003;5.6451;5.463000000000001;5.098800000000001;4.7346;5.88183;PCE;22.0;IVA 22%;0.0;0.0;0.0;0.0;;731;CHINT;CHT;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;CHTCHTAUTOM-A01;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;2;9713;BSD450B/8A; CONVERTITORE BRUSHLESS FD BSD450B/8A;;;;;;;;;;;;;;;;680.0;680.0;1054.0;1020.0;952.0;884.0;1098.2;;22.0;IVA 22%;0.0;0.0;0.0;0.0;;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;

而不是这个（我需要的结果）：

1;244;300817; BA9S - 220R  LAMPADA LED 220VAC  ROSSA;;;;;;;;;;;;;;;;12.14;3.6420000000000003;5.6451;5.463000000000001;5.098800000000001;4.7346;5.88183;PCE;22.0;IVA 22%;0.0;0.0;0.0;0.0;;731;CHINT;CHT;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;CHTCHTAUTOM-A01;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;
2;9713;BSD450B/8A; CONVERTITORE BRUSHLESS FD BSD450B/8A;;;;;;;;;;;;;;;;680.0;680.0;1054.0;1020.0;952.0;884.0;1098.2;;22.0;IVA 22%;0.0;0.0;0.0;0.0;;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;

标签： javamysqlcsvms-accessdatatable

解决方案

我想你可以这样做：

StringBuilder sb = new StringBuilder("");
while (rs.next()) {
    // Clear StringBuilder object in prep for a new CSV line
    sb.setLength(0); 
    // Create custom CSV data line using semicolon (;) as delimiter...
    for (int i = 1; i < columnsNumber; i++) {
        /* IF StringBuilder is not empty then add a semicolon
           to the end of the last piece of columnar data that 
           was appended before appending this next piece of 
           columnar data.            */
        if (!sb.toString().isEmpty()) {
            sb.append(";");
        }
        sb.append(rs.getString(i));
    }
    // Add the system newline character to custom CSV data line.
    sb.append(System.lineSeparator()); 
    // Add the newly created CSV line to the ArrayList.
    arr.add(sb.toString());
}
   
    
// Write to File...
for(String str: arr) {
    /* str already has the newline character appended 
       to it from the process above.  */
    writer.append(str); 
    // immediate write:
    writer.flush(); 
}
writer.close(); // Close the writer.

推荐阅读